SQL syntax: Concatenating multiple columns into one

Question

I am using PostgreSQL 9.2. I need to copy the addresses (which are split across multiple columns) from this table entitled houses:

 house_id | unit_number| street_number | street| street_extension| suburb | postcode | state | sale_price | sale_date |
----------+------------+-------+-------+-------+-----------------+--------+----------+-------+------------+-----------+
    1     |    2       |  17           | Rose  |     Av          |  Rye   | 3099     | VIC   | 240000     | 2003-12-02|
    2     |            |  3            | Jack  |     St          |  Rye   | 3099     | VIC   | 260000     | 2003-10-02|

into a single column in this table entitled address_list:

house_id  |                 address                  | formatted_address | lat | lon | wkb_geometry | is_processed 
----------+------------------------------------------+-------------------+-----+-----+--------------+--------------
          |                                          |                   |     |     |              |  
          |                                          |                   |     |     |              |

My syntax is

INSERT INTO address_list (house_id, address)
SELECT house_id, unit_number || '/' || street_number || ' ' || street || ' ' || street_extension || ', ' || suburb || ', ' || state || ' ' || postcode 
FROM houses;

My syntax does not work because of some null entries in the unit_number field of the source table.

Is there a way of copying the unit_number plus a "\" if NOT NULL, and ignoring the unit_number field and the "\" IF NULL?

I have spent several hours searching for a solution without luck so I would be extremely grateful for any assistance.

score 1 · Answer 1 · answered Aug 05 '13 at 11:44

Use the format function

insert into address_list (house_id, address)
select
    house_id,
    format(
        '%s%s %s %s, %s, %s %s', 
        unit_number || '\',
        street_number,
        street, street_extension, suburb, state, postcode
    )
from houses;

The %s marks the parameter position.

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER

score 0 · Answer 2 · answered Aug 05 '13 at 11:32

You can use case statement:

INSERT INTO address_list (house_id, address)
    SELECT house_id, case when unit_number is null then '' else unit_number end || '/' || street_number || ' ' || street || ' ' || street_extension || ', ' || suburb || ', ' || state || ' ' || postcode 
    FROM houses;

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

does not work because of some null entries in the unit_number

`COALESCE()`

If unit_number is the only column that can be NULL, use COALESCE, much simpler than CASE:

SELECT COALESCE(unit_number || '/', '')
    || street_number    || ' ' 
    || street           || ' ' 
    || street_extension || ', ' 
    || suburb           || ', ' 
    || state            || ' ' 
    || postcode 
FROM   houses;

If unit_number is null, the separator / is dropped, too. This primitive solution is also the fastest.

`concat_ws()`

(Postgres 9.1+)
concat() "with separator".

SELECT concat_ws(' '
       , unit_number || ' /'
       , street_number
       , street 
       , street_extension || ',' 
       , suburb || ',' 
       , state
       , postcode
       )
FROM   houses;

Would insert a space after /, though. So I included another one before /.

`format()`

(Postgres 9.1+)
For more complex cases use format() (Postgres 9.1+), as @Clodoaldo supplied. Slightly more expensive.

In fact there was an issue with the CASE syntax provided by Agad, but your concat syntax above works perfectly. A huge thank you. — Mike, Aug 05 '13 at 14:25
@Mike: agad's error is that `|| '/'` must be inside the `ELSE` branch. But you wouldn't use `CASE` in this case to begin with ... — Erwin Brandstetter, Aug 09 '13 at 13:52

SQL syntax: Concatenating multiple columns into one

3 Answers3

`COALESCE()`

`concat_ws()`

`format()`

Linked