PostgreSQL INSERT FROM SELECT with additional column

Question

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?

C_additional='needed_value' is a boolean expression that evaluates if the column name C_additional equals the constant 'needed_value'. If you want a column alias use one: 'needed_value' as c_additional — user330315
– user330315, Commented Apr 27, 2016 at 7:18

Mureinik · Accepted Answer · 2016-04-27 07:17:02Z

3

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)

answered Apr 27, 2016 at 7:17

Mureinik

316k54 gold badges398 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhivanan · Accepted Answer · 2016-04-27 07:16:43Z

0

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)

answered Apr 27, 2016 at 7:16

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

PostgreSQL INSERT FROM SELECT with additional column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related