How to concat data columns using loop?

Question

How to concat columns data using loop in Postgres?

I have this table:

+------+------+------+--------+--------+--------+
| col1 | col2 | col3 | other1 | other2 | other3 |
+------+------+------+--------+--------+--------+
|    1 |    1 |    1 |      1 |      1 |      1 |
|    2 |    2 |    2 |      2 |      2 |      2 |
+------+------+------+--------+--------+--------+

and want to concat columns (col*).

Expected output:

+----------------+--------+--------+--------+
| concatedcolumn | other1 | other2 | other3 |
+----------------+--------+--------+--------+
| **1**1**1**    |      1 |      1 |      1 |
| **2**2**2**    |      2 |      2 |      2 |
+----------------+--------+--------+--------+

I can concat using:

select concat('**', col1, '**',col2, '**', col3, '**') as concatedcolumn
      ,other1, other2, other3
from sample_table

I have some 200 columns with prefix "col" and don't want to spell out all columns in sql. How could I achieve this with a loop?

"*I have some 200 columns with prefix col*" this sounds like a questionable database design to begin with. This is almost always a sign that the table should in fact be normalized. If you don't want to write the `concat` (or better `concat_ws()`) every time, create a view once with the concatenated expression (which probably would have taken as long as writing this question) — , Mar 09 '20 at 06:35
@a_horse_with_no_name a sign the design DBA was on vacation... I'm not sure which part is a bigger red flag: the `col*` prefix, or a table with 200 columns ? — Z4-tier, Mar 09 '20 at 06:39
@a_horse_with_no_name I agree that the database design is worst, but still I would like to know how could I do this using loop — Sameer, Mar 09 '20 at 06:57
Well, "SQL" has no loops, so the only thing you could do is a write a procedure (or function) that uses a loop to generate the view using dynamic SQL. Any solution other than creating the view with writing the expression once is going to be slower and much harder to use. — , Mar 09 '20 at 06:59
Either fetch the table as it is and write a loop in client code that concatenates the results, or write a loop to create an SQL statement that concatenates the columns. — Laurenz Albe, Mar 09 '20 at 07:02
*Always* provide Postgres version and a proper table definition (`CREATE TABLE` statement defining data types and constraints), the solution may depend on it. — Erwin Brandstetter, Mar 09 '20 at 13:16

Erwin Brandstetter · Accepted Answer · 2020-03-09T13:43:19.890

Questionable database design aside, you can generate the SELECT statement dynamically:

SELECT 'SELECT concat_ws(''**'', '
     || string_agg(quote_ident(attname), ', ') FILTER (WHERE attname LIKE 'col%')
     || ') AS concat_col, '
     || string_agg(quote_ident(attname), ', ') FILTER (WHERE attname NOT LIKE 'col%')
     || ' FROM public.tbl;'                  -- your table name here
FROM   pg_attribute
WHERE  attrelid = 'public.tbl'::regclass     -- ... and here
AND    attnum > 0
AND    NOT attisdropped;

db<>fiddle here

Query the system catalog pg_attribute or, alternatively, the information schema table columns. I prefer the system catalog.

Related answer on dba.SE discussing "information schema vs. system catalogs"

Execute in a second step (after verifying it's what you want).

No loop involved. You can build the statement dynamically, but you cannot (easily) return the result dynamically as SQL demands to know the return type at execution time.

concat_ws() is convenient, but it ignores NULL values. I didn't deal with those specially. You may or may not want to do that. Related:

How to concat data columns using loop?

1 Answers1