CODEWITHSUNDEEP
databasepostgresqlcsv
DLBPointon
DLBPointon

Reputation: 27

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

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.

Upvotes: 0

Views: 1503

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

Tell COPY that an empty string represents a NULL value:

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

Upvotes: 1

Related Questions