0

I have a PostgreSQL database. I had to extend an existing, big table with a few more columns.

Now I need to fill those columns. I tought I can create an .csv file (out of Excel/Calc) which contains the IDs / primary keys of existing rows - and the data for the new, empty fields. Is it possible to do so? If it is, how to?

I remember doing exactly this pretty easily using Microsoft SQL Management Server, but for PostgreSQL I am using PG Admin (but I am ofc willing to switch the tool if it'd be helpfull). I tried using the import function of PG Admin which uses the COPY function of PostgreSQL, but it seems like COPY isn't suitable as it can only create whole new rows.

Edit: I guess I could write a script which loads the csv and iterates over the rows, using UPDATE. But I don't want to reinvent the wheel.

Edit2: I've found this question here on SO which provides an answer by using a temp table. I guess I will use it - although it's more of a workaround than an actual solution.

Community
  • 1
  • 1
Jdv
  • 962
  • 10
  • 34

1 Answers1

1

PostgreSQL can import data directly from CSV files with COPY statements, this will however only work, as you stated, for new rows.

Instead of creating a CSV file you could just generate the necessary SQL UPDATE statements.

Suppose this would be the CSV file

PK;ExtraCol1;ExtraCol2
1;"foo",42
4;"bar",21

Then just produce the following

UPDATE my_table SET ExtraCol1 = 'foo', ExtraCol2 = 42 WHERE PK = 1;
UPDATE my_table SET ExtraCol1 = 'bar', ExtraCol2 = 21 WHERE PK = 4;

You seem to work under Windows, so I don't really know how to accomplish this there (probably with PowerShell), but under Unix you could generate the SQL from a CSV easily with tools like awk or sed. An editor with regular expression support would probably suffice too.

Sebastian Schrader
  • 1,453
  • 15
  • 19