您可以在Postgres中已经存在的列上创建序列吗 [英] Can you create a sequence on a column that already exists in Postgres

问题描述

我有一个表 linelevelpmts 和一列 seq （Int4），该列将用作序列。

I have a table linelevelpmts with a column seq (Int4) which is to be used as a sequence.

我知道我可以删除该列并将其重新创建为serial类型，但是可以修改现有列以用作序列。

I know I can delete the column and recreate it as type serial, but can I modify the existing column to be used as a sequence.

ALTER TABLE "public"."linelevelpmts" ALTER COLUMN "seq" SET DEFAULT nextval('linelevelpmts_seq_seq'::regclass);

此代码生成错误：关系linelevelpmts_seq_seq不存在。

This code generates an error: Relation linelevelpmts_seq_seq does not exist.

推荐答案

此代码生成错误：关系linelevelpmts_seq_seq不存在。

首先需要创建要用于默认值的序列：

Well you need to first create the sequence you want to use for the default value:

create sequence linelevelpmts_seq_seq;
ALTER TABLE public.linelevelpmts 
    ALTER COLUMN seq SET DEFAULT nextval('linelevelpmts_seq_seq'::regclass);

如果您希望获得与创建为 serial的效果相同的效果，您还需要更改序列的所有者：

If you want the same effect as if it was created as serial you also need to change the "owner" of the sequence:

alter sequence linelevelpmts_seq_seq owned by linelevelpmts.seq;

编辑

Igor的评论很不错：如果您在 seq 列中已经有值，则应调整序列的起始值：

Igor's comment is a good one: if you already have values in the column seq you should adjust the starting value of the sequence:

select setval('linelevelpmts_seq_seq', (select max(seq) from linelevelpmts));

这篇关于您可以在Postgres中已经存在的列上创建序列吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！