Interpret several values as NULL when copying CSV file in PostgreSQL table

Question

I want to copy a CSV file into the table r. In the file, the string "NULL" represents NULL, so I tried:

copy r from 'file.csv' WITH NULL AS 'NULL' DELIMITER ';' CSV HEADER;

This works. But another file contains two values that represent NULL: The string "NULL" and an empty string "". How can I declare that several values should be interpreted as NULL?

technically "" isn't null its a blank string most of the time they are thought of the same but they can have different meanings. You could always just run an update statement after importing to change the several values to NULL. — Matt
– Matt, Commented Sep 27, 2016 at 16:34
I am not sure how to do that, as the column is a timestamp and it only allows timestamps and NULL. — Mark Heckmann
– Mark Heckmann, Commented Sep 27, 2016 at 16:37
yeah you would have to import as VARCHAR then convert to timestamp after handling the invalid values. Sorry I don't know postgressql's import method so I will let one of that platforms experts answer. cheers — Matt
– Matt, Commented Sep 27, 2016 at 16:39

redneb · Accepted Answer · 2016-09-27 18:38:52Z

1

As @Matt alluded, the best option here is to import the column as text and then convert it to timestamptz. So create the table like this:

CREATE TABLE r (
    ...
    t timestamp with time zone,
    ...
);

Then import the data using COPY as you suggested. Finally convert the timestamps like this:

ALTER TABLE r
ALTER COLUMN t
TYPE timestamptz
USING
    CASE t
        WHEN 'NULL' THEN NULL
        WHEN '' THEN NULL
        ELSE t::timestamptz
    END;

answered Sep 27, 2016 at 18:38

redneb

24.3k7 gold badges46 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

Interpret several values as NULL when copying CSV file in PostgreSQL table

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