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"

Answer 1

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

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

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

Answer 2

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

Answer 3

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.