Question 90217 in PostgreSQL Question posted in
The official documentation can be found here.

How to concat columns in SQL? – Postgresql

PostgreSQL 15.0
I want to make a query that concats two different columns into one.

Desired outcome I showed in the exapmle (it’s not a real one but it would be useful to understand on this example).

I’ve used CONCAT but it does’t create new column, just concatenation.
How do I get:

id       Col1        Col2
1        foo          10
2        bar          42
3        baz          14

to

id           NewColumn
1             foo: 10
2             bar: 42
3             baz: 14
Tags:

3

Answers


  1. 0 
    SELECT Name, CONCAT(col1, " ", col2) AS NewColumn FROM Data;

    this code concat the 2 column in table, see more info here

    Login or Signup to reply.
  2. 0 
    create table t (id int, col1 text, col2 int);

insert into t values
    (1, 'foo', 10),
    (2, 'bar', 42), 
    (2, 'baz', 14), 
    (2, null, 14),
    (2, 'wow', null);
    
   select id, coalesce(col1, '') || ': ' || coalesce(col2::text, '') AS NewColumn FROM t

    check the live demo here
    https://www.db-fiddle.com/f/sNANpwdUPdJfUaSQ77MQUM/1

    and read docs here https://www.postgresql.org/docs/current/functions-string.html

    And do not forget about null values

    But if you want to create a really new column in the table as a result of concatenation:

    alter table t 
    add column NewColumn text 
    GENERATED ALWAYS AS (
        coalesce(col1, '') || ': ' || coalesce(col2::text, '')
    ) STORED;

select id, NewColumn FROM t

    Check the live demo here https://www.db-fiddle.com/f/sNANpwdUPdJfUaSQ77MQUM/2

    And documentation https://www.postgresql.org/docs/current/ddl-generated-columns.html

    Login or Signup to reply.
  3. 0

    You can use below statement,

    select id, Col1||': '||Col2 as NewColumn from table_name;

    In order to get Col1 and Col2 as well, you can use below,

    select id, Col1, Col2, Col1||': '||Col2 as NewColumn from table_name;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search