postgresql 冲突时更新整行

35g0bw71  于 2023-01-02  发布在  PostgreSQL
关注(0)|答案(2)|浏览(172)

我有一个名为customers的表,它有许多列:'id', 'name', 'age', 'weight', 'hight', 'blood', 'sex', 'last name', 'datetime'.
我想在插入之前添加一个检查逻辑,即如果datetime已经存在,则更新/覆盖整个现有行。
我的代码:

INSERT INTO customers (id,name,age,weight,hight,blood,sex,last name,datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO 
   UPDATE SET name = EXCLUDED.name || ';' || customers.name;
   ....
   UPDATE SET name = EXCLUDED.datetime || ';' || customers.datetime;

因为表有很多列,如果datetime已经存在,我想更新/覆盖整行,那么有没有更简单的方法来代替对每列都使用UPDATE SET

jhdbpxl9

jhdbpxl91#

你能做到的...

INSERT INTO customers (id,name,age,weight,hight,blood,sex,last name,datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO 
   UPDATE SET
     name = EXCLUDED.name || ';' || customers.name,
     datetime = EXCLUDED.datetime || ';' || customers.datetime,
     ...and so on...

但这是一个坏主意,原因有很多。
1.你必须把所有东西都存储为varchar,你没有类型保护,不能使用任何函数。
1.搜索将是复杂和低效的,因为每次搜索都必须首先解析值。
1.数据的大小将不断增长。
1.没有指示值何时更新。
假设这是为了记录更改以便以后进行审计,那么使用审计表来存储旧值。这可以是一个特定的表,它逐列地镜像原始值,但是使用单个表和JSON更容易。

create table audits (
  id bigserial primary key,
  table_name text not null,
  data jsonb not null,
  event text not null,
  changed_at timestamp default(current_timestamp)
)

然后,您可以让触发器在每次更改时写入审计表。event可用于记录它是插入、更新还是删除。您还可以记录是哪个用户进行了更改。

bogh5gae

bogh5gae2#

使用合并
这里有很好的例子https://www.sqlshack.com/understanding-the-sql-merge-statement/
您的目标表将是Customers表,您需要创建一个包含要插入/更新的记录的虚拟表,我们将其命名为Record

MERGE Customers AS Target
USING Record AS Source
ON Source.datetime = Target.datetime And id Source.id = Target.id

-- For Inserts
WHEN NOT MATCHED BY Target THEN
    -- insert the record here

-- For Updates
WHEN MATCHED THEN UPDATE SET
    -- Update here

解决方案不完整,您需要将值放入虚拟表并更新insert和update语句,但您已经了解了这个想法。

相关问题