问题描述

我使用的是 PostgreSQL 9.2.我需要从这个名为 house 的表中复制地址(它们被分成多列):

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|

进入该表中名为 address_list 的单列:

into a single column in this table entitled address_list:

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

我的语法是

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

由于源表的 unit_number 字段中有一些空条目，我的语法不起作用.

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

有没有办法复制 unit_number 加上\"(如果 NOT NULL)，而忽略 unit_number 字段和\"IF NULL?

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.

推荐答案

使用format函数

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;

%s 标记参数位置.

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

这篇关于SQL 语法:将多列连接成一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！