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

How to set auto increment primary key in PostgreSQL?

提问by mkn

I have a table in PostgreSQL with 22 columns, and I want to add an auto increment primary key.

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

I tried to create a column called idof type BIGSERIAL but pgadmin responded with an error:

我试图创建一个名为idBIGSERIAL 类型的列，但 pgadmin 响应了一个错误：

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

Does anyone know how to fix this issue? How do I add a create an auto-incrementing primary key in PostgreSQL without recreating the table again?

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

回答by A.H.

Try this command:

试试这个命令：

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

Try it with the same DB-user as the one you have createdthe table.

使用与您创建表相同的 DB 用户来尝试。

回答by Eric Leschinski

Auto incrementing primary key in postgresql:

postgresql 中的自动递增主键：

Step 1, create your table:

第 1 步，创建您的表：

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

Step 2, insert values into your table like this, notice that mytable_key is not specified in the first parameter list, this causes the default sequence to autoincrement.

第 2 步，像这样向表中插入值，注意第一个参数列表中没有指定 mytable_key，这会导致默认序列自动递增。

insert into epictable(moobars,foobars) values('delicious moobars','2012-05-01')
insert into epictable(moobars,foobars) values('worldwide interblag','2012-05-02')

Step 3, select * from your table:

第 3 步，从您的表中选择 *：

el@voyager$ psql -U pgadmin -d kurz_prod -c "select * from epictable"

Step 4, interpret the output:

第 4 步，解释输出：

mytable_key  |        moobars        |  foobars   
-------------+-----------------------+------------
           1 | delicious moobars     | 2012-05-01
           2 | world wide interblags | 2012-05-02
(2 rows)

Observe that mytable_key column has been auto incremented.

观察 mytable_key 列已自动增加。

ProTip:

专家提示：

You should always be using a primary key on your table because postgresql internally uses hash table structures to increase the speed of inserts, deletes, updates and selects. If a primary key column (which is forced unique and non-null) is available, it can be depended on to provide a unique seed for the hash function. If no primary key column is available, the hash function becomes inefficient as it selects some other set of columns as a key.

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

回答by Eric Leschinski

Create an auto incrementing primary key in postgresql, using a custom sequence:

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

Step 1, create your sequence:

第 1 步，创建您的序列：

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

Step 2, create your table

第 2 步，创建您的表

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

Step 3, insert into your table

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

Step 4, observe the rows

第四步，观察行

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)

The two rows have keys that start at 1 and are incremented by 1, as defined by the sequence.

两行的键从 1 开始并按序列定义递增 1。

Bonus Elite ProTip:

奖金精英专家提示：

Programmers hate typing, and typing out the nextval('splog_adfarm_seq')is annoying. You can type DEFAULTfor that parameter instead, like this:

程序员讨厌打字，打字nextval('splog_adfarm_seq')很烦人。您可以输入DEFAULT该参数，如下所示：

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

For the above to work, you have to define a default value for that key column on splog_adfarm table. Which is prettier.

要使上述工作正常运行，您必须为 splog_adfarm 表上的该键列定义默认值。哪个更漂亮。

回答by toing_toing

If you want to do this in pgadmin, it is much easier. It seems in postgressql, to add a auto increment to a column, we first need to create a auto increment sequence and add it to the required column. I did like this.

如果您想在 pgadmin 中执行此操作，则要容易得多。好像在postgressql中，要给一列添加自增，我们首先需要创建一个自增序列，并将其添加到需要的列中。我就是喜欢这个。

1) Firstly you need to make sure there is a primary key for your table. Also keep the data type of the primary key in bigint or smallint. (I used bigint, could not find a datatype called serial as mentioned in other answers elsewhere)

1）首先，您需要确保您的表有一个主键。还要保持主键的数据类型为 bigint 或 smallint。（我使用了 bigint，找不到其他地方的其他答案中提到的名为 serial 的数据类型）

2)Then add a sequence by right clicking on sequence-> add new sequence. If there is no data in the table, leave the sequence as it is, don't make any changes. Just save it. If there is existing data, add the last or highest value in the primary key column to the Current value in Definitions tab as shown below.

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

3)Finally, add the line nextval('your_sequence_name'::regclass)to the Default value in your primary key as shown below.

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

Make sure the sequence name is correct here. This is all and auto increment should work.

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

回答by acaruci

If you want to use numbers in a sequence, define a new sequence with something like

如果要在序列中使用数字，请使用类似的内容定义一个新序列

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

and then alter the table to use the sequence for the id:

然后更改表以使用 id 的序列：

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

回答by Asad Shakeel

I have tried the following script to successfully auto-increment the primary key in PostgreSQL.

我尝试了以下脚本来成功地自动增加 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)
);

EDIT:

编辑：

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

SERIAL keyword automatically create a sequence for respective column.

SERIAL 关键字自动为各个列创建一个序列。

回答by user5838061

Maybe I'm a bit of late to answer this question, but I'm working on this subject at my job :)

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

I wanted to write column 'a_code' = c1,c2,c3,c4...

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

Firstly I opened a column with the name ref_idand the type serial. Then I solved my problem with this command:

首先，我打开了一个带有 nameref_id和 type的列serial。然后我用这个命令解决了我的问题：

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