How to make the Primary Key have X digits in Postg

问题:

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?

回答1:

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 [phone]).

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:

• int(8): use int.
• datetime: use timestamp.
• TYPE=INNODB: just drop that.

回答2:

You could make user_id a serial type column and set the seed of this sequence to 10000000.

回答3:

Why?

int(8) in mysql doesn't actually only store 8 digits, it only displays 8 digits

Postgres supports check constraints. You could use something like this:

create table foo (
  bar_id int primary key check ( 9999999 < bar_id and bar_id < 100000000 )
);

If this is for numbering important documents like invoices that shouldn't have gaps, then you shouldn't be using sequences / auto_increment