如何在PostgreSQL 11.1中向标识列插入值

Question

我希望自己可以插入一个值到identity列中。

表结构：

CREATE TABLE public.userdetail (
    userdetailid int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    username varchar(30) NOT NULL,
    "password" varchar(1000) NOT NULL,
    CONSTRAINT pk_userdetail PRIMARY KEY (userdetailid)
);

插入查询：

INSERT INTO UserDetail (UserDetailId,UserName, Password) 
  VALUES(1,'admin', 'password');

以下是插入查询引发错误的内容：

无法将数据插入到列"userdetailid"中

是否存在类似于MS SQL中强制向标识列插入的命令：

 SET IDENTITY_INSERT UserDetail ON

如果您有任何解决方案，请告诉我。

- Nayan Rudani

---

不要在 values() 或 into.. 中指定 UserDetailId。它将自动插入，这就是标识列的含义。 - Kaushik Nayak

Answer 1

GENERATED ALWAYS指示Postgres始终为标识列生成值。如果您尝试在此类列中插入值，Postgres将引发错误。

如果要进行插入操作，可以使用以下查询

INSERT INTO UserDetail (UserName, Password) 
  VALUES('admin', 'password');

如果您真的想插入到标识列中，可以使用GENERATED BY DEFAULT代替GENERATED ALWAYS。在这种情况下，如果您没有为标识列提供值，则Postgres将使用生成的值。

或者

您可以像下面示例一样使用OVERRIDING SYSTEM VALUE

INSERT INTO UserDetail (UserDetailId,UserName, Password) 
OVERRIDING SYSTEM VALUE 
  VALUES(1,'admin', 'password');

- Fathah Rehman P

---

是否存在像 MS SQL 中的 SET IDENTITY_INSERT UserDetail ON; 命令来强制插入到标识列的语句？ - Nayan Rudani

@NayanRudani - 请检查修改后的答案。 - Fathah Rehman P

@FathahRehmanP 如果表中已经存在 UserDetailId = 1，该怎么办？ - Rajnish kumar

@Rajnishkumar 如果 UserDetailId 是一个 PRIMARY KEY，那么数据库将拒绝任何试图复制值的尝试。通常只有在尝试恢复丢失的数据时，才会写入标识列。 - Manngo

请注意，将显式值插入自动递增字段不会更新Postgres用于生成该字段新值的序列，如果您在显式插入值后不手动更新序列，则可能会导致后续冲突。有关更新自动递增序列的信息，请参见此其他问题。 - Nick Muise