using copy from command to load data to postgres from csv

Question

create table emp (emp_id integer,
    emp_name varchar(30),
    registered_course varchar(15),
    progress integer);

I'm having data in csv as :

emp_id, emp_name, registered_course
1,"vignesh","java",10
2,"rakesh","",

i.e., In my csv NULL integer column data is represented as unquoted empty string and NULL varchar data is represented as ""

Now, when I'm trying to upload csv to Postgres using copy from command in place of int columns of postgres I can see NULL is inserted but where as for varchar I can see it's blank/zero length string).

so far my command:

String copyQuery="COPY "+tableName+"("+columns+") FROM STDIN WITH DELIMITER ',' CSV HEADER";
    

long rowsInserted;
    
try {

    rowsInserted = new CopyManager((BaseConnection) conPost)

            .copyIn(

                copyQuery,new FileReader(filePath)

                );

}

In documentation :- https://www.postgresql.org/docs/current/sql-copy.html I can see that NULL Specifies the string that represents a null value and by default it is an unquoted empty string in CSV format. So this is why I can see for null integer data which is in csv as unquoted empty string represented as NULL after loading.

But, now how can I make NULL varchar data which in csv as "" to represented as NULL at postgres ??? as of now it is representing as empty string (blank/zero length).

Thanks in advance

Thanks you @a_horse_with_no_name for providing the current documentation and for correcting my question. Current document resolved my issue :) — vigneshwar reddy
– vigneshwar reddy, Commented Jul 29, 2021 at 5:44

jjanes · Accepted Answer · 2021-07-28 13:07:00Z

1

Use force_null, documented at the same place you already linked.

copy emp from stdin with (format csv, header, force_null (registered_course));

answered Jul 28, 2021 at 13:07

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

using copy from command to load data to postgres from csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related