如何使主键在PostgreSQL中具有X位数? [英] How to make the Primary Key have X digits in PostgreSQL?
问题描述
我尝试使用 INT(8)
,但没有起作用。另外 AUTO_INCREMENT
在PostgreSQL中不起作用,但是我看到有几种数据类型会自动递增,但是我仍然有这个键不够长的问题。 p>
基本上我想要有数字代表用户ID,从 10000000
开始,向上移动。 00000001
并且起作用,对我来说并不重要。
我看到一个接近的答案不幸的是,它不适用于PostgreSQL。
希望我的问题有道理,如果不是,我会尝试澄清。
我的代码(我正在使用一个网站来尝试自己的实践项目论坛)是:
CREATE表用户(
user_id INT(8)NOT NULL AUTO_INCREMENT,
user_name VARCHAR(30)NOT NULL,
user_pass VARCHAR(255)NOT NULL,
user_email VARCHAR(255)NOT NULL,
user_date DATETIME NOT NULL,
user_level INT(8)NOT NULL,
UNIQUE INDEX user_name_unique(user_name),
PRIMARY KEY(user_id)
)TYPE = INNODB;
它在PostgreSQL(9.4 Windows x64版本)中不起作用。
您正在混合两个方面:
- 数据类型 允许某些
- 格式 您选择显示
AUTO_INCREMENT
是MySQL的非标准概念,SQL Server使用 IDENTITY(1,1)
等。 >
使用 serial $在Postgres中的c $ c>
列:
CREATE TABLE users(
user_id serial PRIMARY KEY
,...
)
这是一个伪类型实现作为整数
数据类型,附带列的默认绘图来自附加的 SEQUENCE
。 整数
对于您的案件来说足够大(-2147483648至+2147483647)。
如果您真的需要强制执行数字最多为8位十进制数,添加一个 CHECK
约束:
CONSTRAINT id_max_8_digits CHECK user_id BETWEEN 0 AND< 99999999)
以任何方式显示您想要的数字 - 0填充至8位数字,请使用 to_char()
:
SELECT to_char(user_id,'00000000')AS user_id_8digit
FROM users;
这非常快。请注意,输出现在是 text
,而不是 integer
。
SQL Fiddle。
另外还有一些其他的东西在你的代码中是MySQL特定的:
I am fairly new to SQL but have been working hard to learn. I am currently stuck on an issue with setting a primary key to have 8 digits no matter what.
I tried using INT(8)
but that didn't work. Also AUTO_INCREMENT
doesn't work in PostgreSQL but I saw there were a couple of data types that auto increment but I still have the issue of the keys not being long enough.
Basically I want to have numbers represent User IDs, starting at 10000000
and moving up. 00000001
and up would work too, it doesn't matter to me.
I saw an answer that was close to this, but it didn't apply to PostgreSQL unfortunately.
Hopefully my question makes sense, if not I'll try to clarify.
My code (which I am using from a website to try and make my own forum for a practice project) is:
CREATE Table users (
user_id INT(8) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(30) NOT NULL,
user_pass VARCHAR(255) NOT NULL,
user_email VARCHAR(255) NOT NULL,
user_date DATETIME NOT NULL,
user_level INT(8) NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
) TYPE=INNODB;
It doesn't work in PostgreSQL (9.4 Windows x64 version). What do I do?
You are mixing two aspects:
- the data type allowing certain values for your PK column
- the format you chose for display
AUTO_INCREMENT
is a non-standard concept of MySQL, SQL Server uses IDENTITY(1,1)
, etc.
Use a serial
column in Postgres:
CREATE TABLE users (
user_id serial PRIMARY KEY
, ...
)
That's a pseudo-type implemented as integer
data type with a column default drawing from an attached SEQUENCE
. integer
is easily big enough for your case (-2147483648 to +2147483647).
If you really need to enforce numbers with a maximum of 8 decimal digits, add a CHECK
constraint:
CONSTRAINT id_max_8_digits CHECK (user_id BETWEEN 0 AND < 99999999)
To display the number in any fashion you desire - 0-padded to 8 digits, for your case, use to_char()
:
SELECT to_char(user_id, '00000000') AS user_id_8digit
FROM users;
That's very fast. Note that the output is text
now, not integer
.
SQL Fiddle.
A couple of other things are MySQL-specific in your code:
这篇关于如何使主键在PostgreSQL中具有X位数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!