sql - 如何指定银行帐号的最小和最大数字？

Question

是否可以限制 PostgreSQL 中整数数据类型列中允许的位数。我有以下示例:

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

我们可以输入如下内容:

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

等等。 ...最多 26 位数字。

但我想限制我的列以恰好 26 位数字存储，不能少也不能多。如何实现？

Answer 1

银行帐号本质上不是整数。无论如何，26 位十进制数字对于 integer or bigint 来说太多了。

银行帐号根本不是数值，真的，即使我们可以使用类型 numeric 进行存储。它可以轻松处理 26 位十进制数字。但它也允许小数位(和其他装饰器， like @klin commented )。您可以限制为 numeric(26)，这是 numeric(26,0) 的缩写，以从存储中删除小数位。但这仍然允许输入小数位，然后四舍五入。和其他装饰器。所有这些对于银行帐号来说似乎都是不可取的:

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

银行帐号本质上更像文本，因此数据类型 text 似乎更合适 ( like @klin provided )，即使它在磁盘上占用更多空间 ( like @a_horse mentioned )。 27 字节对 numeric 17 字节 - 或者 30 对 RAM 中的 20 字节。见:

• What is the overhead for varchar(n)?

但是，您不希望将 collation rules 应用于银行帐号。如果您的数据库集群在非 C 语言环境下运行，则这种情况会发生在 text 或 varchar 等可整理类型上。仅以数字开头将是徒劳的。但是你仍然会得到更慢的排序和更慢的索引等。值得注意的是，Postgres 9.5 或更高版本中的 "abbreviated keys" feature 当前(包括 Postgres 10) disabled for non-C locales 。

将所有内容放在一起，我建议:

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}$')
);

旁白:

• 考虑 IDENTITY 列而不是 Postgres 10+ 中的 serial。详情:

  • https://blog.2ndquadrant.com/postgresql-10-identity-columns/

• INTEGER(26) 在 Postgres 中不是有效语法，其中 integer data type 没有修饰符。您可以从 int2、int4(默认 integer)和 int8 中进行选择，不过 - 悬挂数字表示占用的字节数，而不是允许的位数。