Distinguish empty string and null value on COPY command

user_672305 0
Viewed 30

I have the following command that reads and insert from stdin in a SQL file:

COPY test_table (id, col1, col2) FROM stdin DELIMITER ',' ;
1, "",,
2, "",,

as above the col1 values should be treated as empty string but instead I get an error:

null value in column "col1" violates not null constraint
postgresql
1 Answers

three , (comma) actually you are inserting 4 fields/columns!

You can try

COPY test_table (id, col1, col2) FROM stdin  with (format csv, force_not_null(col1), DELIMITER ',') ;
1,"",
2,"",
\.

or

COPY test_table (id, col1, col2) FROM stdin  with (format csv, force_not_null(col1,col2), DELIMITER ',') ;
3,"",
4,"",
\.

Both query will make col1 value as empty string. But the first one will make col2 value to null, the second query will make col2 value as empty string.

user_156034770
Related