BigDecimal (numeric) attributes are reported as Integer

[konstantin-dzreev]

Complete Description of Issue

I have a very simple table with a numeric(39) field in a PostgreSQL database. When I use the ORM wrapper it does not seem to properly detect the type of the numeric field, and uses Integer class instead of BigDecimal.

Simplest Possible Self-Contained Example Showing the Bug

# Migration

Sequel.migration do
  change do
    create_table(:bananas) do
      primary_key :id, type: :Bignum
      BigDecimal :uid, size: [39]
    end
  end
end

# Model

class Banana < Sequel::Model
end

# Test

>>> b = Banana.new(uid: BigDecimal(999999999999999999999999999999999999999))
=> #<Banana @values={:uid=>999999999999999999999999999999999999999}>

# Why is this :integer?
>>> Banana.db_schema[:uid][:type]
=> :integer

>>> b.uid.class
=> Integer

# It fails to save
>>> b.save
Sequel::Postgres::IntegerOutsideBigintRange: attempt to literalize Ruby integer outside PostgreSQL bigint range: 999999999999999999999999999999999999999
from /app/vendor/bundle/ruby/3.2.0/gems/sequel-5.81.0/lib/sequel/adapters/shared/postgres.rb:2451:in `literal_integer_outside_bigint_range'

# Lets force it to be :numeric
>>> Banana.db_schema[:uid][:type] = :numeric
=> :numeric

>>> b = Banana.new(uid: BigDecimal(999999999999999999999999999999999999999))
=> #<Banana @values={:uid=>0.999999999999999999999999999999999999999e39}>

>>> b.uid.class
=> BigDecimal

# It now works
>>> b.save
=> #<Banana @values={:id=>2, :uid=>0.999999999999999999999999999999999999999e39}>

Full Backtrace of Exception (if any)

No response

SQL Log (if any)

No response

Ruby Version

ruby 3.2.4

Sequel Version

5.81.0

[jeremyevans]

A numeric column with precision but no scale implicitly is a scale of 0, and is equivalent to an integer column with a range specified by the precision, which is why Sequel treats the column as integer.

If you provide a positive scale (e.g. size: [39, 2]) and then Sequel will treat the column as decimal, since then it will be capable of storing non-integer values. See https://www.postgresql.org/docs/current/datatype-numeric.html for details on the numeric data type in PostgreSQL.

For security reasons, Sequel does not literalize Ruby integers outside the 64-bit range by default (see https://code.jeremyevans.net/2022-11-01-forcing-sequential-scans-on-postgresql.html for the reason behind this). However, you can use the pg_extended_integer_support extension to override this:

DB.extension :pg_extended_integer_support

DB.transaction(:rollback=>:always) do
  DB.create_table(:bananas) do
    primary_key :id, type: :Bignum
    BigDecimal :uid, size: [39]
  end

  class Banana < Sequel::Model
  end

  Banana.create(uid: BigDecimal(999999999999999999999999999999999999999))
  # INSERT INTO "bananas" ("uid") VALUES ('999999999999999999999999999999999999999') RETURNING *
end

If you'd like to treat the column as decimal even though it can only contain integer values, please explain why, and I can consider adding support for it.