Unanswered: Inserting null values for intervals using CopyManager fails
I'm using JDBC driver postgresql-9.1-901 and trying to load data into a table with an interval column (e.g. interval day to second) from a delimited file. Postgres complains about the null interval values:
org.postgresql.util.PSQLException: ERROR: invalid input syntax for type interval: "N"
Where: COPY test_interval_unspec_cache, line 2, column col2: "N"
at org.postgresql.core.v3.QueryExecutorImpl.receiveEr rorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processCo pyResults(QueryExecutorImpl.java:964)
at org.postgresql.core.v3.QueryExecutorImpl.endCopy(Q ueryExecutorImpl.java:826)
at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInIm pl.java:61)
at org.postgresql.copy.CopyManager.copyIn(CopyManager .java:183)
at org.postgresql.copy.CopyManager.copyIn(CopyManager .java:163)
at TestPostgresCopyMgr.main(TestPostgresCopyMgr.java: 24)
FileInputStream fis = new FileInputStream(new File("D:/dataToLoad"));
CopyManager cm = new CopyManager((BaseConnection) conn);
cm.copyIn("COPY targetTable FROM STDIN DELIMITER '|' NULL '\\N' ", fis);
The target table is like: (integer, interval day to second)
and the file is like: 111|1 days 2 hours 0 minutes 0.0 seconds
As you see, we're using the default NULL identifier for Postgres: \N, and that works fine when loading nulls into string columns. However, it fails for interval columns. I've also tried using the word NULL and the unquoted empty string instead of \N in the delimited file, but Postgres throws the same exception for those too.
I don't want to use 0 instead, as 0 is not the same as NULL.
What's the trick to express a null interval value in the file that Postgres will accept?
And the solution turned out to be to just remove the null identifier from the COPY command, because it's already \N by default when the format is text. Not sure why we had it there in the first place, but something in the JDBC layer was choking on it. Here's the command that did the job: