Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 690
bnopne Avatar asked Oct 20 '25 16:10

bnopne


1 Answers

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) 
like image 71
Mureinik Avatar answered Oct 22 '25 05:10

Mureinik



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!