在 postgresql 中生成自动 ID [英] Generate auto ID in postgresql

问题描述

用户表:

    ID  Name
    1   usr1
    2   usr2
    3   usr3

在上表中，ID 是主键.我的要求是在向表中插入数据时，我只想指定名称如 INSERT INTO user VALUES('usr4').执行查询后，有没有办法自动为'usr4'创建ID?

In the above table, ID is a primary key. My requirement is while inserting data into the table, I would like to specify only the name like INSERT INTO user VALUES('usr4'). After execute the query, Is there any way to automatically create ID for 'usr4'?

我尝试使用串行数据类型.为此，我们还必须指定关键字 default，如 INSERT INTO user VALUES(default,'usr4').那么，有什么办法可以像 INSERT INTO user VALUES('usr4').注意:ID 应该是唯一的.

I tried with serial data type. For that also we have to specify the keyword default like INSERT INTO user VALUES(default,'usr4'). So, is there any way to do like INSERT INTO user VALUES('usr4'). NOTE: ID should be unique.

推荐答案

使用内置数据类型 serial 或 bigserial.

Use the built-in data type serial or bigserial.

create table users (
  id serial primary key,
  name varchar(100) not null unique -- ?
);

为列命名；省略 serial 或 bigserial 列的名称.

Name the column(s); omit the name of the serial or bigserial column.

insert into users (name) values ('usr4');

一般规则是您必须为 INSERT 语句中使用的每一列提供一个值.如果不指定列名，则必须为每一列提供一个值，包括Id"，并且必须按照列在表中出现的顺序提供它们.

The general rule is that you have to supply one value for each column used in an INSERT statement. If you don't specify column names, you have to supply a value for every column, including "Id", and you have to supply them in the order the columns appear in the table.

如果指定列名，则可以省略具有默认值的列和可为空的列，并且可以按任意顺序放置列名.值的顺序必须与您指定的列名称的顺序相匹配.

If you specify column names, you can omit columns that have defaults and columns that are nullable, and you can put the column names in any order. The order of the values has to match the order of the column names you specify.

这篇关于在 postgresql 中生成自动 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！