Asked  7 Months ago    Answers:  5   Viewed   37 times

I have a table and I'd like to pull one row per id with field values concatenated.

In my table, for example, I have this:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

And I'd like to output:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

In MySQL I was able to use the aggregate function GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?

 Answers

58

This is probably a good starting point (version 8.4+ only):

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array_agg returns an array, but you can CAST that to text and edit as needed (see clarifications, below).

Prior to version 8.4, you have to define it yourself prior to use:

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(paraphrased from the PostgreSQL documentation)

Clarifications:

  • 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.
  • 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.
  • 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).
Tuesday, June 1, 2021
 
astaykov
answered 7 Months ago
69

For a clean and efficient solution you can create an user defined aggregate function, there is even an example that does just what you need.
You can then use it like any other aggregate function (with a standard query plan):

query plan

Monday, June 7, 2021
 
Sethunath
answered 7 Months ago
35

Use FOR XML PATH:

SELECT [Text]+' ' AS 'text()' FROM _table FOR XML PATH('')

Another option - use string concatenation:

DECLARE @s nvarchar(max)
SELECT @s = ISNULL(@s, '') + t + ' '  FROM _table OPTION (MAXDOP 1)
SELECT @s

Please note that the latter one isn't guaranteed to work, afaik, officially the behaviour of "@s = @s + ..." for multi-row resultset is undefined.
MAXDOP 1 hint is used here to prevent the optimizer from creating a parralel execution plan, as this will yield an incorrect result for sure.

Tuesday, August 31, 2021
 
AndrewFerrara
answered 3 Months ago
65

Just define a trivial wrapper function.

CREATE OR REPLACE FUNCTION raise_exception(text) RETURNS text AS $$
BEGIN
    RAISE EXCEPTION '%',$1;
END;
$$ LANGUAGE plpgsql VOLATILE;

then use CASE:

SELECT CASE 
         WHEN parameter_id != p_cropparameter 
           THEN raise_exception("blah") 
         ELSE parameter_id 
       END;

This only works if the CASE otherwise returns text though, e.g. if parameter_id is integer you get:

regress=> SELECT CASE WHEN 1 = 2 THEN raise_exception('blah') ELSE 1 END;
ERROR:  CASE types integer and text cannot be matched
LINE 1: SELECT CASE WHEN 1 = 2 THEN raise_exception('blah') ELSE 1 E...

You can work around this with a hack using polymorphic functions. Define:

CREATE OR REPLACE FUNCTION raise_exception(anyelement, text) RETURNS anyelement AS $$
BEGIN
    RAISE EXCEPTION '%',$2;
    RETURN $1;
END;
$$ LANGUAGE plpgsql VOLATILE;

then pass a fake value of the case type to it so PostgreSQL type-matches it correctly, e.g.

SELECT CASE WHEN 1 = 1 THEN raise_exception(0, 'blah') ELSE 1 END;

or

SELECT CASE WHEN 1 = 1 THEN raise_exception(NULL::integer, 'blah') ELSE 1 END;

All seem too hard? That's because really, this sort of thing is usually just better done in PL/PgSQL.

Wednesday, September 1, 2021
 
Freddie
answered 3 Months ago
36

PostgreSQL has had window functions for a while now which can be used to do many things including counting rows before LIMIT is applied.

Based on the example above:

SELECT stuff,
       count(*) OVER() AS total_count
FROM table
WHERE condition
ORDER BY stuff OFFSET 40 LIMIT 20
Thursday, September 9, 2021
 
Amumu
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share