Using COPY, ignore "invalid format" integers (or set to NULL)

bezout

Hello,

 

I'm using COPY to load csv files. In a certain integer column, some rows have value "----" instead of NULL or 0. This causes my input records to be rejected (Invalid integer format '----' for column)

Is there a way for me to have them insert anyway, setting the value to 0 or NULL in the COPY statement ? I would prefer to avoid having to edit the csv itself.

 

I was thinking maybe one of these solutions :

- Specify a different parser for this columns only

- Force trim or replace those characters

- Specifiy all incorrect integers should be read as NULL instead of rejected

- Something else ?

 

But I have no idea which of those is possible and/or how to make it work.

 

Thanks in advance,

eli_revach

 

Hi ,
Below is useful method:
o Load the data into staging table where the column is define as varchar
o Load the data into the target table using the new casting capabilities exists from 7.2

Below is an example for  the new casting
SELECT a::!int FROM mytable;

The above casting simulte is_numeric()  function and return null if the value is not numeric

I hope you will find it useful

Thanks

 

Adrian_Oprea_1

 Use the filler option for the COPy command.

 

See example: 

-- this is your data

dbadmin=> \! cat /tmp/test.csv
1
2
3
----
----
4
5
6

Copy cmd 

copy test(
v1 filler varchar(20) , 
id as replace(v1,'----','0')
) from '/tmp/test.csv'  abort on error no commit;

Rows Loaded
-------------
           8
(1 row)

dbadmin=> select * from test;
 id
----
 6
 2
 3
 0
 0
 5
 1
 4
(8 rows)

the only thing i did is make the col data type varchar. depending on your use you can cast it to int.

 

Hope this help ! 

bezout

@adrian_oprea_1 Thanks. I didn't think of combining FILLER and REPLACE. I added a CAST and it worked like a charm.

 

myfiller FILLER VARCHAR,
mycolumn AS CAST(REPLACE(myfiller, '----', '0') AS INT),

 

bezout

@eli_revach Thanks, that was instructive. I marked the other as 'selected answer' because I preferred the solution that does without staging tables.