July 11th, 2013, 07:49 PM
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"
The target table is like:
FileInputStream fis = new FileInputStream(new File("D:/dataToLoad"));
CopyManager cm = new CopyManager((BaseConnection) conn);
cm.copyIn("COPY targetTable FROM STDIN DELIMITER '|' NULL '\\N' ", fis);
(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?
July 11th, 2013, 09:21 PM
The error looks like you're program is trying to insert an actual 'N' instead of an \N into the column. I'm not familiar with JDBC so I don't know what string-escape stuff goes on there, but this is how the error reads.
You could check by hopping one level below JDBC and writing the values you want to a file, and then using COPY directly from psql to test whether or not the input you know for certain looks a certain way is being rejected or not. I just toyed with this in Postgres 9.2 to make sure there's not some weird COPY bug, but using COPY directly with default NULL values works fine. Which makes me wonder if there is just some tiny malformation of the strings being passed in from the source side in your case.
Can you dump the source strings to a file instead of COPY and inspect them?
July 12th, 2013, 04:50 AM
Thank you for pointing me towards the \N, that led me to the solution.
While there's probably nothing wrong with specifying the null identifier the way we do ('\\N' with an extra escape because it's Java code), someone somewhere is choking on that \N.
So I figured out that the null identifier is already \N by default when the format is text, which is what we use. And removed that null identifier from the command, leaving it as just:
Then everything magically worked.
COPY targetTable FROM STDIN WITH DELIMITER '|'
July 12th, 2013, 10:01 AM
Great to hear it worked out!
I actually wrote, then erased, a sentence stating that "\N is already the default, why specify it again?" but wasn't sure if there was something else in your environment that made this mandatory. Anyway, good to know you got things sorted.