Multiple Inserts with PostgreSQL if an entry does not exist

Question

I would like to insert data into multiple tables if an entry does not exist.

In my case I have a restaurant table, a location table, a foodtype table and some helper tables like restaurant_location, and restaurant_foodtype. Now I would like to insert a new restaurant entry complete with the location and the foodtype info if the entry does not exist.

So something like:

IF NOT (select 1 from restaurant where name='restaurantname') THEN
 INSERT INTO restaurant(x,y) VALUES (valuex,valuey);
 INSERT INTO restaurant_location(rest_id,..) VALUES (rest_id,..);
 INSERT INTO restaurant_foodtype(rest_id,..) VALUES (rest_id,..);
 ...
END IF

How can I do this with simple SQL?

Answer 1

In Postgres 9.1 or later you can use data-modifying CTEs to make this fast, safe, simple and elegant:

WITH x AS (
   INSERT INTO restaurant(name, x, y)
   SELECT 'restaurantname', valuex, valuey
   WHERE  NOT EXISTS (SELECT 1 FROM restaurant WHERE name = 'restaurantname')
   RETURNING rest_id     -- returns auto-generated id (from sequence)
   )
, y AS (
   INSERT INTO restaurant_location(rest_id, ...)
   SELECT rest_id, ...
   FROM   x              -- only produces rows after a successful INSERT
   )

   --- more chained INSERTs here?

INSERT INTO restaurant_foodtype(rest_id, ...)
SELECT rest_id, ...
FROM   x;

The first INSERT is only executed if 'restaurantname' is not found. There is a super-tiny race-condition if multiple queries should try the same in the same instance. If you have a UNIQUE constraint on restaurant.name (like you should judging from your description), the worst that could happen is that among concurring queries only the first would succeed while the others return with a unique violation (doing nothing). Chances are, however, you will never see this, because it is very unlikely to happen.

The RETURNING clause returns the automatically generated rest_id - I assume rest_idis a serial column.

The following INSERT queries only generate rows if the first one succeeded.

Finish the series with be a plain INSERT.

With PostgreSQL 8.1 I would write a plpgsql function to achieve the same.
But, really, you'd better upgrade to a current version.

Answer 2

I just wrote this at the top of my head but this should be the idea, if you must do it with simple sql.

insert into 
    restaurant(x, y)
values
    select valuex, valuey 
    from dual
    where
      not exists(
        select 1 from restaurant where name = 'restaurantname')

---

EDIT: Again, I couldn't parse it but you probably could make use of the WITH clause:

with validation as(
  select 1 from restaurant where name = 'restaurantname'
)
insert into 
    restaurant(x, y)
values
    (
     select value1x, value1y 
     from dual
     where
       validation.v = 1),
    (
     select value2x, value2y 
     from dual
     where
       validation.v = 1)