Which data type I need to use to store value between 0 to 1 upto 2 decimal points in SQL-CodePudding

I need to add column in table called value which needs to store value from 0 to 1 up to 2 decimal points i.e. 0.25 0.50 0.75 0.80 etc..

Can anyone help with this? Thanks in advance

CodePudding user response：

Assuming that your specified range includes both bounds, use decimal(3,2) or numeric(3,2). Add a CHECK constraint to reject negative numbers. Online demo.

create table test (
  yournum numeric(3,2),
  check (yournum between 0 and 1));

Test (note the rounding behaviour):

insert into test values
  (-0.0049),
  (-0.001),
  (0),
  (0.001),
  (0.2),
  (0.201),
  (0.234),
  (0.2345),
  (0.2349),
  (0.235),
  (0.239),
  (1),
  (1.001),
  (1.0049)
returning *;
-- yournum
-----------
--    0.00
--    0.00
--    0.00
--    0.00
--    0.20
--    0.20
--    0.23
--    0.23
--    0.23
--    0.24
--    0.24
--    1.00
--    1.00
--    1.00
--(14 rows)
--INSERT 0 14

Example rejects:

insert into test values (-0.005) returning *;
--ERROR:  new row for relation "test" violates check constraint "test_yournum_check"
--DETAIL:  Failing row contains (-0.01).
insert into test values (1.005) returning *;
--ERROR:  new row for relation "test" violates check constraint "test_yournum_check"
--DETAIL:  Failing row contains (1.01).
insert into test values (11) returning *;
--ERROR:  numeric field overflow
--DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.
insert into test values ('Infinity'::float) returning *;
--ERROR:  numeric field overflow
--DETAIL:  A field with precision 3, scale 2 cannot hold an infinite value.
insert into test values ('NaN'::float) returning *;
--ERROR:  new row for relation "test" violates check constraint "test_yournum_check"
--DETAIL:  Failing row contains (NaN).

CodePudding user response：

I personally would use smallint and, store the value multiplied by 100 and add a check constraint that makes sure that the value is between 0 and 100. This minimizes storage space and makes calculations faster.

The down side is of course that some of your calculations have to be changed. Addition is pretty straightforward, but you'd have to divide by 100 after multiplying two numbers.