如果条目不存在，请使用PostgreSQL多次插入 [英] Multiple Inserts with PostgreSQL if an entry does not exist

问题描述

如果条目不存在，我想将数据插入多个表中。

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

在我的情况下，我有一个餐厅表，一个位置表，一个 foodtype 表和一些帮助程序表，例如 restaurant_location 和 restaurant_foodtype 。现在，我想插入一个新的餐厅条目，如果该条目不存在，则包含位置和食物类型信息。

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.

所以类似：

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

如何使用简单的SQL执行此操作？

How can I do this with simple SQL?

推荐答案

我只是在脑海中写下了这个，但这应该是个主意，如果您必须简单地做到这一点的话

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')

---

编辑：
同样，我无法解析它，但您可能可以使用WITH子句：

---

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)

这篇关于如果条目不存在，请使用PostgreSQL多次插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！