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
How can I
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.