1

How to update table from csv file in PostgreSQL? (version 9.2.4)

Copy command is for insert. But I need to update table. How can I update table from csv file without temp table?
I don't want to copy to temp table from csv file and update table from temp table.
And no merge command like Oracle?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user2991200
  • 11
  • 1
  • 1
  • 2
  • possible duplicate of [How to update selected rows with values from a CSV file in Postgres?](http://stackoverflow.com/questions/8910494/how-to-update-selected-rows-with-values-from-a-csv-file-in-postgres) – Sergiu Paraschiv Nov 14 '13 at 09:05
  • 2
    *Why* don't you want to use a temp table? What about a permanent staging tabled (created as `UNLOGGED`), or a db function that uses `postgres_fdw` or `file_fdw` to read from your file and transform the data into your target table? – bma Nov 14 '13 at 16:06
  • Thanks a lot. I'll try it.. The reason that I don't want to use a temp table is.. there are many tables (about 100 tables) to be updated hourly – user2991200 Nov 15 '13 at 01:11

1 Answers1

3

The simple and fast way is with a temporary staging table, like detailed in this closely related answer:
How to update selected rows with values from a CSV file in Postgres?

If you don't "want" that for some unknown reason, there are more ways:

Details in this related answer:
Read data from a text file inside a trigger

There is no MERGE command in Postgres, even less for COPY.
Discussion about whether and how to add it is ongoing. Check out the Postgres Wiki for details.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228