PostgreSql 从选择返回 ID 插入 [英] PostgreSql INSERT FROM SELECT RETURNING ID

问题描述

在 PostgreSql 9.2.4 中，我有两个表:user (id, login, password, name) 和 dealer (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');

但是我可以使用 RETURNING 语句通过单个 INSERT 查询来实现吗?

But can I implement this with a single INSERT query using RETURNING statement?

推荐答案

你只需要在你的INSERT ... SELECT中添加一个RETURNING id:

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

这篇关于PostgreSql 从选择返回 ID 插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！