CODEWITHSUNDEEP
sqlpostgresqlsql-insert
bnopne
bnopne

Reputation: 95

PostgreSQL INSERT FROM SELECT with additional column

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)

I get the following error:

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.

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

Upvotes: 4

Views: 2810

Answers (2)

Mureinik
Mureinik

Reputation: 311018

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)

Upvotes: 3

Madhivanan
Madhivanan

Reputation: 13700

Can you try this?

INSERT INTO T1
SELECT 
        C1,
        '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)

Upvotes: 0

Related Questions