How to concatenate columns in a Postgres SELECT?

by

With string type columns like character(2) (as you mentioned later), the displayed concatenation just works because, quoting the manual:

[…] the string concatenation operator (||) accepts non-string
input, so long as at least one input is of a string type, as shown in
Table 9.8. For other cases, insert an explicit coercion to text […]

Bold emphasis mine. The 2nd example (select a||', '||b from foo) works for any data types since the untyped string literal ', ' defaults to type text making the whole expression valid in any case.

For non-string data types, you can “fix” the 1st statement by casting at least one argument to text. (Any type can be cast to text):

SELECT a::text || b AS ab FROM foo;

Judging from your own answer, “does not work” was supposed to mean “returns NULL“. The result of anything concatenated to NULL is NULL. If NULL values can be involved and the result shall not be NULL, use concat_ws() to concatenate any number of values (Postgres 9.1 or later):

SELECT concat_ws(', ', a, b) AS ab FROM foo;

Separators are only added between non-null values, i.e. only where necessary.

Or concat() if you don’t need separators:

SELECT concat(a, b) AS ab FROM foo;

No need for type casts here since both functions take "any" input and work with text representations.

More details (and why COALESCE is a poor substitute) in this related answer:

Regarding update in the comment

+ is not a valid operator for string concatenation in Postgres (or standard SQL). It’s a private idea of Microsoft to add this to their products.

There is hardly any good reason to use character(n) (synonym: char(n)). Use text or varchar. Details:

More Related Contents:

  1. Auto increment table column
  2. Any downsides of using data type “text” for storing strings?
  3. Best way to check for “empty or null value”
  4. Time zone storage in data type “timestamp with time zone”
  5. Change PostgreSQL columns used in views
  6. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  7. Casting NULL type when updating multiple rows
  8. How much disk-space is needed to store a NULL value using postgresql DB?
  9. PostgreSQL ORDER BY issue – natural sort
  10. How to store array or multiple values in one column
  11. Combine two columns and add into one new column
  12. Fetch records that are non zero after the decimal point in PostgreSQL
  13. Aggregate columns with additional (distinct) filters
  14. Insert data in 3 tables at a time using Postgres
  15. How to use ANY instead of IN in a WHERE clause?
  16. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  17. sql ORDER BY multiple values in specific order?
  18. Show a one to many relationship as 2 columns – 1 unique row (ID & comma separated list)
  19. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  20. Postgres unique constraint vs index
  21. How to select id with max date group by category in PostgreSQL?
  22. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  23. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  24. Alphanumeric sorting with PostgreSQL
  25. Transposing an sql result so that one column goes onto multiple columns
  26. How to use multiple CTEs in a single SQL query?
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. What is the most appropriate data type for storing an IP address in SQL server? [duplicate]
  29. PostgreSQL: Full Text Search – How to search partial words?
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment