How to Concatenate Columns in a Postgres Select

How to concatenate columns in a Postgres SELECT?

The problem was in nulls in the values; then the concatenation does not work with nulls. The solution is as follows:

SELECT coalesce(a, '') || coalesce(b, '') FROM foo;

Combine two columns and add into one new column

Generally, I agree with @kgrittn's advice. Go for it.

But to address your basic question about concat(): it is useful if you need to deal with null values - and null has neither been ruled out in your question nor in the one you refer to.

If you can rule out null values, the good old (SQL standard) concatenation operator || is still the best choice, and @luis' answer is just fine:

SELECT col_a || col_b;

If either of your columns can be null, the result would be null in that case. You could defend with COALESCE:

SELECT COALESCE(col_a, '') || COALESCE(col_b, '');

But that gets tedious quickly with more arguments. That's where concat() comes in, which never returns null, not even if all arguments are null. The manual:

NULL arguments are ignored.

SELECT concat(col_a, col_b);

The remaining corner case for both alternatives is where all input columns are null in which case we still get an empty string ''. To get null instead:

SELECT CASE
          WHEN col_a IS NULL THEN col_b
          WHEN col_b IS NULL THEN col_a
          ELSE col_a || col_b
       END;

This gets more complex with more columns quickly. Again, use concat(), but add a check for the special condition:

SELECT CASE WHEN (col_a, col_b) IS NULL THEN NULL
            ELSE concat(col_a, col_b) END;

How does this work?

(col_a, col_b) is shorthand for ROW (col_a, col_b). And a row type is only null if all columns are null. Detailed explanation:

• NOT NULL constraint over a set of columns

Also, use concat_ws() to add separators between elements (ws for "with separator").

---

An expression like the one in Kevin's answer:

SELECT $1.zipcode || ' - ' || $1.city || ', ' || $1.state;

is tedious to prepare for null values in PostgreSQL 8.3 (without concat()). One way (of many):

SELECT COALESCE(
         CASE
            WHEN $1.zipcode IS NULL THEN $1.city
            WHEN $1.city    IS NULL THEN $1.zipcode
            ELSE $1.zipcode || ' - ' || $1.city
         END, '')
       || COALESCE(', ' || $1.state, '');

Function volatility is only STABLE

concat() and concat_ws() are STABLE functions, not IMMUTABLE because they can invoke datatype output functions (like timestamptz_out) that depend on locale settings.

Explanation by Tom Lane.

This prohibits their direct use in index expressions. If you know that the result is actually immutable in your case, you can work around this with an IMMUTABLE function wrapper. Example here:

• Does PostgreSQL support "accent insensitive" collations?

Concatenate multiple result rows of one column into one, group by another column

Simpler with the aggregate function string_agg() (Postgres 9.0 or later):

SELECT movie, string_agg(actor, ', ') AS actor_list
FROM   tbl
GROUP  BY 1;

The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case.

string_agg() expects data type text as input. Other types need to be cast explicitly (actor::text) - unless an implicit cast to text is defined - which is the case for all other string types (varchar, character, name, ...) and some other types.

As isapir commented, you can add an ORDER BY clause in the aggregate call to get a sorted list - should you need that. Like:

SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
FROM   tbl
GROUP  BY 1;

But it's typically faster to sort rows in a subquery. See:

• Create array in SELECT

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all your results or deal with an undefined order. So you can now write:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

PostgreSQL 8.4.x:

PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg(expression) which collects the values in an array. Then array_to_string() can be used to give the desired result:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x and older:

When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcat function (which lies behind the || operator):

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Here is the CREATE AGGREGATE documentation.

This simply glues all the strings together, with no separator. In order to get a ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together and tested on 8.3.12:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

This version will output a comma even if the value in the row is null or empty, so you get output like this:

a, b, c, , e, , g

If you would prefer to remove extra commas to output this:

a, b, c, e, g

Then add an ELSIF check to the function like this:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

How to combine/merge two select queries by COLUMN in PostgreSQL?

A cross join would be very dangerous because it produces a huge amount of data. E.g. if Table1 and Table2 each have 1000 rows, the result of a cross join would be 1,000,000 rows!

However, I assume you want to line up matching rows. So use the following:

select  COALESCE(t1.id, t2.id) as id,
        t1.attr1, t1.attr2, t2.attr1, t2.attr2
from    Table1 t1
        full outer join Table2 t2 on
            t2.id = t1.id

The full outer join means this also returns rows where no match is found.

Concatenate columns and adds digits postgresql

Assuming all columns are NOT NULL, and max_length is always greater than min_length this does the job:

CREATE TABLE table2 AS
SELECT t.number1::text || rpad(t.number2::text, len, '0') AS start
     , t.number1::text || rpad(t.number2::text, len, '9') AS stop
FROM   table1 t, generate_series(min_length, max_length) len

db<>fiddle here

The manual for generate_series() and rpad().

If number1 or number2 can be NULL, throw in COALESCE:

SELECT COALESCE(t.number1::text, '') || rpad(COALESCE(t.number2::text,''), len, '0') AS start
     , COALESCE(t.number1::text, '') || rpad(COALESCE(t.number2::text,''), len, '9') AS stop
FROM table1 t, generate_series(min_length, max_length) len;

db<>fiddle here

If min_length or max_length can be NULL, you'll have to define what's supposed to happen.

---

Related Topics