When using COPY, there is an option called FORCE_NOT_NULL allowing to enforce a string to be not null even if it is not quoted. Here is an example of how it works:
=# CREATE TABLE aa (a text); CREATE TABLE =# \COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(a)); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> "" >> >> \. =# \pset null 'null' Null display (null) is "null". =# SELECT * FROM aa; a --- (2 rows)
In this case, even if the values are empty, FORCE_NOT_NULL forces them to be inserted as empty strings and not NULL values. Now, let’s look at the new option FORCE_NULL that is introduced in Postgres 9.4 by this commit, feature actually written by Ian, hacker living in Japan as well as the author of this blog.
commit 3b5e03dca2afea7a2c12dbc8605175d0568b5555 Author: Andrew Dunstan <firstname.lastname@example.org> Date: Tue Mar 4 17:31:59 2014 -0500 Provide a FORCE NULL option to COPY in CSV mode. This forces an input field containing the quoted null string to be returned as a NULL. Without this option, only unquoted null strings behave this way. This helps where some CSV producers insist on quoting every field, whether or not it is needed. The option takes a list of fields, and only applies to those columns. There is an equivalent column-level option added to file_fdw. Ian Barwick, with some tweaking by Andrew Dunstan, reviewed by Payal Singh.
Contrary to FORCE_NOT_NULL, this can be used to force an empty value to be inserted as NULL even if it is quoted.
=# TRUNCATE aa; TRUNCATE TABLE =# \COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NULL(a)); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> "" >> >> \. =# SELECT * FROM aa; a ------ null null (2 rows)
file_fdw comes up as well with a similar new option, and it can be used on columns.
=# CREATE EXTENSION file_fdw; CREATE EXTENSION =# CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; CREATE SERVER =# CREATE FOREIGN TABLE test ( a int, b text OPTIONS (force_null 'true'), c text OPTIONS (force_not_null 'true') ) SERVER file_server OPTIONS ( filename '/path/to/data/test.csv', format 'csv'); CREATE FOREIGN TABLE =# \! cat /path/to/data/test.csv 1,"", 2,,"" =# SELECT * FROM test; a | b | c ---+------+--- 1 | null | 2 | null | (2 rows)
Note that both force_null and force_not_null cannot be specified on the same column.
=# ALTER FOREIGN TABLE test ALTER COLUMN b OPTIONS (force_not_null 'true'); ERROR: 42601: conflicting or redundant options HINT: option "force_not_null" cannot be used together with "force_null" LOCATION: file_fdw_validator, file_fdw.c:274
And that’s all for this short but instructive post, for a feature proved to be useful for Or**le to Postgres migrations.