Rails & Postgres: Migration to change_colomn gives error "cannot be cast to type timestamp without time zone"

Question

A Rails migration to turn a "deleted_at" time column to a datetime column failed. Any ideas on how to solve this? It's a fresh install of Postgres if that is relevant.

-- change_column(:products, :deleted_at, :datetime)
 PGError: ERROR:  column "deleted_at" cannot be cast to type timestamp without time zone
: ALTER TABLE "products" ALTER COLUMN "deleted_at" TYPE timestamp

Solution 1

You can't alter a field's type from time to timestamp ("datetime"), because the values couldn't be converted -- the database doesn't know the date.

You can, however, drop and re-create the column:

ALTER TABLE products DROP COLUMN deleted_at;
ALTER TABLE products ADD COLUMN deleted_at timestamp;

Or if this field was set to NOT NULL, you should instead do:

ALTER TABLE products ADD COLUMN deleted_at timestamp NOT NULL;

But if you insist on retaining fake values in this table like Sean, you can use ALTER...TYPE...USING like this:

ALTER TABLE products ALTER COLUMN deleted_at TYPE timestamp USING
    CASE WHEN deleted_at IS NOT NULL THEN timestamp '1970-01-01 00:00:00' END;
-- Or:
ALTER TABLE products ALTER COLUMN deleted_at
    TYPE timestamp USING date '1970-01-01' + deleted_at;

Solution 2

In Rails this would look something like

class ChangeStatusUpdatedAtToDateTime < ActiveRecord::Migration
  def up
    remove_column :bookings, :status_updated_at
    add_column :bookings, :status_updated_at, :datetime
  end
  def down
    remove_column :bookings, :status_updated_at
    add_column :bookings, :status_updated_at, :time
  end
end

If you had data you wanted to transfer you could use the following code (not tested!):

class ChangeStatusUpdatedAtToDateTime < ActiveRecord::Migration
  def up
    add_column :bookings, :temp_status_updated_at, :datetime
    Booking.update_all("temp_status_updated_at = updated_at")
    remove_column :bookings, :status_updated_at
    rename_column :bookings, :temp_status_updated_at, :status_updated_at
  end
  def down
    add_column :bookings, :temp_status_updated_at, :time
    Booking.update_all("temp_status_updated_at = updated_at")
    remove_column :bookings, :status_updated_at
    rename_column :bookings, :temp_status_updated_at, :status_updated_at
  end
end

Comments

[Oto Brglez]

But this will also drop all the values inside deleted_at. How can that be solution?

[intgr]

@OtoBrglez The values are pretty useless to begin with -- the old column type only stores the time of day, but not the date. There's no reasonable way to convert those to datetime.

[Sean]

This is a bad solution. Many systems use deleted_at to determine if a product is deleted or not. Removing the column and reading has the side-effect of un-deleting these records!!

[intgr]

@Sean I think that's a fair price to pay for messing up data types like that :) but I updated the answer for your use case.

[Roger Perez]

What would you do if you already have data within the column?

[Paul Odeon]

Added an example @RogerPerez