Concat columns in postgresql select

Question

I've tried a few variations, but from my reading of the the documentation this pattern should work `'' || val1 || val1` ... yet my result is an empty column ... ``` thedb=# \d buildings_propertyvalue; Table "public.buildings_propertyvalue" Column | Type | Modifiers -----------+------------------------+---------------------------------------------------------------------- id | integer | not null default nextval('buildings_propertyvalue_id_seq'::regclass) prop_id | integer | not null place_id | integer | not null float_val | double precision | int_val | integer | char_val | character varying(255) | text_val | text | thedb=# select * from buildings_propertyvalue limit 10; id | prop_id | place_id | float_val | int_val | char_val | text_val -----+---------+----------+-----------+---------+----------+---------- 798 | 3 | 170 | | 831 | | 2 | 46 | 180 | | 0 | | 733 | 2 | 180 | 40 | | | 737 | 10 | 180 | | 0 | | 740 | 5 | 345 | 100 | | | 742 | 10 | 345 | | 0 | | 11 | 2 | 170 | 50 | | | 744 | 11…

Accepted Answer

Concatenating a `NULL` with a non-empty string yields a `NULL`

Since your `*_val` columns are nullable, it's probably what is happening.

Try this:

```
'' || COALESCE(float_val::TEXT, '') || COALESCE(int_val::TEXT, '') || COALESCE(char_val, '') || COALESCE(text_val, '')
```

or, if you can only have at most one non-null value, just this:

```
COALESCE(float_val::TEXT, int_val::TEXT, char_val, text_val, '')
```

Note that in `PostgreSQL`, unlike some other engines, `TEXT` has no downsides compared to `VARCHAR`. There is no point in separating `TEXT` and `VARCHAR` data.

Concat columns in postgresql select

Solution

Problem