PostgreSQL实现on_air为true时uuid列唯一的插入函数

表结构 #

create table "deneme" (
    id serial primary key,
    uuid integer not null,
    on_air boolean default false
);

需求目标 #

uuid列需为六位整数，且所有on_air为true的记录中，uuid不能重复。需要实现逻辑：插入新记录时，让PostgreSQL自动生成唯一的六位uuid（在on_air=true的范围内）。

尝试代码及报错 #

尝试编写了如下DO块逻辑：

do $$
declare
    uuid integer := cast(000000 + floor(random() * 999999) as int);
begin
    while (select exists(select 1 from deneme where uuid = uuid and on_air = true)) loop
        uuid integer := cast(000000 + floor(random() * 999999) as int);
    end loop;
    insert into deneme (uuid, on_air) values (uuid, true);
end $$;

执行时报错：

ERROR:  syntax error at or near "uuid"

1. 修正基础语法错误 #

你的代码存在两个核心问题：

• 变量名uuid与表列名重名，导致where uuid = uuid被解析为列自身等于自身，永远返回true，会进入死循环；同时循环内重新赋值时无需重复声明变量类型。
• 生成随机数的写法冗余，000000在PostgreSQL中会被识别为八进制数（等价于0），直接用floor(random() * 1000000)即可生成0到999999的整数。

修正后的DO块代码：

do $$
declare
    new_uuid integer := floor(random() * 1000000)::integer;
begin
    while exists(select 1 from deneme where uuid = new_uuid and on_air = true) loop
        new_uuid := floor(random() * 1000000)::integer;
    end loop;
    insert into deneme (uuid, on_air) values (new_uuid, true);
end $$;

2. 解决并发冲突问题 #

上述DO块仅适用于单会话场景，多会话同时插入时，可能出现多个会话生成相同uuid、同时通过exists检查后插入重复值的情况。推荐以下两种解决方式：

方法一：添加部分唯一约束+异常重试 #

先给表添加针对on_air=true的部分唯一约束，从数据层面保证唯一性：

alter table deneme add constraint unique_on_air_uuid unique (uuid) where (on_air = true);

再编写带异常处理的函数，自动处理插入冲突并重试：

create or replace function insert_deneme_with_unique_uuid() returns void as $$
declare
    new_uuid integer;
begin
    loop
        new_uuid := floor(random() * 1000000)::integer;
        begin
            insert into deneme (uuid, on_air) values (new_uuid, true);
            exit; -- 插入成功则退出循环
        exception
            when unique_violation then
                -- 触发唯一约束冲突时，继续循环生成新uuid
                continue;
        end;
    end loop;
end;
$$ language plpgsql;

调用方式：

select insert_deneme_with_unique_uuid();

方法二：事务锁（低并发场景适用） #

如果是低并发场景，可在事务中锁定表，避免其他会话同时插入：

do $$
declare
    new_uuid integer;
begin
    begin transaction;
    lock table deneme in exclusive mode;
    
    loop
        new_uuid := floor(random() * 1000000)::integer;
        if not exists(select 1 from deneme where uuid = new_uuid and on_air = true) then
            insert into deneme (uuid, on_air) values (new_uuid, true);
            exit;
        end if;
    end loop;
    
    commit;
end $$;

补充说明 #

如果需要uuid是带前导零的六位字符串格式，建议将uuid列类型改为char(6)或varchar(6)，生成时用lpad(floor(random() * 1000000)::text, 6, '0')确保格式，同时对应调整唯一约束。

内容的提问来源于stack exchange，提问作者Kerem Nayman