StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is probably a good starting point (version 8.4+ only):</p> <pre><code>SELECT id_field, array_agg(value_field1), array_agg(value_field2) FROM data_table GROUP BY id_field </code></pre> <p><a href="https://www.postgresql.org/docs/9.5/static/functions-aggregate.html" rel="noreferrer">array_agg</a> returns an array, but you can CAST that to text and edit as needed (see clarifications, below).</p> <p>Prior to version 8.4, you have to define it yourself prior to use:</p> <pre><code>CREATE AGGREGATE array_agg (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); </code></pre> <p>(paraphrased from the PostgreSQL documentation)</p> <p>Clarifications:</p> <ul> <li>The result of casting an array to text is that the resulting string starts and ends with curly braces. Those braces need to be removed by some method, if they are not desired. </li> <li>Casting ANYARRAY to TEXT best simulates CSV output as elements that contain embedded commas are double-quoted in the output in standard CSV style. Neither array_to_string() or string_agg() (the "group_concat" function added in 9.1) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.</li> <li>The new 9.1 string_agg() function does NOT cast the inner results to TEXT first. So "string_agg(value_field)" would generate an error if value_field is an integer. "string_agg(value_field::text)" would be required. The array_agg() method requires only one cast after the aggregation (rather than a cast per value).</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload