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

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,

Comments

  •  

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file