PostgreSQL 9.1主键自动递增 [英] PostgreSQL 9.1 primary key autoincrement

问题描述

我正在尝试添加具有自动增量的主键。

I'm trying to add a primary key with an autoincrement.

我已经阅读了一些文档和其他问题-有 SERIAL 和 nextval（）语句，但是它不起作用。

I have read some docs and other questions - there're SERIAL and nextval() statements but it doesn't work.

这就是我的意思制作：

CREATE TABLE IF NOT EXISTS "category" (
  "id" integer SERIAL PRIMARY KEY,
  "name" varchar(30) DEFAULT NULL
); // the error near "SERIAL"

和

CREATE SEQUENCE your_seq;
CREATE TABLE IF NOT EXISTS "category" (
  "id" integer PRIMARY KEY nextval('your_seq'),
  "name" varchar(30) DEFAULT NULL
); // the error near 'nextval'

我怎么了？我只想将主键增加1。

What do I wrong? I just want to increment the primary key by 1.

推荐答案

serial 是或多或少是一种列类型，所以说整数序列就像说文本文本，只是说序列：

serial is, more or less, a column type so saying integer serial is like saying text text, just say serial:

CREATE TABLE IF NOT EXISTS "category" (
  "id" SERIAL PRIMARY KEY,
  "name" varchar(30) DEFAULT NULL
);

如果您想自己创建序列，则希望将默认值设置为 id 序列中的下一个值，这意味着说 default nextval（'your_seq'）：

If you want to create the sequence yourself then you want to make the default value of id the next value in the sequence and that means saying default nextval('your_seq'):

CREATE SEQUENCE your_seq;
CREATE TABLE IF NOT EXISTS "category" (
  "id" integer PRIMARY KEY default nextval('your_seq'),
  "name" varchar(30) DEFAULT NULL
);

要模拟通常的 serial 行为， ll还希望使表拥有该序列：

To simulate the usual serial behavior you'll also want to make the sequence owned by the table:

alter sequence your_seq owned by category.id;

阅读串行类型部分可能会很有成果。

Reading the Serial Types section of the manual might be fruitful.

我还建议您不要对表名和列名加双引号，除非必须这样做。 PostgreSQL会将您的标识符折叠成小写，因此 id 和 id 是相同的，但是不必要的引号是不良习惯很容易导致到处乱七八糟的报价。

I'd also recommend that you don't double quote your table and column names unless you have to. PostgreSQL will fold your identifiers to lower case so id and "id" will be the same thing but unnecessary quoting is a bad habit that can easily lead to a big mess of quotes everywhere.

这篇关于PostgreSQL 9.1主键自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！