the scenario is as follows:

I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier table, first insert into supplier then use the new supplier_id in invoice, else use the supplier_id of the matching supplier_name.

I've managed to get here:

insert into supplier (supplier_company) select ('Company Name') where not exists (select 1 from supplier where supplier_company='Company Name');
insert into invoice (invoice_number, supplier_id) values (xxx, (select supplier_id from supplier where supplier_company = 'Company Name')

but I was wondering if there's a cleaner, clearer way to express this in one expression.

Thank you for your time.

  • I think this answer to a very similar question is quite elegant: dba.stackexchange.com/questions/46410/… Note: not my answer there but Erwin's (the code under the INSERT missing FK rows at the same time title). Commented Jun 2, 2015 at 11:51
  • A single statement can change at most one table. So it is impossible for one expression to insert rows into two tables.
    – Andomar
    Commented Jun 2, 2015 at 15:09
  • @ypercube I agree, it does what I'm looking for well, thank you! Commented Jun 4, 2015 at 11:57
  • 1
    @Andomar in Postgres, you can insert into / update / delete from 2 (or more) tables in one statement, using CTEs. Commented Jun 4, 2015 at 12:43


Your Answer

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

Browse other questions tagged or ask your own question.