Concate text fields with PostgreSQL

Question

What is wrong with this query?

SELECT gid, (tex1||' '||tex2) AS ident FROM my_table ;

The structure of my_table is as follow:

gid serial NOT NULL
tex1 CHARACTER VARYING(254)
tex2 CHARACTER VARYING(254)

The content of my_table is:

gid | tex1   | tex2
----+--------+--------
1   | A      | dog
2   | Two    | birds
3   | More   | things

The result of the query is:

gid | ident
----+-------
1   |
2   |
3   |

I would have never thought having troubles with such a simple query...

Thanks for help !

Answer 1

Are you sure that the sample data you are providing is correct?

What is the output of: SELECT gid, (tex1||' '||tex2) IS NULL FROM my_table;?

It seems that either tex1 or tex2 (or both) is NULL, thus the concatenation also produces NULL. Use COALESCE to provide a non null default value to use in such cases.

SELECT
  gid,
  (COALESCE(tex1, '') || ' ' || COALESCE(tex2, '')) AS ident
FROM
  my_table;