PostgreSQL: COPY from csv missing values into a column with NOT NULL Constraint

Question

I have a table with an INTEGER Column which has NOT NULL constraint and a DEFAULT value = 0;

I need to copy data from a series of csv files.

In some of these files this column is an empty string.

So far, I have set NULL parameter in the COPY command to some non existing value so empty string is not converted to NULL value, but now I get an error saying that empty string is incorrect value for the INTEGER column.

I would like to use COPY command because of its speed, but maybe it is not possible. The file contains no header. All columns in the file have their counterparts in the table.

It there a way to specify that:

an empty sting is zero, or
if there is en empty string use the default column value?

Do you mean empty string as in ''? Then the problem is a type mismatch. Your setup with default of 0 should work fine if the column is simply empty 123,,456, etc. — Andrew Lazarus
Yes, I believe the problem is as you say a type mismatch, but for some reason it does not work. I have no experience with postgreSQL, but it seems to me that COPY is like BULK insert and no defaulting will work. I have implemented a workaround, I use the temp table with NULLs allowed then UPDATE all NULLs to default values and finally insert records into the target table. I can post the working example tomorrow together with file examples — Sebastian Widz

Laurenz Albe Laurenz Albe · Accepted Answer · 2017-10-13T15:35:30

You could create a view on the table that does not contain the column and create an INSTEAD OF INSERT trigger on it. When you COPY data into that view, the default value will be used for the table. Don't know if the performance will be good enough.

PostgreSQL: COPY from csv missing values into a column with NOT NULL Constraint

1 Answers