1

How can I COPY a CSV file in Postgres along with static data for the columns not in the CSV? In MySQL I can do LOAD DATA INFILE ... and profile the column names along with any values for the columns not present in the file. I want to do something similar with Postgres but this is not supported by the COPY command.

4

You will have to create an intermediate table as the target of COPY, and then insert into the final table in the second step.

If you don't have to keep the intermediate table (for cross-checking your results, or as an easy retry point), you can make it temporary and unlogged:

BEGIN;

CREATE TEMP UNLOGGED TABLE intermediate (... columns matching the CSV contents ...);

COPY intermediate FROM 'your.csv' WITH (FORMAT csv, ...);

INSERT INTO final_table (column1, column2, column_with_default1)
SELECT col1, col2, 'default'
  FROM intermediate;

-- check if everything looks fine, row counts match the expectation, etc.

COMMIT;

UNLOGGED will prevent a lot of WAL writes when the CSV is big. I put the defaults into the SELECT part of the INSERT instead of the (temp) table to save resources - there is no need to store the same default values many times.

3

PostgreSQL isn't an ETL tool. You should do your ETL with other tools.

If you want to add a column to a CSV, just do something like this:

awk '{print $0, "static value"}' OFS=, my.csv

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.