Postgres, COPY data into table but "" causing an error

Question

I am copying a few hundred lines of a TSV into a postgres database, that will hopefully serve a cube.js dashboard. However where there is a empty value an error is being thrown. The error details that "" is not a NUMERIC data type, i'm wondering whether or not it is possible whilst COPYing data into the table it could be ignored and default to NULL if the value doesn't equal the data type specified when creating the table. My workflow so far has been:

CREATE TABLE DataFrame(
sample_id VARCHAR(20) DEFAULT NULL,
assignment NUMERIC DEFAULT NULL);

COPY DataFrame FROM '{ABSOLUTE_LOC}dump_050321.tsv.sorted' DELIMITER E'\t'

Error is:
ERROR:  22P02: invalid input syntax for type numeric: ""
CONTEXT:  COPY dataframe, line 8, column assignment: ""
LOCATION:  set_var_from_str, numeric.c:5999

This is a shortened version of the data in use that encompasses the error (the real data is 20 odd columns). As you can see the final line has no assignment, causing the above error.

#sample_id    assignment      
Buf1_1       99.1    
Ser1_1       99.84
Uni1_1       97.05
Tem1_1       99.48
Biv1_3       97.35
Chl1_1       99.04 
Tor1_1       99.44 
Chr1

So yes, is there a way of seeing this and then swapping it with a NULL whilst loading into the database or should i go back to my python script which generates this and assign empty strings as 'NULL' rather than None.

Thanks for any help.

Laurenz Albe · Accepted Answer · 2021-03-09 10:13:53Z

1

Tell COPY that an empty string represents a NULL value:

COPY DataFrame FROM '{ABSOLUTE_LOC}dump_050321.tsv.sorted' (NULL '');

answered Mar 9, 2021 at 10:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

DLBPointon Over a year ago

Thanks a ton, can't believe it was so simple i ended up having to change it a little as it didn't like the brackets. So i used: COPY DataFrame FROM '{ABSOLUTE_LOC}dump_050321.tsv.sorted' DELIMITER E'\t' NULL '';

Laurenz Albe Over a year ago

In fact, COPY prefers the parentheses, and the syntax you use is deprecated. Note that I deliberately omitted DELIMITER in my example, as that is the default value.

DLBPointon Over a year ago

Ah ok, i'll add that to my notes then. I've only been working with this for a day and alot of the tutorials i have found must be in this older style then. Again thanks for the help.

Collectives™ on Stack Overflow

Postgres, COPY data into table but "" causing an error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest