Postgres copy null into timestamp column

Question

I am following this question to resolve an invalid input syntax for type timestamp when I try to copy an empty end_at column from a csv file into my Postgres table.

The last answer recommended (I am running a 4.2 Rails app) I create temporary table where I copy as a string field and then INSERT INTO the real table using the temporary table data. Because I will be doing this alot I just ran a migration to keep a permanent temp_table (that I truncate after each import). My code is below but I can't figure out how to do the INSERT INTO on the end_at column so that the blank strings come through as null timestamps.

Any hints would be great.

def pg_import data
  ActiveRecord::Base.connection.execute("truncate temp_pricelist_price_groups")
  ActiveRecord::Base.connection.execute("truncate pricelist_price_groups")
  conn = ActiveRecord::Base.connection_pool.checkout
  raw  = conn.raw_connection

  raw.exec("COPY temp_pricelist_price_groups (
                pricelist_id,
                price_group_id,
                uom_id,
                quantity,
                price,
                disc_dollar,
                disc_percent,
                price_flag,
                gross_or_net,
                start_at,
                end_at
                      ) FROM STDIN WITH (FORMAT CSV, DELIMITER ',',  null '', HEADER true)")
  # open up your CSV file looping through line by line and getting the line into a format suitable for pg's COPY...

  ticker = 0
  counter = 0
  success_counter = 0
  failed_records = []

  data = CSV.parse(data)
  data.shift

  data.each_with_index do |line, index|
    line = line.to_csv
    raw.put_copy_data line
    counter += 1
  end
  # once all done...
  raw.put_copy_end
  raw.exec("INSERT INTO pricelist_price_groups 
            SELECT *
            FROM temp_pricelist_price_groups)")
  while res = raw.get_result do; end # very important to do this after a copy
  postgresql_error_message = raw.error_message
  ActiveRecord::Base.connection_pool.checkin(conn)
  ActiveRecord::Base.connection.execute('truncate temp_pricelist_price_groups')
  return { :csv => false, :item_count => counter, :processed_successfully => counter, :errored_records => failed_records, :error_message => postgresql_error_message }
end

Answer 1

It may be a bug with the order of operations in COPY, but when using the CSV format, the NULL '' option has no effect on a quoted empty string ("").

You have a couple of options to mitigate that. Either modify the data to strip "" which will allow NULL '' to force those columns as NULL, or change the timestamp data types in your temporary table to text. Then the quoted empty strings will import without issue.

To deal with the empty strings you'll need to change the INSERT to use NULLIF() to treat the empty string as NULL, and then cast the result to the timestamp type:

INSERT INTO pricelist_price_groups 
        SELECT
            pricelist_id,
            price_group_id,
            uom_id,
            quantity,
            price,
            disc_dollar,
            disc_percent,
            price_flag,
            gross_or_net,
            NULLIF(start_at,'')::timestamp with time zone,
            NULLIF(end_at,'')::timestamp with time zone
        FROM temp_pricelist_price_groups