Is there a way to treat a csv like a table to match keys and import data to appropriate rows in postgres?

user_9778354 0
Viewed 104

We have multiple data sources we're trying to merge into a DB table that are not ordered and may not even have matching records. We have a column that is common to both that we'd like to match up and merge the records. I'm looking to find a command that we can write that will do something like:

if column1.table = column1.csvfile then update table set column2.table = column2.csvfile WHERE column1.table = column1.csvfile

Scanning through each row of the CSV.

COPY assumes that your data is in order.

sql-update postgresql
2 Answers

file_fdw is made precisely for this requirement.

Define a foreign table on the CSV file, then you can query it like a regular table.

user_64643080

An easy way to do this is to create a temporary table (let's call it table2) with a structure that matches your CSV file, and COPY the file to there. Then you can run a simple update:

UPDATE table1
SET column2 = table2.column2
FROM table2
WHERE table1.column1 = table2.column1;

And then drop table2 when you're done.

user_68978070
Related