How do I insert a pandas DataFrame to an existing PostgreSQL table?

Question

I have a pandas DataFrame df and a PostgreSQL table my_table. I wish to truncate my_table and insert df (which has columns in the same order) into my_table, without affecting the schema of my_table. How do I do this?

In a rather naive attempt, I tried dropping my_table and then using pandas.DataFrame.to_sql, but this creates a new table with a different schema.

MaxU - stand with Ukraine · Accepted Answer · 2017-01-19 11:52:07Z

9

I would manually truncate the table and then simply let Pandas do its job:

con.execute('TRUNCATE my_table RESTART IDENTITY;')
df.to_sql('my_table', con, if_exists='append')

answered Jan 19, 2017 at 11:52

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

4 Comments

Train Heartnet Over a year ago

Thank you for the quick response! Could you tell me what the 'RESTART IDENTITY' bit does? I did refer the PostgreSQL docs just now, but couldn't understand what it means; "Automatically restart sequences owned by columns of the truncated table(s)".

MaxU - stand with Ukraine Over a year ago

@TrainHeartnet, you may want to read about PostgreSQL sequnces. You can omit RESTART IDENTITY if you don't have sequences in the my_table table

Lauren Fitch Over a year ago

You could skip the manual truncation and do a single line: df.to_sql('my_table', con, if_exists='replace')

Aziz Alto Over a year ago

What if I need to execute an UPSERT statement?

Collectives™ on Stack Overflow

How do I insert a pandas DataFrame to an existing PostgreSQL table?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related