CODEWITHSUNDEEP
postgresql
iliyesku
iliyesku

Reputation: 401

PostgreSQL insert current sequence value to another field with condition

the issue: i need to do something like this

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

but execution raises an error

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

solution: there is a way to solve this with anonymous code block

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;
$$
;

but anonymous blocks has some restrictions e.g. Dapper parameters not working with PostgreSQL through npgsql connection, is postgres anonymous function parameterization supported?

how do i fix it without anonymous code block (UPD: and without any DDL changes)?

Upvotes: 1

Views: 1088

Answers (2)

user330315
user330315

Reputation:

What about using a default value:

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

insert into tt_t(external_id)
select 1
where not exists (select * from tt_t where external_id = 1);

In theory it shouldn't be possible that another nextval() is called between the one for the id and the lastval(). However I am not 100% sure if there are some corner cases that I don't know of.

The following works as well (even if one or more of the external_id values already exist).

insert into tt_t(external_id)
select *
from (values (1),(2),(3)) x (external_id)
where not exists (select * 
                  from tt_t 
                  where external_id = x.external_id);

Upvotes: 1

iliyesku
iliyesku

Reputation: 401

probable solution:

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

shorter code has been proposed to me

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

but i'm not sure if nextval will be calculated first

Upvotes: 1

Related Questions