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

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

 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 ! 

@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),

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