Question 391123 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SETVAL for Sequence only after creation

I’m working with PostgreSQL and have a script that attempts to create a sequence named hibernate_seq and set its starting value:

CREATE SEQUENCE IF NOT EXISTS hibernate_seq;
SELECT setval('hibernate_seq', (SELECT MAX(NEXT_HI) + 1 FROM HIBERNATE_ID_GENERATION));

However, I want to achieve the following behavior:

Create the sequence hibernate_seq only if it doesn’t already exist.
Set the sequence’s starting value but only if the sequence is newly created.
In other words, if the sequence hibernate_seq already exists, I don’t want to accidentally modify its current value.

how can I do that?

Tags:

2

Answers


  1. 0

    Something like:

    DO $$
BEGIN
PERFORM * FROM pg_class WHERE relname = 'hibernate_seq';

IF NOT FOUND THEN
   CREATE SEQUENCE 'hibernate_seq';
   PERFORM setval('hibernate_seq', (SELECT MAX(NEXT_HI) + 1 FROM  HIBERNATE_ID_GENERATION));
ELSE
    RAISE NOTICE 'hibernate_seq found';
END IF;
END $$;

    This uses an anonymous function(DO) and FOUND from here Result status to test whether the sequence already exists and if it does not the creates the sequence and sets the value. Otherwise leaves the sequence alone

    Login or Signup to reply.
  2. 0

    A safe version that covers most possible hickups – and attaches the new sequence properly to make it a serial column.

    DO
$do$
DECLARE
   _target_seq text := quote_ident('hibernate_seq');  -- assuming current default schema?!
   _target_tbl text := quote_ident('hibernate_id_generation');
   _target_col text := quote_ident('next_hi');
   _actual_seq text;
   _relkind    "char";
   _owning_col text;
BEGIN
   -- Column already has a sequence?
   SELECT INTO _actual_seq  pg_get_serial_sequence(_target_tbl, _target_col);

   IF _actual_seq IS NOT NULL THEN
      RAISE NOTICE '% already has an attached sequence named %', _target_tbl, _actual_seq;
      RETURN;  -- exit early
   END IF;

   -- Sequence name free?
   SELECT INTO _relkind  c.relkind
   FROM   pg_class c
   WHERE  c.relname = _target_seq;  -- your sequence here (assuming schema public?!)

   IF FOUND THEN
      RAISE NOTICE 'A % named % already exists!'
                 , CASE _relkind
                     WHEN 'S' THEN 'sequence'
                     WHEN 'r' THEN 'ordinary table'
                     WHEN 'p' THEN 'partitioned table'
                     -- more types?
                     ELSE 'unspecified object type'
                   END
                 , _target_seq;
      RETURN;  -- exit early
   END IF;

   -- create sequence, attach it properly, and set dolumn default
   -- *at this point* raise an exception if concurrent transaction just created a conflicting object (highly unlikely)
   EXECUTE format('CREATE SEQUENCE %s', _target_seq);
   EXECUTE format('ALTER SEQUENCE %s OWNED BY %s.%s', _target_seq, _target_tbl, _target_col);  -- attach it properly !
   EXECUTE format('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval(%L)', _target_tbl, _target_col, _target_seq);  -- set default ?
   -- sync sequence safely
   EXECUTE format('SELECT setval(%L, COALESCE(max(%s) + 1, 1), false) FROM %s', _target_seq, _target_col, _target_tbl);
END
$do$;

    See:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search