CODEWITHSUNDEEP
postgresqldatabase-design
hicham
hicham

Reputation: 25

trigger or calculated columns or update or view

I have two csv files file1.csv and file2.csv I load those files in two tables in a database 'table1' and 'table2'. table1 contains two columns, those columns use table2 columns to calculate their values, and after that the row of table2 can be deleted because it used just to update table1.

My question is:

Which solution is better:

  1. When insert in table2 executes a trigger that update the two columns in table1 then delete current row from table2 because it used just to calculate the two columns of table1.
  2. Delete table2 and do just update table1 when loading file2.
  3. Create the two columns as calculated columns, so they will be virtual columns only.
  4. Create view composed of all columns of table1 and add two calculated columns in this view. You can propose another solution

Upvotes: 0

Views: 123

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

5:

  • Create a TEMPORARY staging table table2. Temporary tables perform much better (and are not persisted).
  • COPY file2.csv into it.
  • UPDATE table1.

The temporary table is dropped at the end of the session automatically. Code example here:
How to tell if record has changed in Postgres

For repeated use, you might keep an empty persisted table2 as template for the temporary table like demonstrated in the linked answer.
Or even put all of it into a plpgsql function (that's what I would do).

Upvotes: 1

bma
bma

Reputation: 9776

Another option is to use the file_fdw FOREIGN DATA WRAPPER to read the file2.csv from within the db (without having to load it) and insert the calculated results into table1. This assumes that you are using Postgres 9.1+, and that your file2.csv isn't too big. The suggestion from Erwin is probably the most useful solution, however.

Upvotes: 1

Related Questions