Catholicism Computes » Concatenating row fields in PostgreSQL

Filed under: Computing — Pistos @ 11:24

If you want to concatenate multiple row results into one string, use this:

SELECT array_to_string(
  array(
    SELECT text_column
    FROM some_table
    WHERE foo = 'bar'
  ),
  ', '
);

To concatenate multiple columns for each row, use the normal text concatenation operator, ||.

SELECT array_to_string(
  array(
    SELECT text_column || '; ' || another_column
    FROM some_table
    WHERE id = 123
  ),
  ', '
);

db=> SELECT * FROM users;

 firstname | lastname
-----------+----------
 John      | Doe
 Jane      | Doe
 Joe       | Bloe
(3 rows)

db=> SELECT array_to_string(
  array(
    SELECT lastname || ', ' || firstname
    FROM users
  ),
  '; '
);

         array_to_string
---------------------------------
 Doe, John; Doe, Jane; Bloe, Joe

No comments yet.

You may use Markdown syntax in your comment.