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

@VaoTsun, thanks for the reply. I found another solution that I've put as the answer below — nLee
– nLee, Commented Mar 22, 2018 at 21:02

Vao Tsun · Accepted Answer · 2018-03-23 08:13:04Z

10

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));

edited Mar 23, 2018 at 8:13

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

answered Mar 22, 2018 at 18:07

nLee

1,3952 gold badges11 silver badges22 bronze badges

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

2 Comments

Denis Or Over a year ago

Big thx! long time searching answer! My code:

copy TableName(place_id,osm_type,problem_column) FROM 'D:\File.csv' WITH (format csv, DELIMITER ';', header true, force_null (problem_column), ENCODING 'UTF8' );

Denis Or Over a year ago

UPD it works for postgresql version 11. check version select version(); for another versions at bottom of this page

Collectives™ on Stack Overflow

Postgres Copy with null date and integer fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related