I'm using the following psql command to import data to the postgresql database:
\COPY tablename FROM '/home/user/mycsv.txt' WITH CSV HEADER DELIMITER ';' NULL AS 'null';
This command works fine for csv files that have the same number of columns that tablename. However, I'm on a project where there are old csv files with less columns than the new files and I need to insert these old files inside postgresql. Is there any way of making the \copy command to insert the missing data as null on the extra columns instead of returning the following error?
ERROR: missing data for column "firstMissingColumn"
Right now, the only possible solution that I see would be modifying the old csv files by filling the extra columns with null. But I don't see that as an ideal solution. Is there any easier way of solving this problem? Can I insert files with less columns than the created table using \copy?
You can list target columns. The manual:
For
COPY FROM, each field in the file is inserted, in order, into the specified column. Table columns not specified in theCOPY FROMcolumn list will receive their default values.
Unless you have set custom column defaults, the default is null like you desire. So:
\COPY tablename(col1, col2, col3) FROM '/home/user/mycsv.txt' WITH CSV HEADER DELIMITER ';' NULL AS 'null';