Update one postgres table from another postgres table

Question

I am loading a batch csv file to postgres using python (Say Table A). I am using pandas to upload the data into chunk which is quite faster.

for chunk in pd.read_csv(csv_file, sep='|',chunksize=chunk_size,low_memory=False):

Now I want to update another table (say Table B) using A based on following rules

if there are any new records in table A which is not in table B then insert that as a new record in table B (based on Id field)
if the values changes in the Table A for the same ID which exists in Table B then update the records in table B using TableA (There are server tables which i need to update based on Table A )

I am able to do that using below and then loop through each row, but Table A always have records around 1,825,172 and it becomes extremely slow. Any forum member can help to speed this up or suggest a alternate approach to achieve the same.

cursor.execute(sql)
records = cursor.fetchall()

for row in records:  
    id= 0 if row[0] is None else row[0]  # Use this to match with Table B and decide insert or update     
    id2=0 if row[1] is None else row[1]   
    id2=0 if row[2] is None else row[2]

GMB · Accepted Answer · 2019-10-24 13:29:01Z

2

You could leverage Postgres upsert syntax, like:

insert into tableB tb (id, col1, col2)
select ta.id, ta.col1, ta.col2 from tableA ta
on conflict(id) do update
    set col1 = ta.col1, col2 = ta.col2

edited Oct 24, 2019 at 13:29

answered Oct 24, 2019 at 13:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

learner Over a year ago

thanks for sharing about upsert statement, this is really good but i can't define the constraints at the moment as it can have duplicate rows in table b.

kutschkem · Accepted Answer · 2019-10-24 13:29:15Z

0

You should do this completely inside the DBMS, not loop through the records inside your python script. That allows your DBMS to better optimize.

UPDATE TableB
SET    x=y
FROM TableA
WHERE TableA.id = TableB.id

INSERT INTO TableB(id,x)
SELECT id, y
FROM TableA
WHERE TableA.id NOT IN ( SELECT id FROM TableB )

answered Oct 24, 2019 at 13:29

kutschkem

8,2683 gold badges26 silver badges66 bronze badges

Collectives™ on Stack Overflow

Update one postgres table from another postgres table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related