Update table rows based on values in a text file

Question

I have a Postgres table publications that has a boolean column active, and my task was to set all the active values to FALSE, so I did the following copy command to have a backup of the rows that have an active value of TRUE:

\copy (SELECT publication_id FROM publications WHERE active = TRUE)
to '~/active_publications.csv'
with csv

In the end, I got a CSV file that has all the publication_ids of the active rows. Then I ran the following command to set all the active values to FALSE:

UPDATE publications SET active = FALSE;

Now I need to go back in a reverse process: How can I use this file to set the active values back to TRUE for all the rows that have a publication_id in this CSV file?

Laurenz Albe · Accepted Answer · 2019-02-14 06:46:09Z

1

The simple way is to load the file with COPY into a temporary table and then use UPDATE ... FROM or INSERT ... ON CONFLICT to update your table.

Alternatively, you can use file_fdw to define the file as a foreign table and UPDATE using that.

answered Feb 14, 2019 at 6:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Update table rows based on values in a text file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related