Load NULL TIMESTAMP with TIME ZONE using COPY FROM in PostgreSQL
csv, postgresql
Solution
The `FORCE_NULL` option for `COPY FROM` in Postgres 9.4+ would be the most elegant way to solve your problem. Per documentation:
`FORCE_NULL`
Match the specified columns' values against the null string, even if it has been quoted, and if a match is found set the value to `NULL`. In the default case where the null string is empty, this converts a quoted empty string into `NULL`. This option is allowed only in `COPY FROM`, and only when using `CSV` format.
Of course, it converts all matching values in all columns.
In older versions, you can `COPY` to a temporary table with the same table layout - except data type `text` for the problem column. Then fix offending values and `INSERT` from there:
- single quotes appear arround value after running copy in postgres 9.2
Problem
I have a CSV file that I'm trying to load into a PostgreSQL 9.2.4 database using the `COPY FROM` command. In particular there is a timestamp field that is allowed to be null, however when I load "null values" (actually just `""`) I get the following error: ``` ERROR: invalid input syntax for type timestamp with time zone: "" ``` An example CSV file looks as follows: ``` id,name,joined 1,"bob","2013-10-02 15:27:44-05" 2,"jane","" ``` The SQL looks as follows: ``` CREATE TABLE "users" ( "id" BIGSERIAL NOT NULL PRIMARY KEY, "name" VARCHAR(255), "joined" TIMESTAMP WITH TIME ZONE, ); COPY "users" ("id", "name", "joined") FROM '/path/to/data.csv' WITH ( ENCODING 'utf-8', HEADER 1, FORMAT 'csv' ); ``` According to the documentation, null values should be represented by an empty string that cannot contain the quote character, which is double quote (`"`) in this case: NULL Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don't want to distinguish nulls from empty strings. This option is not allowed when using binary format. Note: When using COPY FROM, any data item that matches this string will be stored as a null value, so you should make sure that you use the same string as you used with COPY TO. I've tried the option `NULL ''` but that seems to have no affect. Advice, please!