database 使用 SERIAL 函数声明的列将记录插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12836043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting a record into a table with a column declared with the SERIAL function
提问by AntiGMO
My database is using PostgreSQL. One table is using the serialauto-increment macro. If I want to insert a record into the table, do I still need to specify that value, or it is be automatically assigned for me?
我的数据库正在使用 PostgreSQL。一张表正在使用serial自动增量宏。如果我想在表中插入一条记录，我还需要指定那个值，还是自动分配给我？
CREATE TABLE dataset
(
id serial NOT NULL,
age integer NOT NULL,
name character varying(32) NOT NULL,
description text NOT NULL DEFAULT ''::text
CONSTRAINT dataset_pkey PRIMARY KEY (id)
);
回答by Craig Ringer
Using the DEFAULTkeyword or by omitting the column from the INSERTlist:
使用DEFAULT关键字或省略INSERT列表中的列：
INSERT INTO dataset (id, age, name, description)
VALUES (DEFAULT, 42, 'fred', 'desc');
INSERT INTO dataset (age, name, description)
VALUES (42, 'fred', 'desc');
回答by francs
If you create a table with a serial column then if you omit the serial column when you insert data into the table PostgreSQL will use the sequence automatically and will keep the order.
如果您创建一个带有串行列的表，那么如果您在向表中插入数据时省略串行列，PostgreSQL 将自动使用该序列并保持顺序。
Example:
例子：
skytf=> create table test_2 (id serial,name varchar(32));
NOTICE: CREATE TABLE will create implicit sequence "test_2_id_seq" for serial column "test_2.id"
CREATE TABLE
skytf=> insert into test_2 (name) values ('a');
INSERT 0 1
skytf=> insert into test_2 (name) values ('b');
INSERT 0 1
skytf=> insert into test_2 (name) values ('c');
INSERT 0 1
skytf=> select * From test_2;
id | name
----+------
1 | a
2 | b
3 | c
(3 rows)
回答by Hiram Walker
These query work for me:
这些查询对我有用：
insert into <table_name> (all columns without id serial)
select (all columns without id serial)
FROM <source> Where <anything>;
回答by ChetPrickles
Inserting multiple rows wasn't working for me in this scenario:
在这种情况下，插入多行对我不起作用：
create table test (
id bigint primary key default gen_id(),
msg text not null
)
insert into test (msg)
select gs
from generate_series(1,10) gs;
because I had mistakenly marked my gen_id function IMMUTABLE.
因为我错误地将我的 gen_id 函数标记为 IMMUTABLE。
The insert query was being optimized to only call that function once rather than 10 times. Oops...
插入查询被优化为只调用该函数一次而不是 10 次。哎呀...
