postgres import csv and auto populate ID and created on and updated on

Question

I just switched from sqlite to postgresql for my rails app and attempting to import data into the database via csvs. I have been able to import the data just fine, however, I cannot get the id, created on, and updated on fields to auto populate when I import the csv. I am leaving them blank on the csv file. Is there a way to get those fields to auto populate when I import the csv?

Answer 1

If you want some fields to be set to default values when importing with COPY, you need to omit those fields from the COPY command.

Instead of:

COPY tablename FROM 'somefile.csv' FORMAT csv;

you'd write:

COPY tablename (col1, col2, col3) FROM 'somefile.csv' FORMAT csv;

and completely omit the columns from the CSV data, rather than just leaving them blank. When you omit the columns, they're automatically set to DEFAULT.

Unfortunately PostgreSQL has no option to ignore columns of the input CSV, so the columns of the CSV must match the columns in the COPY list. If you can't modify the CSV input, you can COPY to a TEMPORARY or UNLOGGED table with the same structure, then do an INSERT INTO real_target (col1, col2, col3) SELECT col1, col2, col3 FROM the_temp_table;