Unable to replace dash with null during COPY operation from CSV

user_72437 0
Viewed 71

I have the following CSV data

"AG","Saint Philip","AG-08"
"AI","Anguilla","-"
"AL","Berat","AL-01"

I want to replace - with NULL

I use the following command

copy subdivision from '/tmp/IP2LOCATION-ISO3166-2.CSV' with delimiter as ',' NULL AS '-' csv;

The copy operation is success. However, - in 3rd column is being copied as well, instead of replaced with NULL.

Do you have idea what mistake in my command? My table is

CREATE TABLE subdivision(
    country_code TEXT NOT NULL,
    name TEXT NOT NULL,
    code TEXT
);
postgresql
1 Answers

It comes down to the quoting. If you have this:

"AI","Anguilla",-
"AL","Berat","AL-01"

Then the below works(using newer COPY format):

copy 
   subdivision 
from 
    '/home/postgres/csv_test.csv' 
with(format csv, delimiter  ',' , NULL '-');

COPY 3

\pset null NULL

select * from subdivision ;
 country_code |     name     | code  
--------------+--------------+-------
 AG           | Saint Philip | AG-08
 AI           | Anguilla     | NULL
 AL           | Berat        | AL-01

If you maintain the original csv:

"AG","Saint Philip","AG-08"
"AI","Anguilla","-"
"AL","Berat","AL-01"

then you have to do this:

copy 
   subdivision 
from 
   '/home/postgres/csv_test.csv' 
with(format csv, delimiter  ',' , NULL '-', FORCE_NULL (code) );

select * from subdivision ;
 country_code |     name     | code  
--------------+--------------+-------
 AG           | Saint Philip | AG-08
 AI           | Anguilla     | NULL
 AL           | Berat        | AL-01

where FORCE_NULL is:

https://www.postgresql.org/docs/current/sql-copy.html

FORCE_NULL

Match the specified columns' values against the null string, even if it has been quoted, and if a match is found set the value to NULL. In the default case where the null string is empty, this converts a quoted empty string into NULL. This option is allowed only in COPY FROM, and only when using CSV format.

So to convert quoted values you have to force the conversion by specifying the columns(s)

user_70706130
Related