PostgreSQL INSERT FROM SELECT 带有附加列

我有桌子T1在数据库中DB1和桌子T2在数据库中DB2，这些表具有几乎相同的列集，除了列C_additional in T1，它不存在于T2。我需要传输所有行T2 to T1，设置某个值C_additional对于我插入的每一行。例如：T1 and T2只有一列C1类型的integer and T1还有专栏C_additional类型的text，所以我的代码如下所示：

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？

您可以在之前用括号指定目标列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)