MySQL double to PostgreSQL double precision works, but default column value does not exist in schema.rb dump

Question

I converted a MySQL database to PostgreSQL in a Rails app, and the MySQL double column was converted to a double precision column in PostgreSQL.

The problem is that even if the PostgreSQL structure has this column with a default value of "0::double precision", rake db:schema:dump does not add this default value.

Therefore, when a model instance is created, the default value is not auto-assigned, and instead its value is nil.

Now, I'm on Rails 4.1.6, and this happens because the column type is not listed here (AFAIK). What is strange (to me) is why this hasn't been added yet.

What could be the recommended approach here? Change the column type in PostgreSQL?

A \d payments from inside psql says:

                                       Table "public.payments"
     Column     |            Type             |                       Modifiers                       
----------------+-----------------------------+-------------------------------------------------------
 id             | integer                     | not null default nextval('payments_id_seq'::regclass)
 credit_card_id | integer                     | not null default 0
 order_id       | integer                     | not null default 0
 amount         | double precision            | not null default 0::double precision
 created_at     | timestamp without time zone | not null
 updated_at     | timestamp without time zone | not null
 authorization  | character varying(510)      | default NULL::character varying
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)
    "credit_card_id" btree (credit_card_id)
    "purchase_order_id_1" btree (order_id)

and the corresponding section of schema.rb says:

create_table "payments", force: true do |t|
  t.integer  "credit_card_id",             default: 0, null: false
  t.integer  "order_id",                   default: 0, null: false
  t.float    "amount",                                 null: false
  t.datetime "created_at",                             null: false
  t.datetime "updated_at",                             null: false
  t.string   "authorization",  limit: 510
end

What does PostgreSQL think the table looks like? Connect your database with psql from the command line and \d table_name. What does the table look like in schema.rb? — mu is too short
– mu is too short, Commented Dec 24, 2014 at 17:55
psql: gist.github.com/favrik/b34637a19d91b56285c9 schema.rb: gist.github.com/favrik/a90bfe77720fab71574e The column I'm referring to is amount. — Favio
– Favio, Commented Dec 25, 2014 at 21:46

mu is too short · Accepted Answer · 2014-12-26 00:08:59Z

1

I think you're right about the problem being in extract_value_from_default inside ActiveRecord; AR tends to ignore anything from the database that it doesn't understand and it doesn't understand the ::double precision cast in amount's definition so AR assumes that there is no default.

Whatever tool you used to convert your MySQL schema and database to PostgreSQL is also sort of at fault. The type cast is not needed, PostgreSQL can convert an integer to double precision all by itself so just:

amount double precision default 0

is sufficient. If you want some sort of reminder in the default that you're dealing with floating point then you could use:

amount double precision default 0.0

AR should understand either of those.

The easiest solution is to fix the default value inside the database and removing the type cast. Going behind AR's back and doing a manual ALTER TABLE should be sufficient. From psql, you could say:

alter table payments alter column amount set default 0;

or inside a migration:

def up
  connection.execute('alter table payments alter column amount set default 0')
end

After that, your db:schema:dump should include the new default value and newly created Payments should have an amount of zero.

As an aside, you might want to switch to the numeric type (AR calls this t.decimal) inside the database. Using floating point for money rarely ends well.

answered Dec 26, 2014 at 0:08

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Favio Over a year ago

Thanks, you rock! :D In fact, I'm using this converter, so I will just modify that to add an AR recognizable default. You're absolutely right about the column type not good for money of course. It's a rails 1 to rails 4 migration...

Collectives™ on Stack Overflow

MySQL double to PostgreSQL double precision works, but default column value does not exist in schema.rb dump

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related