How to check if an sequence is above a certain number and if not change it in Postgres

Question

I have a problem where my SQL sequence has to be above a certain number to fix a unique constraint error. Now I started to write an if-statement that checks for a certain number and if it's below it should be increased to a certain number. The statement is for Postgres.

I got the separate parts running but the connection over if is throwing an error and I don't know why.

First for selecting the current number:

SELECT nextval('mySequence')

Then to update the number:

SELECT setval('mySequence', targetNumber, true)

The full statement looks something like this in my tries:

IF (SELECT nextval('mySequence') < targetNumber)
THEN (SELECT setval('mySequence', targetNumber, true))
END IF;

and the error is

ERROR:  syntax error at »IF«

Can someone explain to me what I did wrong there because the error message isn't giving me much to work with? I would appreciate your help.

SQL is a query language and has no support for procedural constructs like `IF`. — , Jan 15 '21 at 12:48
Are you looking for this: [How to reset postgres' primary key sequence when it falls out of sync](https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync)? — , Jan 15 '21 at 12:49
If this is only for a single table: `select setval('mysequence', (select max(id) from the_table))` — , Jan 15 '21 at 12:50
could i use something else to recreate some kind of mechanism like an if-statement read about case then and some stuff that would make it possible? — matse, Jan 15 '21 at 12:51
You don't need all that. You can sync the sequence without any procedural code, CASE expressions or similar. `select setval('mysequence', max(id)) from the_table;` — , Jan 15 '21 at 12:53
I dont think that will work for my case. But anyways thank you very much for your help i would have wasted a lot of time tring to figgure this if-statement out without your help. — matse, Jan 15 '21 at 13:02
Why wouldn't it work? You want to synchronize the sequence with the highest value of that column - this is exactly what that statement does. — , Jan 15 '21 at 13:03
i use this sequence over multiple tables so i cant just set it to a max value of one table because it would create problems for other tables — matse, Jan 15 '21 at 13:07

score 1 · Answer 1 · answered Jan 15 '21 at 13:11

1

Try this:

   SELECT setval('mySequence', targetNumber, true) 
    WHERE (SELECT nextval('mySequence') < targetNumber) is true;

answered Jan 15 '21 at 13:11

Hard_Coder

718
6
13

score 1 · Answer 2 · answered Jan 15 '21 at 13:18

You can use postgres functions if you want to use IF statement. You can try something like this:


CREATE SEQUENCE seq_test_id_seq;

CREATE TABLE seq_test(
    id integer NOT NULL DEFAULT nextval('seq_test_id_seq'),
     name VARCHAR 
);


CREATE OR REPLACE FUNCTION seq_test_function(target_number bigint)
    RETURNS void
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100
    
AS $BODY$
DECLARE
    seq_val INTEGER;
    BEGIN

        SELECT nextval('seq_test_id_seq') INTO seq_val;

        RAISE NOTICE 'NEXT SEQUENCE [%]', seq_val;
        
        IF (seq_val < target_number) THEN 
            SELECT setval('seq_test_id_seq', target_number, true) INTO seq_val;
            RAISE NOTICE 'SEQUENCE VALUE MODIFIED [%]', seq_val;
        END IF;
    END;
$BODY$;

Then call the procedure:

select seq_test_function(10);

How to check if an sequence is above a certain number and if not change it in Postgres

2 Answers2