PostgreSQL 多对多关系插入

Question

我正在使用PostgreSQL 9.3.5。我在“Foo”和“Bar”两个实体之间建立了多对多的关系，并将其建模为以下结构：

CREATE TABLE Foo
(
    id SERIAL PRIMARY KEY NOT NULL,
    .... various columns for foo ....
);

CREATE TABLE Bar
(
    id SERIAL PRIMARY KEY NOT NULL,
    field1 varchar(50) UNIQUE NOT NULL,
    .... various columns for bar ....
);

CREATE TABLE FooBar
(
    fooID int NOT NULL,
    barID int NOT NULL,
    PRIMARY KEY (fooID, barID),
    FOREIGN KEY (fooID) REFERENCES Foo(id),
    FOREIGN KEY (barID) REFERENCES Bar(id)
);

现在我想要做的是向Foo插入一条记录，向Bar插入相应的记录，然后插入包含foo和bar条目id的匹配FooBar记录。

注意：我不知道何时插入Bar记录是否已经存在，因此当前我的Bar插入看起来像这样：

INSERT INTO Bar(field1, .... other fields for Bar....) 
SELECT 'value1', .... other values for the insert....
WHERE NOT EXISTS (
SELECT 1 FROM Bar WHERE field1 = 'value1')

这样做是有效的，但我的问题是：我如何获取新插入（或已存在）的Bar记录的id，以便我可以将其插入到FooBar表中？

- Adam Parkin

Answer 1

这似乎可以工作，尽管它远非优雅，可能非常低效：

WITH new AS (
    INSERT INTO bar(field1) 
    SELECT ('aaa') WHERE NOT EXISTS (
        SELECT 1 FROM bar WHERE field1='aaa'
    ) RETURNING id
),
existing AS (
    SELECT id FROM bar WHERE field1='aaa'
) 
SELECT id FROM existing UNION SELECT id FROM new

我想这样做可能会因为在 bar 中重复搜索匹配值而效率低下。更高效的解决方案可能是编写一个存储过程。

- harmic

Answer 2

试一下

INSERT INTO Bar(field1, field2,etc...) values(value1, value2,etc...) RETURNING id;

SQL FIDDLE

{{链接1：SQL FIDDLE}}

- Dhaval Modi

---

再次从问题中提取：「我不知道在插入 Bar 记录时，如果它们已经存在该怎么办。」如果在记录已经存在时这样做，那么会导致错误。 - Adam Parkin