带有附加列的 PostgreSQL INSERT FROM SELECT [英] PostgreSQL INSERT FROM SELECT with additional column

问题描述

我在数据库 DB1 中有表 T1 和数据库 DB2 中有表 T2，这些表几乎相同除了 T1 中的 C_additional 列之外的列集，在 T2 中不存在.我需要将所有行从 T2 转移到 T1，为我插入的每一行设置一些 C_additional 的值.例如:T1 和 T2 只有一列 C1 类型为 integer 和 T1> 也有 text 类型的 C_additional 列，所以我的代码如下所示:

I have table T1 in database DB1 and table T2 in database DB2, those tables have almost identical sets of columns except column C_additional in T1, which is not present in T2. I need to transfer all the rows from T2 to T1, setting some value of C_additional for each row i insert. For example: T1 and T2 have only one column C1 of type integer and T1 also has column C_additional of type text, so my code looks like this:

INSERT INTO T1
SELECT 
        C1,
        C_additional='needed_value'
FROM dblink(
    'hostaddr=127.0.0.1 port=5432 dbname=DB2 user=postgres password=postgres', 
    'SELECT * FROM T2')
AS T2_row(C1 integer)

我收到以下错误:

ERROR: column "C_additional" does not exist
SQL state: 42703
Hint: There is a column named "C_additional" in table "T1", but it cannot be referenced from this part of the query.

如何使用 SQL 进行数据传输，还是应该使用 PG/SQL?

How can I do the data transfer using SQL, or should I use PG/SQL?

推荐答案

可以在select子句前用括号指定目标列:

You can specify the target columns with parenthesis before the select clause:

INSERT INTO T1
(c1, c_additional) -- here
SELECT 
        C1,
        'needed_value' -- just select a constant here
FROM dblink(
    'hostaddr=127.0.0.1 port=5432 dbname=DB2 user=postgres password=postgres', 
    'SELECT * FROM T2')
AS T2_row(C1 integer) 

这篇关于带有附加列的 PostgreSQL INSERT FROM SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！