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
– Andrew Lazarus, Commented Oct 13, 2017 at 17:39
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
– Sebastian Widz, Commented Oct 13, 2017 at 19:09

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

1

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.

answered Oct 13, 2017 at 15:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian Widz Over a year ago

Thanks for your suggestion. For the moment, I turned to other workaroud. TEMP TABLE + UPDATE + INSERT (see my comment under the original post). The important question is should default values work with COPY operation, when the column is explicitly mentioned in the COPY and how to avoid type mismatch.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related