我在 PostgreSQL 中有一个包含许多列的表,我想添加一个自动增量主键。
我尝试创建类型为 BIGSERIAL
的名为 id
的列,但 pgadmin 响应错误:
错误:序列必须与其链接的表具有相同的所有者。
有谁知道如何解决这个问题?如何在不重新创建表的情况下在 PostgreSQL 中添加或创建自增主键?
试试这个命令:
ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;
尝试使用与您创建表的用户相同的 DB 用户。
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 内部使用哈希表结构来提高插入、删除、更新和选择的速度。如果主键列(强制唯一且非空)可用,则可以依赖它为散列函数提供唯一种子。如果没有可用的主键列,则散列函数会变得低效,因为它会选择其他一些列集作为键。
如果您想更好地控制序列键的行为,请参阅 postgresql 序列。
在 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!'
);
第四步,观察行
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 表上为该键列定义一个默认值。哪个更漂亮。
如果您想在 pgadmin 中执行此操作,则要容易得多。在 postgressql 中,要为列添加自动增量,我们首先需要创建一个自动增量序列并将其添加到所需的列中。我确实喜欢这个。
1)首先你需要确保你的表有一个主键。还要将主键的数据类型保留在 bigint 或 smallint 中。 (我使用了 bigint,找不到其他地方的其他答案中提到的名为 serial 的数据类型)
https://i.stack.imgur.com/JH8tQ.png
3)最后,将行 nextval('your_sequence_name'::regclass)
添加到主键中的默认值,如下所示。
https://i.stack.imgur.com/tTZjd.png
如果要在序列中使用数字,请定义一个新序列,例如
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);
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);
默认和始终的区别:
GENERATED ALWAYS 指示 PostgreSQL 总是为标识列生成一个值。如果您尝试将值插入(或更新)到 GENERATED ALWAYS AS IDENTITY 列,PostgreSQL 将发出错误。
GENERATED BY DEFAULT 还指示 PostgreSQL 为标识列生成一个值。但是,如果您为插入或更新提供值,PostgreSQL 将使用该值插入标识列,而不是使用系统生成的值。
在 PgAdmin 上执行此操作的步骤:
创建序列 sequnence_title START 1; // 如果表存在最后一个id
将此序列添加到主键,表-属性-列-column_id(主键)编辑-约束-将nextval('sequnence_title'::regclass)添加到字段默认值。
我已经尝试了以下脚本来成功地自动增加 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 关键字自动为各个列创建一个序列。
ALTER SEQUENCE dummytable_id_seq RESTART WITH 1;
及其工作。
也许我回答这个问题有点晚了,但我在工作中正在研究这个主题:)
我想写列 'a_code' = c1,c2,c3,c4...
首先,我打开了一个名称为 ref_id
和类型为 serial
的列。然后我用这个命令解决了我的问题:
update myschema.mytable set a_code=cast('c'||"ref_id" as text)
ALTER TABLE mytable ADD PRIMARY KEY (column);
。 Postgresql 将检查该列是否不包含 NULL。bigserial
和serial
都给出相同的错误:ERROR: syntax error at or near "BIGSERIAL"