从SELECT RETURNING ID插入PostgreSQL [英] PostgreSql INSERT FROM SELECT RETURNING ID
本文介绍了从SELECT RETURNING ID插入PostgreSQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在PostgreSql 9.2.4中,我有两个表: user(ID,登录名,密码,名称)
和经销商(id,user_id)
。
In PostgreSql 9.2.4 I have two tables: user (id, login, password, name)
and dealer (id, user_id)
.
我想插入两个表,返回创建的交易商的ID。
And I want to insert into both tables returning id of created dealer.
目前,我正在通过两个查询来做到这一点:
Currently I'm doing it with two queries:
WITH rows AS (
INSERT INTO "user"
(login, password, name)
VALUES
('dealer1', 'jygbjybk', 'Dealer 1')
RETURNING id
)
INSERT INTO dealer (user_id)
SELECT id
FROM rows;
SELECT currval('dealer_id_seq');
但是我可以用一个 INSERT
使用 RETURNING
语句查询?
But can I implement this with a single INSERT
query using RETURNING
statement?
推荐答案
您只需要添加一个返回ID
到您的 INSERT ... SELECT
:
You just need to add a RETURNING id
to your INSERT ... SELECT
:
WITH rows AS (...)
INSERT INTO dealer (user_id)
SELECT id
FROM rows
RETURNING id;
演示: http://sqlfiddle.com/#!12/75008/1
这篇关于从SELECT RETURNING ID插入PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文