如何在PostgreSQL中设置自增主键？

Question

我在PostgreSQL中有一个表格，拥有很多列，我想要添加自增主键。

我尝试创建一个名为id的列，类型为BIGSERIAL，但是pgadmin报了一个错误：

ERROR: sequence must have same owner as table it is linked to.

有人知道如何解决这个问题吗？在不重新创建表的情况下，如何在PostgreSQL中添加或创建自增主键？

- mkn

---

对于Postgres 10或更高版本，考虑使用IDENTITY列而不是serial：https://dev59.com/wGkw5IYBdhLWcg3wdKXD#9875517。 - Erwin Brandstetter

Answer 1

尝试使用此命令：

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

以与您创建表的数据库用户相同的用户进行尝试。

- A.H.

---

关键在于使用SERIAL或BIGSERIAL数据类型，它会在背后创建一个序列，并在插入时递增/使用它。 - rogerdpack

如果你想从另一个表引用它，请使用整数或大整数。 - Ward

@satishkilari：是的，语法是ALTER TABLE mytable ADD PRIMARY KEY (column);。Postgresql将检查该列不包含任何NULL值。 - A.H.

在pgAdmin 4中遇到了这个错误。bigserial和serial都会产生相同的错误： ERROR: syntax error at or near "BIGSERIAL"。 - Adil

使用bigserial或serial时也会出现语法错误。这是因为需要特定的PostgreSQL版本吗？ - dacDave

Answer 2

在PostgreSQL中自动递增的主键：

创建您的表：

CREATE TABLE epictable
(
    mytable_key    serial primary key,
    moobars        VARCHAR(40) not null,
    foobars        DATE
);

插入值到你的表中：

insert into epictable(moobars,foobars) values('delicious moobar','2012-05-01');
insert into epictable(moobars,foobars) values('WorldWideBlag','2012-05-02');

选择 * 从你的表中：

select * from epictable

mytable_key  |        moobars        |  foobars   
-------------+-----------------------+------------
           1 | delicious moobar      | 2012-05-01
           2 | WorldWideBlag         | 2012-05-02
(2 rows)

观察到mytable_key列已经自动递增。

Postgresql的COPY命令不会观察到序列。

自动递增的序列键在从文件进行批量数据导入时，不会像COPY命令那样自动递增。你会得到一个"psql:ERROR: duplicate key value violates unique constraint"的错误。修复这个问题的一种方法是将其复制到一个新表中，然后使用插入操作。

COPY epictable TO '/tmp/etbackup.csv' DELIMITER ',' CSV HEADER;

CREATE TABLE epictable2 ( mytable_key    integer, 
                          moobars        VARCHAR(40) not null, 
                          foobars        DATE ); 

COPY epictable2(mytable_key, moobars, foobars) FROM 
    '/tmp/etbackup.csv' DELIMITER ',' CSV HEADER;

--and again to make the duplicates requiring an autoincrement:
COPY epictable2(mytable_key, moobars, foobars) FROM 
    '/tmp/etbackup.csv' DELIMITER ',' CSV HEADER;

insert into epictable(moobars, foobars) (
    select moobars, foobars from epictable2
);

select * from epictable;

┌─────────────┬──────────────────┬────────────┐
│ mytable_key │     moobars      │  foobars   │
├─────────────┼──────────────────┼────────────┤
│           1 │ delicious moobar │ 2012-05-01 │
│           2 │ WorldWideBlag    │ 2012-05-02 │
│           3 │ delicious moobar │ 2012-05-01 │
│           4 │ WorldWideBlag    │ 2012-05-02 │
│           5 │ delicious moobar │ 2012-05-01 │
│           6 │ WorldWideBlag    │ 2012-05-02 │
└─────────────┴──────────────────┴────────────┘

观察自动递增发生在mytable_key上，使用COPY。 专业提示： 您应该始终在表上使用主键，因为PostgreSQL在内部使用哈希表结构来提高插入、删除、更新和查询的速度。如果有一个主键列（强制唯一且非空），它可以被依赖于为哈希函数提供一个唯一的种子。如果没有主键列可用，哈希函数会变得低效，因为它会选择其他一组列作为键。

如果您想对序列键的行为有更多控制，请参阅PostgreSQL序列。

- Eric Leschinski

---

小小的挑剔，SERIAL在幕后确实会创建一个sequence（序列）: http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-SERIAL - carbocation

在不添加任何新列的情况下，是否可以将表中的现有列设置为主键？ - satish kilari

@satishkilari 是的，您可以就地添加、修改和删除主键。https://docs.actian.com/psql/psqlv13/index.html#page/sqlref/sqlref.PRIMARY_KEY.htm 但更简单的做法是：1. 创建一个正确布局的新表，2. 将数据复制到新表中，3. 最后删除旧表并将新表重命名为旧表名。 - Eric Leschinski

@EricLeschinski 我尝试使用COPY像这样添加数据，但显然这会破坏表的自增部分。有什么建议可以重新开始自动递增或修复ID列？ - gwhiz

@gwhiz将您的数据从文件复制到一个新表中，不包括序列，然后使用带有选择子查询的插入操作。自动递增将被执行。上面修改后的答案中有演示。 - undefined

Answer 3

在PostgreSQL中创建一个使用自定义序列的自增主键：

第一步，创建您的序列：

create sequence splog_adfarm_seq
    start 1
    increment 1
    NO MAXVALUE
    CACHE 1;
ALTER TABLE fact_stock_data_detail_seq
OWNER TO pgadmin;

第2步，创建您的表格

CREATE TABLE splog_adfarm
(
    splog_key    INT unique not null,
    splog_value  VARCHAR(100) not null
);

