I'm using Postresql 9.3.5. I have a many-to-many relationship between entities "Foo" and "Bar" that I've modeled as something like:
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)
);
Now what I want to do is insert a record into Foo, insert a corresponding record into Bar, and then insert the matching FooBar record containing the ids of the foo & bar entries.
Catch: I don't know when I go to insert the Bar records if they already exist, so currently my insert for Bar looks something like:
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')
Which works fine, but my question: how do I get the id of the newly inserted (or existing) Bar record so that I can insert it into the FooBar table?
This seems to work, although it is far from elegant and is probably very inefficient:
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
I imagine this would be inefficient due to repeated searches in bar for the matching value. A more efficient solution might be to write a stored procedure.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With