Postgres Copy with null date and integer fields

Question

I have a java app that is streaming data from possibly large files into a postgres RDS via a JDBC connection.

I am using the following command which works for null integer types.

COPY tableName FROM STDIN with (format csv, delimiter E'\u0001', NULL '', QUOTE E'\u0005')

However this does not work for null date fields (PSQLException: ERROR: invalid input syntax for date: "")

If I modify the command to

COPY tableName FROM STDIN with (format csv, delimiter E'\u0001', NULL '\N', QUOTE E'\u0005')

It works for date fields, but not for integer fields. (PSQLException: ERROR: invalid input syntax for integer: "\N")

I've seen similar questions on here addressing either nulls for integer fields or nulls for date fields, but not both, so I'm wondering if there is a way to specify null that will work with both integer and date fields (or a way to specify multiple different null strings for different data types)

I would really like to use COPY as the performance is much better than parsing the file and inserting each record, so hoping this is possible

Answer 1

So with Postgres 9.4+ This is achievable using FORCE_NULL

based on the documentation

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.

So my modified SQL is as follows:

COPY tableName FROM STDIN with (format csv, delimiter E'\u0001', null '', quote E'\u0005', force_null(dateField1, dateField2,...,dateFieldN));