Create Sequence in PostgreSQL

Question

I am trying to create a new sequence in PostgreSQL with the start value being the max of all available ids in all the tables using the following query:

CREATE SEQUENCE idschema.global_id_sequence INCREMENT 1 START (
  SELECT MAX(t.max_id) FROM 
  (
    (SELECT MAX(public.tbl1.id) max_id FROM public.tbl1) UNION
    (SELECT MAX(public.tbl2.id) max_id FROM public.tbl2) UNION
    (SELECT MAX(public.tbl3.id) max_id FROM public.tbl2) UNION
  ) t
);

This doesn't seem to work. How can I achieve the above in PostgreSQL using scalars or other means?

user330315 · Accepted Answer · 2020-01-13 14:00:02Z

3

You can't use a query to provide the start value in CREATE SEQUENCE. You need to use setval() after you created the sequence:

create sequence idschema.global_id_sequence;
with global_max as (
  select max(max_id) as max_id
  from (
    SELECT MAX(id) max_id FROM public.tbl1 
    UNION ALL
    SELECT MAX(id) max_id FROM public.tbl2
    UNION ALL
    SELECT MAX(id) max_id FROM public.tbl3
  ) t
)
select setval('idschema.global_id_sequence', (select max_id from global_max));

answered Jan 13, 2020 at 14:00

user330315

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

Comments

Jim Jones · Accepted Answer · 2020-01-13 14:09:36Z

0

An alternative is to do it in two parts:

CREATE SEQUENCE seq INCREMENT 1 START WITH 1;
SELECT setval('seq',  
 (SELECT max(val) FROM (
    SELECT max(id) AS val FROM t1 UNION ALL 
    SELECT max(id) AS val FROM t2) j));

Check setval at the documentation.

edited Jan 13, 2020 at 14:09

answered Jan 13, 2020 at 14:03

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Create 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