Question 215002 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create new related entity and enter its id into a link table

My foos each have a bar.

At least they’re supposed to.

I now want to create and assign bars to all foos that don’t.

bar is completely detached from foo, it only has an id serial that serves as its primary key

CREATE TABLE bar
(
    id SERIAL PRIMARY KEY
);

bars are attached to foos by use of a foo_bar link table.

CREATE TABLE foo_bar
(
    foo_id INTEGER NOT NULL
        CONSTRAINT foo_bar__foo__fk
            REFERENCES foo
            ON DELETE CASCADE,
    bar_id  INTEGER NOT NULL
        CONSTRAINT foo_bar__bar__fk
            REFERENCES bar
            ON DELETE RESTRICT,
    CONSTRAINT foo_bar__pk
        PRIMARY KEY (foo_id, bar_id)
);

my first attempt

WITH
foos_without_bar AS (
    SELECT f.id
    FROM foo f
    WHERE NOT EXISTS(
        SELECT 1
            FROM foo_bar fb
            WHERE fb.foo_id = f.id
    )
),
new_bars AS (
    INSERT INTO bar(id)
        SELECT DEFAULT
        FROM foos_without_bar f
        RETURNING f.id as foo_id, bar.id as bar_id
)
INSERT INTO foo_bar(foo_id, bar_id)
    SELECT n.foo_id, n.bar_id
    FROM new_bars n
;

fails with

ERROR:  DEFAULT is not allowed in this context
LINE 13:         SELECT DEFAULT
                        ^

and my second using nextval:

WITH
foos_without_bar AS (
    SELECT f.id
    FROM foo f
    WHERE NOT EXISTS(
        SELECT 1
            FROM foo_bar fb
            WHERE fb.foo_id = f.id
    )
),
new_bars AS (
    INSERT INTO bar(id)
        SELECT nextval(pg_get_serial_sequence('bar','id'))
        FROM foos_without_bar f
        RETURNING f.id as foo_id, bar.id as bar_id
)
INSERT INTO foo_bar(foo_id, bar_id)
    SELECT n.foo_id, n.bar_id
    FROM new_bars n
;

also fails, this time because

ERROR:  missing FROM-clause entry for table "f"
LINE 15:         RETURNING f.id as foo_id, bar.id as bar_...
                           ^

and I’m not quite sure how to rectify that.

How do I make this statement work?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I gave up and decided to just write a function:

    DO $createMissingBars$
DECLARE
    new_bar_id INTEGER;
    foos_without_bar INTEGER[];
BEGIN
    foos_without_bar := ARRAY (
        SELECT f.id
        FROM foo f
        WHERE NOT EXISTS(
            SELECT 1
            FROM foo_bar fb
            WHERE fb.foo_id = f.id
        )
    );

    FOR i in 1 .. array_upper(foos_without_bar,1) LOOP
        INSERT INTO foo_bar
            DEFAULT VALUES
            RETURNING bar.id INTO new_bar_id
        ;
        INSERT INTO foo_bar(foo_id, bar_id)
            VALUES (foos_without_bar[i], new_bar_id)
        ;
    END LOOP;
END $createMissingBars$;

  2. 0

    From your explaination, I think that you want to create a new bar for every foo that doesn’t have one, and then create an entry in the foo_bar table to link the foo and its new bar.

    There’s a problem with the way you’re trying to insert rows into bar and return the corresponding foo_id from foos_without_bar. The RETURNING clause can only return values that were actually inserted into the table.

    You can solve this by first generating bar for each foo in foos_without_bar and then inserting the links in foo_bar.

    If that’s what you want try below query,

    WITH
foos_without_bar AS (
    SELECT f.id
    FROM foo f
    WHERE NOT EXISTS(
        SELECT 1
        FROM foo_bar fb
        WHERE fb.foo_id = f.id
    )
),
new_bars AS (
    INSERT INTO bar(id)
        SELECT nextval(pg_get_serial_sequence('bar','id'))
        FROM foos_without_bar
        RETURNING id as bar_id
),
foo_bar AS (
    SELECT f.id as foo_id, nb.bar_id
    FROM foos_without_bar f
    JOIN new_bars nb ON TRUE
)
INSERT INTO foo_bar(foo_id, bar_id)
    SELECT fb.foo_id, fb.bar_id
    FROM foo_bar fb
;

    The JOIN ON TRUE operation in the foo_bar subquery pairs each foo in foos_without_bar with each bar in new_bars. Since there is the same number of foo and bar (one bar for each foo), each foo is matched with the bar that corresponds to it by order.

    Be aware that this operation assumes that foos_without_bar and new_bars return rows in the same order. Normally, this should be the case, but it’s not guaranteed by SQL standards, and might not be true if your actual query is more complex than the provided example.

    As a final note, remember to be careful with concurrency issues. If this code is running while other code might be modifying the foo, bar, or foo_bar tables, there could be race conditions. Consider using appropriate locking mechanisms if necessary.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search