postgresql Postgres自动递增主键

3lxsmp7m 于 2023-01-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(219)

我试图弄清楚自动增量键在Postgres中是如何表示的，这取决于您是使用SERIAL类型还是使用IDENTITY创建它。
当我使用以下DDL创建一个带有SERIAL主键的表时：

CREATE TABLE public.test_change_column (
    id SERIAL PRIMARY KEY NOT NULL
)

我得到一个名为'test_change_column_id_seq'的序列，并将列id的缺省值设为nextval('test_change_column_id_seq'::regclass)
如果我使用以下DDL创建一个没有SERIAL和主键的表：

CREATE TABLE public.test_change_column (
    id INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
)

创建序列，但ID的默认值未设置为nextval（'test_change_column_id_seq'：：regclass），而是将is_identity列设置为“YES”。
如果创建列时不使用自动增量，则不能在以后使用SERIAL：

ALTER TABLE public.test_change_column  ALTER COLUMN id TYPE serial;

这导致错误“类型“串行”不存在”。
使用IDENTITY方法，可以使用以下DDL添加自动增量：

ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

这个SQL将展示postgres如何存储两种不同方法的元数据：

SELECT column_name, column_default, is_identity 
FROM information_schema.columns 
WHERE table_name = 'test_change_column';

删除自动增量时，根据您使用的是序列还是标识，自动增量的删除方式会有所不同。
如果自动增量是使用序列类型创建的，则必须更改列ID DROP DEFAULT。这不会删除关联的序列表。
如果自动增量是使用IDENTITY创建的，则必须ALTER COLUMN id DROP IDENTITY。这也会删除序列表。

postgresql

来源：https://stackoverflow.com/questions/74996570/postgres-auto-increment-primary-key

2条答案

按热度按时间

f45qwnt81#

将列更改为标识列（并添加PK约束）就足够了，而且是正确的做法（不鼓励使用serial伪类型）
例如，您可以看到psql将其正确地报告为标识列：

arthur=> CREATE TABLE public.test_change_column (id INTEGER NOT NULL);
CREATE TABLE
arthur=> \d public.test_change_column
         Table "public.test_change_column"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |

arthur=> ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE
arthur=> ALTER TABLE public.test_change_column ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
ALTER TABLE
arthur=> \d public.test_change_column
                     Table "public.test_change_column"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
Indexes:
    "test_change_column_pkey" PRIMARY KEY, btree (id)

您还可以通过插入一行来验证默认设置是否有效：

insert into public.test_change_column default values;

将创建新行并递增id值。
这样的列将显示在information_schema.columns中，其中is_identity = 'YES'不是默认值。
我建议使用generated always as identity，这样您就不会意外地通过序列绕过生成（结果是序列和表中的值不再“同步”）。

赞(0）回复(0）举报 2023-01-05

ubby3x7f2#

创建表格。

CREATE TABLE public.test_change_column (
    id INTEGER NOT NULL
);

在一行中更改表格：

ALTER TABLE 
    public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY, 
    ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);

\d test_change_column
                     Table "public.test_change_column"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
Indexes:
    "test_change_column_pkey" PRIMARY KEY, btree (id)

IDENTITY列有一个与之关联的序列，该序列的使用方式与serial列类似。只是未显示为DEFAULT。要查找基础序列：

select pg_get_serial_sequence ('public.test_change_column', 'id');
      pg_get_serial_sequence      
----------------------------------
 public.test_change_column_id_seq

select * from public.test_change_column_id_seq ;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f

您可以在ALTER TABLE中使用IDENTITY命令操作上述内容：
ALTER [列]列名ADD生成{总是|默认为标识[（sequence_options）]
ALTER [COLUMN]列名{生成的集合{总是|默认设置}|设置序列_选项|重新开始[[with] restart ]}[...]
ALTER [列]列名删除标识[如果存在]

- 更新**

从此处输入序列号：
数据类型smallserial、serial和bigserial不是真正的类型，而只是为了便于创建唯一标识符列（类似于某些其他数据库支持的AUTO_INCREMENT属性）。
创建表表名（列名序列）;
等效于指定：
CREATE SEQUENCE表列名序号为整数;
CREATE TABLE表名（列名整数NOT NULL默认下一值（'tablename_colname_seq'））;
ALTER SEQUENCE表名_列名_序号所有者表名.列名;
因此，我们创建了一个整数列，并安排从序列生成器分配其默认值。应用了一个NOT NULL约束以确保不能插入空值。（在大多数情况下，您还希望附加UNIQUE或PRIMARY KEY约束以防止意外插入重复值，但这不是自动的。）最后，该序列被标记为由该列"拥有"，使得如果该列或表被丢弃，则该序列也将被丢弃。
因此，如果你想ALTER一个表复制一个serial '类型'，你将需要执行上述各个步骤：

CREATE TABLE public.test_change_column (id INTEGER NOT NULL);

create sequence test_change_column_seq;

alter 
    table test_change_column alter COLUMN id 
    set default nextval('test_change_column_seq'), 
ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
alter sequence test_change_column_seq owned by test_change_column.id;

\d test_change_column
                           Table "public.test_change_column"
 Column |  Type   | Collation | Nullable |                   Default                   
--------+---------+-----------+----------+---------------------------------------------
 id     | integer |           | not null | nextval('test_change_column_seq'::regclass)
Indexes:
    "test_change_column_pkey" PRIMARY KEY, btree (id)

赞(0）回复(0）举报 2023-01-05

我来回答

postgresql Postgres自动递增主键

2条答案

相关问题

热门标签

最新问答