I have an SQL query like this:
SELECT DISTINCT(id) FROM users WHERE ...
and I would like to display the results like that:
user=12355
user=78949
user=9898
user=489891
Basically with "user=" prepended. Is it possible to do this with PostgreSQL? I've tried with STRING_AGG('user=', DISTINCT(id)) but got an error on DISTINCT(id). Any idea?
I'd use a plain GROUP BY for this.
SELECT format('user=%s',id)
FROM users
GROUP BY id;
http://sqlfiddle.com/#!1/39727/3
This will be considerably more efficient than using DISTINCT on the string concatenation.
You should be able to use || for string concatenation:
SELECT DISTINCT('user=' || id)
FROM users
WHERE ...
This might be useful as well:
http://www.postgresql.org/docs/current/static/functions-string.html
The only reason you got an error message from string_agg() is because you forgot the second parameter which is required. This very simple query would just work:
SELECT string_agg('user=' || id, E'\n')
FROM users
WHERE ...
E'\n' .. newline character
Produces one row with the exactly the string you have in your question.
You do not need either DISTINCT or GROUP BY unless you have duplicates in id - in which case you'd need a subquery:
SELECT string_agg('user=' || id, E'\n')
FROM (SELECT id FROM users GROUP BY id) x
Simply Select concat('user=',id) from users
