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?

There's no "simple" SQL query that inserts into multiple tables. But it's easily done in procedural code in plpgsql, which looks quite like the pseudo-code in the question. If you can't use that for some reason, more info on the context would be welcome. — Daniel Vérité, Aug 28 '12 at 11:40
8.1 has been out of support for a long time. You should upgrade ***now***, really. — , Aug 28 '12 at 11:41
@DanielVérité: Well, since Postgres 9.1 it can be done with a "simple" SQL statement using data-modifying CTEs. I posted an example. — Erwin Brandstetter, Aug 28 '12 at 13:58

Erwin Brandstetter · Answer 1 · 2014-02-25T21:43:48.067

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.

That was basically what I had in mind when asking for the version ;) — , Aug 28 '12 at 14:30
Tis is "fast, safe, simple and elegant"? I'm just using postGreSQL for one project, and I have to wonder if this really the simplest way to do this very comment operation. — joelm, Jun 18 '13 at 14:00
If you wrap this in a function that returns a composite data type, an empty row (instead of no rows) is returned if exists. Why's that? http://stackoverflow.com/questions/22700215/shouldnt-this-postgresql-function-return-zero-rows — ma11hew28, Mar 27 '14 at 22:06

Erkan Haspulat · Accepted Answer · 2012-08-28T11:13:14.340

1

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)

edited Aug 28 '12 at 11:13

answered Aug 28 '12 at 11:01

Erkan Haspulat

12,032
6
39
45

This works great for exactly one insert statement. But I have multiple ones. just adding the WHERE NOT EXISTS to every single INSERT does not work, cause after the first insert, the entry should exist – Stefan Arn Aug 28 '12 at 11:03
1

If the restaurant doesn't exist for the first row, you'll insert a row. *That* restaurant's id number is accessible through the [currval() function](http://www.postgresql.org/docs/9.1/static/functions-sequence.html). You don't need to check related inserts for the existence of an id number; foreign key constraints guarantee it can't possibly exist unless your database design is badly broken. – Mike Sherrill 'Cat Recall' Aug 28 '12 at 11:12

Multiple Inserts with PostgreSQL if an entry does not exist

2 Answers2

Linked