CODEWITHSUNDEEP
ruby-on-railspostgresqldatetime
Burak Özmen
Burak Özmen

Reputation: 873

Cast old string values to datetime with migration in Rails PostgreSQL

I had a couple of date fields in a database table, however they are firstly initiated as string, not datetime. Therefore, I wanted to change those value types to datetype with a migration, 

class ChangeDateColumnsToDateTime < ActiveRecord::Migration
  def change
    change_column :users, :flight_date_departure, :datetime
    change_column :users, :flight_date, :datetime
    change_column :users, :appointment_date, :datetime
  end
end

however it can not cast old string values to datetimes that exists in database currently, saying that PG::DatatypeMismatch: ERROR: column "flight_date_departure" cannot be cast automatically to type timestamp without time zone. HINT: You might need to specify "USING flight_date_departure::timestamp without time zone". We've done it without problem in a SQLite database, however there is this problem for PostgreSQL. How can I modify my migration so that I do not lose old values and properly convert them to datetime?

Upvotes: 8

Views: 4968

Answers (4)

Christos-Angelos Vasilopoulos
Christos-Angelos Vasilopoulos

Reputation: 940

It would be better to make this a reversible migration.

def up
  change_column :table_name, :column_name, 'timestamp USING CAST(column_name AS timestamp)'
end

def down
  change_column :table_name, :column_name, 'date USING CAST(column_name AS date)'
end

Upvotes: 0

Charlie T Lee
Charlie T Lee

Reputation: 1

here is my working code

class AddSrCreateDateToCart < ActiveRecord::Migration[5.2]
  def change
    add_column :carts, :sr_create_date, :datetime
    change_column :carts, :sr_create_date, 'datetime USING CAST(sr_create_date AS timestamp)'
  end
end

postgres is now downloading the data

Upvotes: 0

Jaques Dias
Jaques Dias

Reputation: 91

I've tried the way bellow and it worked like a charm:

change_column :table_name, :column_name, 'timestamp USING CAST(column_name AS timestamp)'

Upvotes: 9

Rehan Munir
Rehan Munir

Reputation: 269

try like this:

change_column :table_name, :column_name, 'datetime USING CAST(column_name AS datetime)'

Upvotes: 7

Related Questions