Timestamp breaks sqlite3 import to postgresql

Question

I have a rails app that I used Sqlite3 for the db, but am now switching to use Postgresql.

Per this excellent post, I have set up the postgresql databases, and am now importing the sqlite3 db's to postgres using the taps gem.

The problem is: My sqlite3 db has a column :time, in which I store unix timestamps (integers). The postgres schema says they are 'timetstamp without time zone' however, and are rejecting the current values. This is the error:

PG::Error: ERROR:  column "time" is of type timestamp without time zone but expression is of type integer (Sequel::DatabaseError)LINE 1: ..."address_type", "medical") VALUES (2, 2, 0, NULL, 1367688600...

How do you get around this? Do I need to convert all the time entries in my sqlite3 database to match the postgres schema? Also, since this is only for my dev db, it's not a problem to drop the db values entirely and start afresh -- so if there is an easy way to do this too, I would appreciate knowing that.

Answer 1

To convert the value back you will need to run it through a query like:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + timecol * INTERVAL '1 second';

So what I would do is change your schema in PostgreSQL to make these columns integers. Then I would run them through a job that would add a new timestamp column, and run the above query on it (where timecol is the column name of the old timestamp). Then, once you have spot-checked the data, you can drop the old column and rename the new column to the old name.