I have the task to insert data into couple tables and return data from their intersection in one query. The tables are linked through specific fields (solditems refers to products and invoices, invoices refers to customers etc). The trick here is that id in invoices is set automatically on row insert. The following query returns an empty result, unless I remove JOIN on invoices. If the SELECT is done in a separate query everything is OK. What's wrong?

WITH newid AS (
INSERT INTO invoices (customer,idate)
    VALUES (777,(SELECT now()::date))
sold AS (
INSERT INTO solditems (invoiceid,prod,qty)
    VALUES ((SELECT id FROM newid), 888, 1),
           ((SELECT id FROM newid), 999, 2)
   JOIN products AS p ON p.id=s.prod
   JOIN invoices AS i ON i.id=s.invoiceid;
  • 2
    What's wrong? INSERT in WITH performs "I will insert a row with returned ID" - but the row is not inserted yet. And you do not see it while selecting from invoices table. Return all columns in newid instead of id column only, and join this CTE instead of invoices table.
    – Akina
    Nov 16 at 12:22
  • 2
    Investigate dbfiddle.uk/laYcevlv If you need the whole table, not only inserted row then use (table UNION ALL cte).
    – Akina
    Nov 16 at 12:31
  • @Akina Thank you very much! Now it is clear to me and the modified query works as expected. If you post this as the answer, I will accept it. Thank you!
    – Outtruder
    Nov 16 at 13:05

1 Answer 1


Like Akina explained, all CTEs and the outer SELECT of the same command see the same snapshot of the table, so the new row is not visible to the SELECT, yet. See:

Return all you need in the RETURNING clause:

WITH ins_invo AS (
   INSERT INTO invoices (customer, idate)
   RETURNING *  --!
, ins_sold AS (
   INSERT INTO solditems (invoiceid, prod, qty)
   SELECT i.id, v.p, v.q  -- !
   FROM   ins_invo i
      VALUES (888, 1)
           , (999, 2)
      ) v(p, q)
FROM   ins_sold      s
JOIN   ins_invo      i ON i.id = s.invoiceid
LEFT   JOIN products p ON p.id = s.prod  --!

You also join to products to retrieve (assumingly one) pre-existing row from table products. Use a LEFT JOIN there, or you get nothing back if there is no matching row in products. Unless referential integrity is enforced with an FK constraints from sold.prodproducts.id, then you'd get an exception for the missing products.id, and this point is moot. (Doesn't hurt in any case, though.)

Also simplified repeated SELECT, where you only need one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.