Rails PostgreSQL wrong decimal to numeric convertion after migration

[Rodrigo Lueneberg]

After running a migration I noticed that rails is changing the specified
decimal data type in PostgreSQL. Is there a reason for that?

According to this post:
http://stackoverflow.com/questions/1841915/difference-betweeen-decimal-and-numeric
I found that there is a slight difference between decimal type and
numeric type:


NUMERIC must be exactly as precise as it is defined — so if you define 4
decimal places, the DB must always store 4 decimal places.

DECIMAL must be at least as precise as it is defined. This means that
the database can actually store more digits then specified (due to the
behind-the-scenes storage having space for extra digits). This means the
database might store 1.00005 instead of 1.0000, affecting future
calculations.


class CreateBooks < ActiveRecord::Migration
def change
create_table :books, id: :uuid do |t|
t.decimal :price
t.string :title
t.timestamps
end
end
end

After running this migration, the column price is of numeric type
instead of decimal.

Any feedback is appreciated

Rod

--
Posted via http://www.ruby-forum.com/.

[Hassan Schroeder]

On Wed, Jan 28, 2015 at 6:03 AM, Rodrigo Lueneberg <li...@ruby-forum.com> wrote:

> According to this post:
> http://stackoverflow.com/questions/1841915/difference-betweeen-decimal-and-numeric
> I found that there is a slight difference between decimal type and
> numeric type:

Via: http://www.postgresql.org/docs/9.4/static/datatype-numeric.html

"The types decimal and numeric are equivalent. Both types are part of
the SQL standard."

HTH,
--
Hassan Schroeder ------------------------ hassan.s...@gmail.com
http://about.me/hassanschroeder
twitter: @hassan

[Rodrigo Lueneberg]

OK, but if they are the same there would be no need for 2 different
types under PostgreSQL database. According to the post link provided,
decimal is more flexible allowing to store less decimal as opposed to
numeric which requires all decimal places to be filled in. This means
that using decimal I could stored the numbers 12345.12345 as well as
12345.12. In the other hand, numeric would only accept 12345.12345 or
12345.12000 if I understood it right.

[Hassan Schroeder]

On Wed, Jan 28, 2015 at 8:27 AM, Rodrigo Lueneberg <li...@ruby-forum.com> wrote:
> OK, but if they are the same there would be no need for 2 different
> types under PostgreSQL database. According to the post link provided,

So you prefer to believe a 5-year-old SO post rather than actual
product documentation?

Up to you. Regardless, while I read it as numeric and decimal being
the same thing, you can test that theory yourself. Here's one way:

testdb=# create table things ( name varchar(255), price decimal );
testdb=# \dS things
Table "public.things"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(255) |
price | numeric |

This is on a PG 9.3.5 installation, BTW; you didn't mention what
version you're running, so YMMV.

[Rodrigo Lueneberg]

Thank. Don´t need to say anything else. Indeed it is not rails fault.

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit


Hassan Schroeder wrote in post #1167348: