主页 / user-11940364

Peter Fence's questions

Martin Hope
Peter Fence
Asked: 2024-11-19 02:20:07 +0800 CST
  • 6

想象一下这样的桌子

create table public.my_table (
    id          uuid primary key,
    category_id uuid not null,
    name        text not null,
    is_active   bool not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now()
);

有一条规则：一个 category_id 必须只有一个记录is_active=true

我可以想出两种添加记录的方法：

＃1 使用 CTE 插入添加新的活动记录+更新以停用上一个：

WITH new_record AS (
    INSERT INTO my_table (id, category_id, name, is_active) VALUES (?, ?, ?, true)
    RETURNING id, category_id
)
UPDATE my_table
SET is_active  = false,
    updated_at = now()
FROM new_record
WHERE my_table.category_id = new_record.category_id
  AND my_table.id <> new_record.id
  AND my_table.is_active = true

#2 在事务中进行单独的插入和更新查询：

BEGIN TRANSACTION;

INSERT INTO my_table (id, category_id, name, is_active) VALUES (?, ?, ?, true);

UPDATE my_table
   SET is_active  = false,
       updated_at = now()
   WHERE my_table.category_id = ?
         AND my_table.id <> ?
         AND my_table.is_active = true;

COMMIT;

我喜欢第二种方式，因为它简单。

第一种方式相对于第二种方式有什么优势吗？

postgresql