如何在PostgreSQL 11.1中将现有列更改为Identity [英] How can i change existing column as Identity in PostgreSQL 11.1

问题描述

我想将现有的列更改为Postgres数据库中的自动身份。

我尝试使用以下脚本，但无法正常工作。

让我知道您是否有相同的解决方案

我不想使用postgres SEQUENCE。我想使用始终作为身份标识。

I would like to changes my existing column as Auto Identity in a Postgres Database.
I tried with below script but it won't worked.
Let me know if you have solution for the same
I don't want to use postgres SEQUENCE. I would like to use GENERATED ALWAYS AS IDENTITY.

ALTER TABLE public.patient ALTER COLUMN patientid Type int4 
USING patientid::int4 GENERATED ALWAYS AS IDENTITY;

推荐答案

跟随文档

ALTER TABLE patient 
    ALTER patientid SET NOT NULL,  -- optional
    ALTER patientid ADD GENERATED ALWAYS AS IDENTITY 
        (START WITH 2);  -- optional

添加 NOT NULL 约束，如果该列没有有约束力了。可选子句 START WITH start 更改记录的序列起始值。

Add NOT NULL constraint if the column does not have the constraint yet. The optional clause START WITH start changes the recorded start value of the sequence.

在 DB<>小提琴。

这篇关于如何在PostgreSQL 11.1中将现有列更改为Identity的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！