PostgreSQL 将当前序列值插入到另一个有条件的字段

Question

问题:我需要做这样的事情

drop table if exists tt_t;
create temp table tt_t(id serial primary key, main_id int, external_id int);

insert into tt_t(main_id, external_id)
select currval(pg_get_serial_sequence('tt_t', 'id')), 1
where not exists (select from tt_t where external_id = 1);

但是执行会报错

SQL Error [55000]: ERROR: currval of sequence "tt_t_id_seq" is not yet defined in this session

解决方法:有一种方法可以用匿名代码块来解决这个问题

do
$$
begin
    if not exists(select from tt_t where external_id = 1)
    then
        insert into tt_t(external_id, main_id)
        values(1, currval(pg_get_serial_sequence('tt_t', 'id')));
    end if;
end;
$$
;

但是匿名 block 有一些限制，例如Dapper parameters not working with PostgreSQL through npgsql connection, is postgres anonymous function parameterization supported?

我如何在没有匿名代码块(UPD:并且没有任何 DDL 更改)的情况下修复它？

Answer 1

可能的解决方案:

insert into tt_t(id, main_id, external_id)
select nextval(pg_get_serial_sequence('tt_t', 'id')), currval(pg_get_serial_sequence('tt_t', 'id')), 1
where not exists (select from tt_t where external_id = 1);

有人向我推荐了更短的代码

insert into tt_t(id, main_id, external_id)
select nextval(pg_get_serial_sequence('tt_t', 'id')), lastval(), 1
where not exists (select from tt_t where external_id = 1);

但我不确定是否会先计算nextval