Rails migration to Postgres issue (time)

Question

I've been working on a rails app locally for some time now and for the most part the application has stayed in localhost. I've made one deployment to Heroku as a test and have developed the application more since. Well now I am wanting to push newer versions to heroku but I am having the worst time. First off, I've lived and learned the importance of consistant databases (test/dev used sqlite3, heroku uses postgres).

I've moved the test/dev databases from sqlite3 to postgres which works fine, but I am still having issues when pushing to heroku that I am not having locally, even though I am using postgres.

Here is the error I am getting:

== 20150713172604 ChangingRoomTimeToDatetime: migrating =======================
-- change_column(:rooms, :start_time, :timestamptz)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::DatatypeMismatch: ERROR:  column "start_time" cannot be cast automatically to type timestamp with time zone
HINT:  Specify a USING expression to perform the conversion.
: ALTER TABLE "rooms" ALTER COLUMN "start_time" TYPE timestamptz/app/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.8/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `async_exec'

Originally I was using time datatype for rooms until I realized that date was important too:

class AddingTimeToRoomModel < ActiveRecord::Migration
  def change
    add_column :rooms, :start_time, :time
    add_column :rooms, :end_time, :time
  end
end

Change:

class ChangingRoomTimeToDatetime < ActiveRecord::Migration
  def change
    change_column :rooms, :start_time, :timestamptz
    change_column :rooms, :end_time, :timestamptz
  end
end

Data is not important at this moment since the only data in database was test data that I've accidentally deleted/dropped anyway. I've tried to use :timestamp and :timestamptz but I just cannot get anything to work and am confused why it works locally but not on heroku.

You're trying to convert a time-of-day to a full timestamp, what date should go with the timestamps? — mu is too short
– mu is too short, Commented Jul 30, 2015 at 15:19
Sorry I am not fully sure what you mean. When I initially set it up I used :time then realized I'd need the date because its a sub-event that is part of an event so I'd need the date so switched to :datetime. Then started getting error on this migration when using heroku/postgres. I don't care too much about timestamp, I just need to have the date and time on this field — ajk4550
– ajk4550, Commented Jul 30, 2015 at 15:26
A time looks like HH:MM:SS but a timestamp is YYYY-MM-DD HH:MM:SS, you can't convert a time to a timestamp without the YYYY-MM-DD part. — mu is too short
– mu is too short, Commented Jul 30, 2015 at 15:28
Oh I see what you're saying, is there a way to set a default value, or should I just change the initial add_column to be a timestamp and rollback migrations? As I said, there is no data in the database except one or two test values that don't matter — ajk4550
– ajk4550, Commented Jul 30, 2015 at 15:31
If you don't care about the data then just drop the column in one migration and add the new version in another. — mu is too short
– mu is too short, Commented Jul 30, 2015 at 15:59

mu is too short · Accepted Answer · 2015-07-30 17:01:20Z

The problem is that a timestampz has more information than a time. From the fine manual:

timestamp with time zone
both date and time, with time zone
[...]
time
time of day (no date)

You can add a date to your times while converting the column type to timestampz using a USING clause:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

However, your comments say that you don't care about the data you have so I'd just drop the existing column with one migration and then re-add the column with another migration:

class RemoveTimeFromRoomModel < ActiveRecord::Migration
  def change
    remove_column :rooms, :start_time
    remove_column :rooms, :end_time
  end
end

class ReAddTimeToRoomModel < ActiveRecord::Migration
  def change
    add_column :rooms, :start_time, :timestampz
    add_column :rooms, :end_time, :timestampz
  end
end

I'm not sure what SQLite will do with :timestampz though, it'll probably end up as text. OTOH, developing on one database and deploying on another is a shortcut to pain and suffering so I'd recommend that you switch your development environment to PostgreSQL so that you don't have to worry about it.

Jim Stewart · Accepted Answer · 2016-10-20 17:20:43Z

1

You can do this with an explicit USING expression and a type cast:

change_column :rooms, :start_time, 'timestamptz using start_time::timestamptz'

answered Oct 20, 2016 at 17:20

Jim Stewart

17.4k5 gold badges77 silver badges91 bronze badges

Collectives™ on Stack Overflow

Rails migration to Postgres issue (time)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related