update and insert records from a csv file in postgresql

Question

I have a table customer in my database in which I have some records already, now I want to make the delta changes in customer table from a CSV file.

Below are the two methods which I have tried but not working:

Method1:

CREATE TEMP TABLE CUSTOMER_TEMP AS
    select * from migration.customer where 1=2;

COPY CUSTOMER_TEMP(CUST_ID,CUST_CONTACT,CUST_COUNTRY,CUST_DETAIL) from 'C:\demo_dir\CUSTOMER_DATA_CHANGE.csv' DELIMITER '|';

MERGE INTO MIGRATION.CUSTOMER t
    USING(SELECT * FROM CUSTOMER_TEMP) AS m
        ON t.CUST_ID = m.CUST_ID
    WHEN MATCHED
        UPDATE SET t.CUST_ID = m.CUST_ID, t.CUST_CONTACT = m.CUST_CONTACT, t.CUST_COUNTRY = m.CUST_COUNTRY, t.CUST_DETAIL = m.CUST_DETAIL
    WHEN NOT MATCHED
        INSERT(CUST_ID,CUST_CONTACT,CUST_COUNTRY,CUST_DETAIL)
            VALUES(m.CUST_ID,m.CUST_CONTACT,m.CUST_COUNTRY,m.CUST_DETAIL);

DROP TABLE CUSTOMER_TEMP;

after checking several sites I got to know that Merge statement is not supported by PostgreSQL database. so I tried below method to do this:

Method2:

CREATE TEMP TABLE CUSTOMER_TEMP AS
select * from migration.customer where 1=2;

COPY CUSTOMER_TEMP(CUST_ID,CUST_CONTACT,CUST_COUNTRY,CUST_DETAIL) from 'C:\demo_dir\CUSTOMER_DATA_CHANGE.csv' DELIMITER '|';

INSERT INTO MIGRATION.CUSTOMER(CUST_ID,CUST_CONTACT,CUST_COUNTRY,CUST_DETAIL)
    (SELECT T.CUST_ID,T.CUST_CONTACT,T.CUST_COUNTRY,T.CUST_DETAIL FROM CUSTOMER_TEMP AS T)
    on conflict on constraint pk_cust
    do update set CUST_CONTACT = T.CUST_CONTACT, CUST_COUNTRY = T.CUST_COUNTRY, CUST_DETAIL = T.CUST_DETAIL
;

DROP TABLE CUSTOMER_TEMP;

The error which I am getting from the above command is:

ERROR: missing FROM-clause entry for table "t" LINE 4: do update set CUST_CONTACT = T.CUST_CONTACT, CUST_COUNTRY =... ^ SQL state: 42P01 Character: 232

But I am getting error in insert command.

Please help, I am using PostgreSQL 11 version.

Jeremy · Accepted Answer · 2019-09-09 15:38:04Z

1

You should use the keyword EXCLUDED:

CREATE TEMP TABLE CUSTOMER_TEMP (LIKE migration.customer);

COPY CUSTOMER_TEMP(CUST_ID,CUST_CONTACT,CUST_COUNTRY,CUST_DETAIL) from 'C:\demo_dir\CUSTOMER_DATA_CHANGE.csv' DELIMITER '|';

INSERT INTO MIGRATION.CUSTOMER(CUST_ID,CUST_CONTACT,CUST_COUNTRY,CUST_DETAIL)
    (SELECT T.CUST_ID,T.CUST_CONTACT,T.CUST_COUNTRY,T.CUST_DETAIL FROM CUSTOMER_TEMP AS T)
    on conflict on constraint pk_cust
    do update set CUST_CONTACT = EXCLUDED.CUST_CONTACT, CUST_COUNTRY = EXCLUDED.CUST_COUNTRY, CUST_DETAIL = EXCLUDED.CUST_DETAIL
;

DROP TABLE CUSTOMER_TEMP;

answered Sep 9, 2019 at 15:38

Jeremy

6,77322 silver badges21 bronze badges

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

2 Comments

Govind Gupta Over a year ago

Thanks! it works for me just wanted to know that what is the meaning of excluded and how it works. any reference?

Jeremy Over a year ago

Yes, it's in the docs for insert: postgresql.org/docs/current/sql-insert.html

Collectives™ on Stack Overflow

update and insert records from a csv file in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related