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
– Adrian Klaver, Commented Jul 7, 2020 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
– Panagiotis Kanavos, Commented Jul 7, 2020 at 16:37
You really shouldn't be using 9.4 for a target of a migration in 2020. Consider upgrading straight to 12 — user330315
– user330315, Commented Jul 7, 2020 at 17:07

user330315 · Accepted Answer · 2020-07-08 10:44:56Z

4

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

answered Jul 8, 2020 at 10:44

user330315

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-07-08 06:11:26Z

1

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.

answered Jul 8, 2020 at 6:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Comments

Adrian Klaver · Accepted Answer · 2020-07-07 16:35:50Z

-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 7, 2020 at 16:35

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

string_agg function in newer postgres version

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related