PostgreSQL中如何设置自增主键？

Question

我在 PostgreSQL 中有一个包含很多列的表，我想添加一个自动增量主键。

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

错误：序列必须与其链接到的表具有相同的所有者。

有谁知道如何解决这个问题？如何在 PostgreSQL 中添加或创建自动递增主键而不重新创建表？

Answer 1

尝试此命令：

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

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

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:错误：重复的键值违反了唯一约束。解决此问题的一种方法是将其复制到新表中，然后使用插入：

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 │
└─────────────┴──────────────────┴────────────┘

使用 COPY 观察 mytable_key 上发生自动递增。

专业提示：

您应该始终在表上使用主键，因为 postgresql 内部使用哈希表结构来提高插入、删除、更新和选择的速度。如果主键列（强制唯一且非空）可用，则可以依赖它为哈希函数提供唯一的种子。如果没有可用的主键列，则哈希函数会变得低效，因为它会选择其他一些列集作为键。

如果您想更多地控制序列键的行为，请参阅 postgresql 序列。

Answer 3

serial 是自动生成唯一值的旧方法，它不是 SQL 标准的一部分。

在PostgreSQL 10之后，您可以使用生成的身份，它符合SQL标准：

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

或者

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

默认和始终之间的区别：

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

了解更多信息。

Answer 4

使用自定义序列在 postgresql 中创建自动递增主键：

第 1 步，创建序列：

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
);

第 3 步，插入表

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!'
);

第 4 步，观察行

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 表上为该键列定义默认值。哪个更漂亮。

Answer 5

如果你想在 pgadmin 中执行此操作，那就容易得多。在postgressql中，要为列添加自动增量，我们首先需要创建一个自动增量序列并将其添加到所需的列中。我确实喜欢这个。

1）首先您需要确保您的表有主键。还要保留主键的数据类型为bigint 或smallint。 （我使用bigint，找不到称为serial的数据类型，如其他答案中提到的）

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

3) 最后，将 nextval('your_sequence_name'::regclass) 行添加到主键中的默认值，如下所示。

确保此处的序列名称正确。这就是全部，自动增量应该可以工作。

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);

Answer 7

我尝试了以下脚本来成功自动增加 P​​ostgreSQL 中的主键。

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 关键字会自动为相应列创建序列。

Answer 8

在 PgAdmin 上执行此操作的步骤：

• CREATE SEQUENCE sequnence_title START 1; // 如果表存在最后一个 id
• 将此序列添加到主键，表 - 属性 - 列 - column_id(主键) 编辑 - 约束 - 添加 nextval('sequnence_title'::regclass) 到
  字段默认值。

Answer 9

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

我想写专栏 'a_code' = c1,c2,c3,c4...

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

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

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
)