PostgreSQL insert current sequence value to another field with condition

Question

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

iliyesku · Accepted Answer · 2018-07-13 15:41:15Z

1

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

edited Jul 13, 2018 at 15:41

answered Jul 13, 2018 at 15:31

iliyesku

4113 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2018-07-13 15:44:03Z

1

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

answered Jul 13, 2018 at 15:44

user330315

1 Comment

iliyesku Over a year ago

Unfortunately I can not make any DDL changes.

Collectives™ on Stack Overflow

PostgreSQL insert current sequence value to another field with condition

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related