string_agg function in newer postgres version

Question

I'm currently working on postgres code developed years ago under 8.4 version, and migrating to a 9.4 postgres version and I came across this line of code:

string_agg(''#attribute_''||attribute_id::varchar||'':''||attribute) as attr

Within this context:

_myquery='INSERT INTO mytable
        SELECT ID,string_agg(''#attribute_''||attribute_id::varchar||'':''||attribute,''|'') as attr
FROM my_attribute_table
GROUP BY ID;';

In the process of migrating to Postgres 9.4 I'm getting this:

ERROR: function string_agg(text) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts.

What would be the proper way of dealing with string_agg in the newer version? I understand I either need to explicitly cast or add a delimiter, but doesn't sound like the current code adds any delimiter today, does it?

Today, the results we are getting on attr are these for example:

"#attribute_78:None#attribute_25:715#attribute_48:Consumer#attribute_538:1yr Ret Base#attribute_1178:1yr Ret Base"

FYI, 9.4 is ~ six months past EOL. I would recommend moving to a newer supported release. — Adrian Klaver, Jul 07 '20 at 16:24
Your query contains duplicate single quotes. Was this part of another query, perhaps an attempt to create dynamic SQL? What is the *actual* query you tried to execute? — Panagiotis Kanavos, Jul 07 '20 at 16:37
You really shouldn't be using 9.4 for a target of a migration in 2020. Consider upgrading straight to 12 — , Jul 07 '20 at 17:07

score 4 · Answer 1 · answered Jul 08 '20 at 10:44

As others have pointed out, there is no string_agg(text) in Postgres. You always have to specify a delimiter - you can provide an empty string however. To get around the need for casting, I typically also prefer concat() over ||

string_agg(concat('#attribute_', attribute_id, ':', attribute), '') as attr

score 1 · Answer 2 · answered Jul 08 '20 at 06:11

PostgreSQL 8.4 didn't have a string_agg function, and the string_agg aggregate function available in current releases takes two arguments, where the second is the separator that is put between the aggregated values.

So this is likely a custom user defined function created in the 8.4 database.

score -1 · Answer 3 · answered Jul 07 '20 at 16:35

-1

You need to show what your expected output is, but in meantime:

select string_agg('#attribute_'||'test'||':'||'test2', ',') as attr5;

-----------------------
 #attribute_test:test2

answered Jul 07 '20 at 16:35

Adrian Klaver

15,886
2
17
28

string_agg function in newer postgres version

3 Answers3