Assigning null value to a numeric column while importing csv

Question

I am using the next cmd to import a csv file to my PostgreSQL table:

\copy myTable from 'myTable_sample_nh.csv' with delimiter E'\t' null as '\x00';
ERROR:  invalid input syntax for type numeric: ""
CONTEXT:  COPY myTable, line 1, column salary: ""

How can I do to bypass this error? I mean, during an INSERT row operation, if I do not specify a column then the DB does not assign a value to that column. In this case, the CSV file does not have a value for a numeric column field so the intention is to assign no value to that column.

Daniel Vérité · Accepted Answer · 2020-05-14 17:41:05Z

'\x00' does not mean a zero byte, it means backslash followed by x followed by 00.

Considering that, it actually works:

create table test(n numeric);

\copy test from stdin with delimiter E'\t' null as '\x00'
>> \x00
>> \.

test=> select n is null from test;
 ?column? 
----------
 t

But presumably what you meant is a null byte to represent a SQL NULL. Syntactically, it can be written as E'\x00' but in practice it's unusable: for implementation reasons, null bytes in strings are not supported by Postgres, in pretty much any context. In the context of the null specification of \copy it would be rejected even before submitting input data:

\copy test from stdin with delimiter E'\t' null as E'\x00'
ERROR:  invalid byte sequence for encoding "UTF8": 0x00

The solution is to use something else that doesn't appear in the data, or the empty string: null as '' is accepted by \copy.

Collectives™ on Stack Overflow

Assigning null value to a numeric column while importing csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related