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

    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
    Arizona, USA
    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 "")
    Select field1, NULLIF(field2, '""') AS field2, NULLIF(field3, ''"") as field3 FROM staging_table
    "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