使用Postgresql v9.1.我有以下表格：
CREATE TABLE foo
(
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,type VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE bar
(
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,description VARCHAR(40) NOT NULL UNIQUE,foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);
假设第一个表foo填充如下：
INSERT INTO foo (type) VALUES
( 'red' ),( 'green' ),( 'blue' );
有没有办法通过引用foo表轻松地将行插入到栏中？或者我必须分两步完成,首先查找我想要的foo类型,然后在栏中插入一个新行？
INSERT INTO bar (description,foo_id) VALUES
( 'testing',SELECT id from foo WHERE type='blue' ),( 'another row',SELECT id from foo WHERE type='red' );
你的语法几乎是好的,在子查询周围需要一些括号,它会起作用：
INSERT INTO bar (description,(SELECT id from foo WHERE type='blue') ),(SELECT id from foo WHERE type='red' ) );
测试时间为SQL-Fiddle
另一种方法,如果要插入大量值,则使用较短的语法：
WITH ins (description,type) AS
( VALUES
( 'more testing','blue'),( 'yet another row','green' )
)
INSERT INTO bar
(description,foo_id)
SELECT
ins.description,foo.id
FROM
foo JOIN ins
ON ins.type = foo.type ;
