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

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或bigint来说太多了。

银行帐号是而不是数值，实际上，即使我们可以使用类型numeric进行存储。它可以轻松处理26位小数。但它也允许使用小数(以及其他装饰符，如@klin评论的)。您可以限制为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似乎更合适(如所提供的@klin)，即使它在磁盘上占用了更多的空间(如所提到的@A_mase)。CCD_ 7的27字节对17字节，或者RAM的30字节对20字节。参见：

• varchar(n)的开销是多少

但是，您不希望将排序规则应用于银行帐号。如果您的DB集群使用非C语言环境运行，那么text或varchar等可整理类型就会发生这种情况。如果一开始只有数字，那将是徒劳的。但你仍然会得到较慢的排序和较慢的索引等。值得注意的是，Postgres9.5或更高版本中的"缩写键"功能目前(包括Postgres10)在非C语言环境中被禁用。

把所有东西放在一起，我建议：

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列，而不是Postgres10+中的serial。详细信息：

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

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

Answer 2

最大整数值为2147483647，最大bigint为9223372036854775807。不能对列使用整数类型。

似乎最简单的方法是将列定义为带有检查约束的文本：

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

在检查约束中使用的正则表达式表示CCD_ 18。