CODEWITHSUNDEEP
postgresql
Chris Kessel
Chris Kessel

Reputation: 5875

Use COPY but table has extra columns?

I've got a CSV file from a vendor. I want to COPY it into a table, except the table has a couple extra columns I want populate via derivation in a trigger. Fairly straight forward copy: copy mytable from '/some/file.csv' DELIMITER ',' csv;

The copy command fails telling me there's missing data: ERROR: missing data for column "arrival_dates"

Well, of course, that column isn't in the original csv. Is there a way to force copy to just fill in the columns it is getting? Essentially, the csv has the first 18 columns, then there are two more I'm deriving and populating via trigger. I've tried assigning a DEFAULT to the columns in the table declaration, but that didn't help.

Upvotes: 1

Views: 602

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246808

That's fairly trivial. Like INSERT, COPY allows you to specify the columns that the fields of the input field correspond to.

If your table has col1, col2, col3 and col4, but the file does not contain the third column, you can simply

COPY atable(col1, col2, col4)
FROM '/some/file.csv'
   (FORMAT 'csv', DELIMITER ',');

Upvotes: 4

Related Questions