如何在 PostgreSQL 9.5 中为“int”数据类型设置大小限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34883306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I set a size limit for an "int" datatype in PostgreSQL 9.5
提问by FlashspeedIfe
I am experimenting with PostgreSQL coming from SQL using MySQL and I simply wish to create a table with this piece of code which is valid SQL:
我正在试验来自使用 MySQL 的 SQL 的 PostgreSQL，我只想用这段代码创建一个表，这是有效的 SQL：
CREATE TABLE flat_10
(
pk_flat_id INT(30) DEFAULT 1,
rooms INT(10) UNSIGNED NOT NULL,
room_label CHAR(1) NOT NULL,
PRIMARY KEY (flat_id)
);
I get the error
我收到错误
ERROR: syntax error at or near "("
LINE 3: pk_flat_id integer(30) DEFAULT 1,
I have conducted searches on the web and found no answer and I cant seem to find an answer in the PostgreSQL manual. What am I doing wrong?
我在网上进行了搜索，但没有找到答案，而且我似乎无法在 PostgreSQL 手册中找到答案。我究竟做错了什么？
I explicitly want to set a limit to the number of digits that can be inserted into the "pk_flat_id" field
我明确想对可以插入“pk_flat_id”字段的位数设置限制
回答by a_horse_with_no_name
I explicitly want to set a limit to the number of digits that can be inserted into the "pk_flat_id" field
我明确想对可以插入“pk_flat_id”字段的位数设置限制
Your current table definition does notimpose a "size limit" in any way. In MySQL the parameter for the intdata type is only a hintfor applications on the display width of the column when displayingit.
您当前的表定义并没有以任何方式征收“大小限”。在MySQL的参数int的数据类型仅是暗示对在列的显示宽度的应用中显示它。
You can store the value 2147483647 in an int(1)without any problems.
您可以int(1)毫无问题地将值 2147483647 存储在一个中。
If you want to limit the values to be stored in an integer column you can use a check constraint:
如果要限制要存储在整数列中的值，可以使用检查约束：
CREATE TABLE flat_10
(
pk_flat_id bigint DEFAULT 1,
rooms integer NOT NULL,
room_label CHAR(1) NOT NULL,
PRIMARY KEY (flat_id),
constraint valid_number
check (pk_flat_id <= 999999999)
);
回答by Gordon Linoff
The answer is that you use numericor decimaltypes. These are documented here.
答案是您使用numeric或decimal键入。这些都记录在此处。
Note that these types can take an optional precision argument, but you don't want that. So:
请注意，这些类型可以采用可选的精度参数，但您不希望这样。所以：
CREATE TABLE flat_10
(
pk_flat_id DECIMAL(30) DEFAULT 1,
rooms DECIMAL(10) NOT NULL,
room_label CHAR(1) NOT NULL,
PRIMARY KEY (pk_flat_id)
);
Hereis a SQL Fiddle.
这是一个 SQL 小提琴。
I don't think that Postgres supports unsigned decimals. And, it seems like you really want serial types for your keys and the long number of digits is superfluous.
我认为 Postgres 不支持无符号小数。而且，您似乎真的想要密钥的序列类型，而长位数是多余的。
