Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: Inserting null values for intervals using CopyManager fails

    Hi guys,
    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)


    Code:
    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
    222|\N

    ...

    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?
    Thanks!

  2. #2
    Join Date
    Jul 2013
    Posts
    2
    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:
    Code:
    COPY targetTable FROM STDIN WITH DELIMITER '|'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •