0

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.

Community
  • 1
  • 1
Aaron Marks
  • 375
  • 2
  • 7
  • 19
  • I just realized after booting up the app, that it actually works now, and is just using the empty postgresql db's (schema got imported via taps, I guess -- just not data). Can I just proceed, or should I destroy those old sqlite3 db's and if so how do you do that? – Aaron Marks Jun 20 '13 at 21:16

1 Answers1

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • And in a Rails environment, you'd either do this stuff by hand via `psql` or, um, do it by hand by stuffing a bunch of SQL into `connection.execute` calls. – mu is too short Oct 27 '13 at 03:10