第三步，将其插入到您的表中

insert into splog_adfarm values (
    nextval('splog_adfarm_seq'), 
    'Is your family tree a directed acyclic graph?'
);

insert into splog_adfarm values (
    nextval('splog_adfarm_seq'), 
    'Will the smart cookies catch the crumb?  Find out now!'
);

第四步，观察行

el@defiant ~ $ psql -U pgadmin -d kurz_prod -c "select * from splog_adfarm"

splog_key |                            splog_value                             
----------+--------------------------------------------------------------------
        1 | Is your family tree a directed acyclic graph?
        2 | Will the smart cookies catch the crumb?  Find out now!
(3 rows)

这两行的键从1开始，每次增加1，如序列所定义。

额外的顶尖专业提示：

程序员不喜欢打字，打出 nextval('splog_adfarm_seq') 是很烦人的。您可以将该参数替换为 DEFAULT，就像这样：

insert into splog_adfarm values (
    DEFAULT, 
    'Sufficient intelligence to outwit a thimble.'
);

为了使上述内容正常工作，您需要在 splog_adfarm 表中为那个关键列定义一个默认值。这样更好看。

- Eric Leschinski

---

定制序列的好处是什么？可能是安全性吗？ - Léo Léopold Hertz 준영

@Masi 自定义序列的一个用途是使主-主复制更容易 - 如果两个数据中心之间的数据链路断开，这将非常有用 - 允许在两个服务器上创建具有不同ID的记录，然后轻松地将数据库同步回来，同时保持在不同位置生成的ID。 - Vincent McNabb

Answer 4

serial是旧方式自动生成唯一值，不符合SQL标准。

PostgreSQL 10之后，你可以使用generated as identity，它符合SQL标准：

CREATE TABLE t1 (id integer primary key generated always as identity);

或者

CREATE TABLE t1 (id integer primary key generated by default as identity); 

by default和always之间的区别:

• GENERATED ALWAYS指示PostgreSQL始终为标识列生成值。如果您尝试向GENERATED ALWAYS AS IDENTITY列插入（或更新）值，则PostgreSQL将发出错误。
• GENERATED BY DEFAULT也指示PostgreSQL为标识列生成值。但是，如果您提供了插入或更新的值，PostgreSQL将使用该值插入到标识列中，而不是使用系统生成的值。

更多信息。

- Ismail

Answer 5

如果您想在pgadmin中完成此操作，那么将会更加容易。在PostgreSQL中，要给列添加自增属性，我们首先需要创建一个自增序列并将其添加到所需的列中。我是这样做的。

1）首先，您需要确保表中有一个主键。同时，将主键的数据类型保持为bigint或smallint。（我使用了bigint，无法找到其他答案中提到的称为serial的数据类型）

2）然后，通过右键单击序列->添加新序列来添加一个序列。 如果表中没有数据，请将序列保留原样，不要进行任何更改。只需保存即可。 如果存在现有数据，请将主键列中的最后或最高值添加到“定义”选项卡中的“当前值”中，如下所示：

3）最后，在您的主键的“默认值”中添加nextval('your_sequence_name'::regclass)代码，如下所示。

- toing_toing

Answer 6

如果您想在序列中使用数字，可以定义一个新的序列，例如：

CREATE SEQUENCE public.your_sequence
    INCREMENT 1
    START 1
    MINVALUE 1
;

然后修改表格使用序列作为ID:

ALTER TABLE ONLY table ALTER COLUMN id SET DEFAULT nextval('your_sequence'::regclass);

- acaruci

---

我需要为每个表格创建一个新的序列吗？ - Bharat Chhabra

您可以为不同的表共享相同的序列，但是每个表中的每条记录的序列都会增加。 - acaruci

Answer 7

我已经尝试了以下脚本，在PostgreSQL中成功地自动增加了主键。

CREATE SEQUENCE dummy_id_seq
    START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

CREATE table dummyTable (
    id bigint DEFAULT nextval('dummy_id_seq'::regclass) NOT NULL,
    name character varying(50)
);

编辑：

CREATE table dummyTable (
    id SERIAL NOT NULL,
    name character varying(50)
)

SERIAL关键字会自动为相应的列创建一个序列。

- Asad Shakeel

---

你可以像对待 SEQUENCE 一样重置 SERIAL 吗？ - thoroc

是的！我已经使用 ALTER SEQUENCE dummytable_id_seq RESTART WITH 1; 进行了检查，它可以正常工作。 - Asad Shakeel

Answer 8

在PgAdmin中执行的步骤：

• CREATE SEQUENCE sequnence_title START 1; // 如果表中已经存在id，则从该id开始
• 将此序列添加到主键中，表 - 属性 - 列 - column_id(主键) 编辑 - 约束 - 将nextval('sequnence_title'::regclass)添加到默认字段。

- Baskovli

Answer 9

也许我回答这个问题有点晚了，但我在我的工作中正在处理这个主题 :)

我想写一个名为'a_code'的列 = c1，c2，c3，c4...

首先，我打开了一个名为“ref_id”的列，类型为“serial”。 然后，我用这个命令解决了我的问题：

update myschema.mytable set a_code=cast('c'||"ref_id" as text) 

- user5838061

---

在不添加任何新列的情况下，是否可以将表中的现有列设置为主键？ - satish kilari

Answer 10

您可以使用以下代码进行自增。

Create table public.EmployeeDapper
(
    Id int not null generated by default as identity(increment by 1 start 1 
    minvalue 1 maxvalue 2147483647 cache 1),

    Name varchar(50) not null,
    Age int not null,
    Position varchar(50)not null
)

- Praveen Prajapati