Error Creating Sequence In PostgreSQL

Question

I have following PostgreSQL create script

CREATE SEQUENCE seq_tbl_ct_yr2_id  START (select max(ct_tran_id)+1 tranid from tbl_ct);

this doestnt create sequence arising folloeing error:

ERROR: syntax error at or near "(" LINE 1: create sequence test_1 start (select 1)

for test purpose i tested following scripts

create sequence test start 1 -- this works

create sequence test_1 start (select 1) -- this doesnt work

how to overcome this ??

Note : PostgreSQL 9.2

user330315 · Accepted Answer · 2015-01-22 06:55:47Z

3

You can't specify the start value as a sub-select. However you can set the sequence to a specific value using setval()

CREATE SEQUENCE seq_tbl_ct_yr2_id;
select setval('seq_tbl_ct_yr2_id', (select max(ct_tran_id)+1 tranid from  tbl_ct));

answered Jan 22, 2015 at 6:55

user330315

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

Comments

Vivek S. · Accepted Answer · 2015-01-22 06:49:17Z

0

DO
$$
declare
start_id int;
begin
select max(ct_tran_id)+1 from tbl_ct  into start_id;
execute 'CREATE SEQUENCE seq_tbl_ct_yr2_id  START '||start_id||'';
end;
$$

test using

select nextval('seq_tbl_ct_yr2_id')

sql-do

edited Jan 22, 2015 at 6:49

answered Jan 22, 2015 at 6:44

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Error Creating Sequence In PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related