CODEWITHSUNDEEP
sqlpostgresqldatabase-designtypesconstraints
Michał Ziembiński
Michał Ziembiński

Reputation: 1154

How to specify min and max digits for a bank account number?

Is it possible to constrain the number of digits allowed in a column of integer data type in PostgreSQL. I have the following example:

CREATE TABLE bank_accounts (    
    id              SERIAL       PRIMARY KEY
  , number_account  INTEGER(26)  NOT NULL
);

We can enter something like:

1 -- one digit
23 -- two digits
444 -- three digits
5555 -- four digits

Etc. ... up to 26 digits.

But I want to constrain my column to store exactly 26 digits, not less and not more. How to achieve that?

Upvotes: 3

Views: 2633

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

A bank account number is not an integer by nature. 26 decimal digits are too much for integer or bigint anyway.

A bank account number is not a numeric value at all, really, even if we could use the type numeric for storage. It can handle 26 decimal digits easily. But it also allows fractional digits (and other decorators, like @klin commented). You can restrict to numeric(26), which is short for numeric(26,0), to remove fractional digits from storage. But that still allows fractional digits on input, which are then rounded off. And other decorators. All of these seem undesirable for a bank account number:

SELECT numeric(26) '12345678901234567890123456'
     , numeric(26) '12345678901234567890123456.4'  -- rounded down
     , numeric(26) '12345678901234567890123456.5'  -- rounded up
     , numeric(26) '1e25'
     , numeric(26) '1.2345e25'
     , numeric(26) '+12345678901234567890123456.5'

SELECT numeric(26) '99999999999999999999999999.5'  -- error after rounding up

A bank account number is more like text by nature, so data type text seems more appropriate (like @klin provided), even if that occupies a bit more space on disk (like @a_horse mentioned). 27 bytes vs. 17 bytes for numeric - or 30 vs. 20 bytes in RAM. See:

However, you would not want to apply collation rules to bank account numbers. That happens with collatable types like text or varchar if your DB cluster runs with a non-C locale. Would be a void effort for only digits to begin with. But you still get slower sorting and slower indexes etc. Notably, the "abbreviated keys" feature in Postgres 9.5 or later is currently (incl. Postgres 10) disabled for non-C locales.

Putting everything together, I suggest:

CREATE TABLE bank_account (
   bank_account_id serial  PRIMARY KEY
-- bank_account_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY  -- in Postgres 10+
 , number_account  text COLLATE "C" NOT NULL  -- disable collation rules
 , CONSTRAINT number_account_has_26_digits CHECK (number_account ~ '^\d{26}$')
);

Asides:

Upvotes: 7

klin
klin

Reputation: 121634

The maximum integer value is 2147483647, maximum bigint is 9223372036854775807. You cannot use integer types for the column.

It seems that the simplest way is to define the column as text with a check constraint:

CREATE TABLE bank_accounts (
    id serial primary key,
    number_account text not null check (number_account ~ '^\d{26}$')
);

The regular expression used in the check constraint means a string with exactly 26 digits.

Upvotes: 1

Related Questions