Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    Unanswered: Copy "" as null to timestamp and integer

    Hi All,

    I have a CSV file where the null values are present as "". I am trying to copy those contents into my postgres table.

    My Query is:
    COPY table1 FROM 'data.csv' with delimiter ',' csv header null as '';

    Error :
    ERROR: invalid input syntax for type timestamp: ""
    CONTEXT: COPY table1, line 2, column login_dt: ""

    where login_dt is a column with type timestamp without time zone.

    Guide me how can I load the data properly.

    Thanks in advance

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    One approach is to first import the data into a staging table.

    Then, run a query that copies data from the staging table into the final table, using the case construct or the NULLIF function in the select subquery, to insert a null in the target field when the source field is an empty string. (or, more accurately, when the staging table source field contains just the two quotation characters "")
    Code:
    Select field1, NULLIF(field2, '""') AS field2, NULLIF(field3, ''"") as field3 FROM staging_table
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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