#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    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.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:964)
    at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:826)
    at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.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
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    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?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0
    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:

    Code:
    COPY targetTable FROM STDIN WITH DELIMITER '|'
    Then everything magically worked.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    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.

IMN logo majestic logo threadwatch logo seochat tools logo