PostgreSQL Autoincrement

2018-06-20 06:02:11

I'm switching from MySQL to PostgreSQL and was wondering how I can do autoincrement values. I saw in the PostgreSQL docs a datatype "serial", but I get syntax errors when using it (in v8.0).

Yes, SERIAL is the equivalent function.

CREATE TABLE foo (
id SERIAL,
bar varchar);

INSERT INTO foo (bar) values ('blah');
INSERT INTO foo (bar) values ('blah');

SELECT * FROM foo;

1,blah
2,blah

SERIAL is just a create table time macro around sequences. You can not alter SERIAL onto an existing column.

You can use any other integer data type, such as smallint .

Example :

CREATE SEQUENCE user_id_seq;
CREATE TABLE user (
    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
);
ALTER SEQUENCE user_id_seq OWNED BY user.user_id;

Better to use your own data type, rather than user serial data type.

如果你想在已经存在的表格中添加序列到id，你可以使用：

CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');

链接地址: http://www.djcxy.com/p/56960.html

上一篇: 在PostgreSQL中创建一个数据库的副本

下一篇: PostgreSQL自动增量