PostgreSQL string to string
I have a result from a query like below which does not have a fixed number of columns
ID COL1 COL2 COL3 COL4
-------------------------------------
1 VAL11 VAL12 VAL13 VAL14
2 VAL21 VAL22 VAL23 VAL24
Now I want the result to be something like this.
RESULT
-----------------------------------------------------
ID:1, COL1:VAL11, COL2:VAL12, COL3:VAL13, COL4:VAL14
ID:2, COL1:VAL21, COL2:VAL22, COL3:VAL23, COL4:VAL24
Please, help.
source to share
A quick and dirty way, but without column names and including NULL values:
SELECT tbl::text
FROM tbl;
The slow and surefire way:
SELECT array_to_string(ARRAY[
'ID:' || id
,'COL1:' || col1
,'COL2:' || col2
], ', ') AS result
FROM tbl;
If the column contains a value NULL
, it will be missing in the result. I am not just concatenating, because the NULL
values will nullify the entire string. array_to_string()
ensures that commas are inserted only where needed.
PostgreSQL 9.1 introduced a new feature (which is very similar to MySQL) with which we can simplify further: concat_ws()
SELECT concat_ws(', '
'ID:' || id
,'COL1:' || col1
,'COL2:' || col2
) AS result
FROM tbl;
source to share
SELECT
'ID:' ||coalesce(id::text, '<null>')
||', '||'COL1:'||coalesce(col1::text, '<null>')
||', '||'COL2:'||coalesce(col2::text, '<null>')
FROM tbl;
You can use this SQL to create the first one for you (in case there are many columns):
SELECT E'SELECT \n'||string_agg(trim(stmt), E' \n')||E'\n FROM tbl;'
FROM (SELECT
CASE WHEN a.attnum > 1 THEN $$||', '||$$ ELSE '' END ||
$$'$$||upper(a.attname)||$$:'||coalesce($$||quote_ident(a.attname)||
$$::text, '<null>')$$ AS stmt
FROM pg_attribute a, pg_class t
WHERE t.relkind='r' AND t.relname = 'tbl' AND a.attrelid = t.oid
AND NOT a.attisdropped AND a.attnum > 0) AS s;
source to share