PGAdmin - import null date value to postgres

Question

I have a CSV file with following format example:

"MC97898","","","file1.txt","01-APR-2020","01-APR-2020"
"MC97899","","","file2.txt","01-APR-2020","01-APR-2020"
"MC97891","","","file3.txt","01-APR-2020","01-APR-2020"
"MC97892","","","file4.txt","01-APR-2020","01-APR-2020"
"MC97893","","","file.xt","01-APR-2020",
"MC97894","","","file.xt","01-APR-2020","01-APR-2020"
"MC97895","","","file.xt","01-APR-2020","01-APR-2020"
"MC97896","","","file.xt","01-APR-2020",

…

1.5 billion records

table xx(receipt_number, id, mig_filename, mig_inserted_dt, mig_modified_dt)

I can run copy in psql but can't import CSV with null value on date (mig_modified_dt) using PGAdmin?
1st it complains about unterminated CSV, then invalid syntax for type timestamp, I altered it to date, still invalid syntax. I put two single quote on NULL strings ('') on PGAdmin 4, still error even '\N' even blank, still error.

Can we import in PGAdmin if the blank string comes through as null value on date/timestamp column?

Community · Accepted Answer · 2023-09-04 05:36:02Z

3

I was having a somewhat similar issue. I wanted to import a CSV file containing the data for a table in PGAdmin. I kept getting this error:

ERROR: invalid input syntax for type integer: "NULL"

I finally found this link:

PgAdmin Null Strings

In PgAdmin, when you right click on the table name > click Import/Export > enter the criteria, then you can click on the "Columns" tab at the top of the pop-up box. There you'll find where you can enter whatever null values you have. For me my CSV, had the word "NULL" for null values. After I typed in NULL in Null Strings, the data imported successfully. Maybe this could work for you.

edited Sep 4, 2023 at 5:36

CommunityBot

11 silver badge

answered Sep 24, 2020 at 16:23

Chnikki

6741 gold badge8 silver badges10 bronze badges

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

3 Comments

Bach-Nga Over a year ago

thank you, I tried that last time after posted, and still not work because the column definition on the table declared as not nullable and somehow the data from other database had null. All need to removed data with null (not nullable column) and import it fine.

greybeard Over a year ago

(If you want line breaks where you put double spaces, follow them with a newline.)

drdwilcox Over a year ago

An update. In later versions of pgadmin, the option to set the NULL string is under "Options", not under "Columns"

Collectives™ on Stack Overflow

PGAdmin - import null date value to postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related