Can I get Unix timestamp automatically converted to a TIMESTAMP column when importing from CSV to a PostgreSQL database?

Question

This is basically a duplicate of this with s/mysql/postgresql/g.

I created a table that has a timestamp TIMESTAMP column and I am trying to import data from CSV files that have Unix timestamped rows.

However, when I try to COPY the file into the table, I get errors to the tune of

2:1: conversion failed: "1394755260" to timestamp
3:1: conversion failed: "1394755320" to timestamp
4:1: conversion failed: "1394755800" to timestamp
5:1: conversion failed: "1394755920" to timestamp

Obviously this works if I set the column to be INT.

In the MySQL variant, I solved with a trick like

LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE raw_data
fields terminated by ','
lines terminated by '\n'
IGNORE 1 LINES
 (@timestamp, other_column)
SET timestamp = FROM_UNIXTIME(@timestamp),
    third_column = 'SomeSpecialValue'
;

Note two things: I can map the @timestamp variable from the CSV file using a function to turn it into a proper DATETIME, and I can set extra columns to certain values (this is necessary because I have more columns in the database than in the CSV).

I'm switching to postgresql because mysql lacks some functions that make my life so much easier with the queries I need to write.

Is there a way of configuring the table so that the conversion happens automatically?

Is this a one-time import? In that case, define the column as bigint, import the data, then convert the column to a proper timestamp — user330315
– user330315, Commented Dec 23, 2017 at 11:07

Hambone · Accepted Answer · 2017-12-24 00:04:09Z

I think you could accomplish this by importing the data as-is, creating a second column with the converted timestamp and then using a trigger to make sure any time a row is inserted it populates the new column:

alter table raw_table
add column time_stamp timestamp;

CREATE OR REPLACE FUNCTION raw_table_insert()
  RETURNS trigger AS
$BODY$
BEGIN
  NEW.time_stamp = timestamp 'epoch' + NEW.unix_time_stamp * interval '1 second';
  return NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER insert_raw_table_trigger
  BEFORE INSERT
  ON raw_table
  FOR EACH ROW
  EXECUTE PROCEDURE raw_table_insert();

If the timestamp column can be modified, then you will want to make sure the trigger applies to updates as well.

Alternatively, you can create a view that generates the timestamp on the fly, but the advantages/disadvantages depend on how often you search on the column, how large the table is going to be and how much DML you expect.

Collectives™ on Stack Overflow

Can I get Unix timestamp automatically converted to a TIMESTAMP column when importing from CSV to a PostgreSQL database?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related