Postgres CSV import - handle empty strings as integers

Question

I have a ton of CSV files that I'm trying to import into Postgres. The CSV data is all quoted regardless of what the data type is. Here's an example:

"3971","14","34419","","","","","6/25/2010 9:07:02 PM","70.21.238.46 "

The first 4 columns are supposed to be integers. Postgres handles the cast from the string "3971" to the integer 3971 correctly, but it pukes at the empty string in the 4th column.

PG::InvalidTextRepresentation: ERROR: invalid input syntax for type integer: ""

This is the command I'm using:

copy "mytable" from '/path/to/file.csv' with delimiter ',' NULL as '' csv header

Is there a proper way to tell Postgres to treat empty strings as null?

CSV data is always going to be strings as it is a text format. The presence of quotes is determined by the program settings for outputting the data. As to your issue look at FORCE_NULL here COPY. Or have the program that is outputting the data not quote everything so you end up with: 3971,14,34419,,, ... — Adrian Klaver
– Adrian Klaver, Commented Aug 24, 2021 at 17:37
Fair enough. I should have said that the data is all quoted. I fixed the question. — kid_drew
– kid_drew, Commented Aug 24, 2021 at 17:51

Adrian Klaver · Accepted Answer · 2021-08-24 19:06:45Z

1

How to do this. Since I'm working in psql and using a file that the server user can't reach I use \copy, but the principle is the same:

create table csv_test(col1 integer, col2 integer);

cat csv_test.csv 
"1",""
"","2"

\copy csv_test from '/home/aklaver/csv_test.csv' with (format 'csv', force_null (col1, col2));
COPY 2

select * from csv_test ;
 col1 | col2 
------+------
    1 | NULL
 NULL |    2

answered Aug 24, 2021 at 19:06

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

2 Comments

kid_drew Over a year ago

From your example, I have to know ahead of time which columns will need this, right?

Adrian Klaver Over a year ago

Or you could include all the columns, in which case all '' will be turned to NULL. It just depends on whether you are depending on char columns to have '' values or not. I know there are systems that count on '' IS NULL.

Collectives™ on Stack Overflow

Postgres CSV import - handle empty strings as integers

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related