CODEWITHSUNDEEP
sqlpostgresqlspring-bootplpgsqlspring-data-r2dbc
Nikolas
Nikolas

Reputation: 44408

PL/pgSQL restart generated sequence

I have an initialization script I use on Spring Boot application start and I create the table if it doesn't exist. Then I delete all the data from the table and execute a bunch on inserts.

create table if not exists employee (
    id serial primary key,
    name varchar(255)
);

delete from employee;

-- inserts

With each execution of the script, the sequence still continues, so the new rows don't start from one. I have to reset such sequence too, however, it is generated and I dont know its name unless I call this script:

select pg_get_serial_sequence('employee', 'id');
-- returns public.employee_id_seq

I tried to combine it together and reset the sequence based on the output of this funciton, but with no luck. How to reset the generated sequence without knowing its name? My attempt so far cannot resolve the seq sequence from the variable:

do $$
    declare
        seq varchar(255);
    begin
        select pg_get_serial_sequence('employee', 'employee_id') into seq;
        alter sequence seq restart with 1;
end; $$;

Upvotes: 1

Views: 432

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

The simplest solution is to use truncate table . . . restart identity instead of delete:

truncate table employee restart identity;

Here is a db<>fiddle.

Truncate table is recommended for other reasons too. For instance, it reclaims the space the table used immediately. And it is much faster. The one difference is that delete triggers are not called (although your table doesn't have any triggers).

Upvotes: 1

nbk
nbk

Reputation: 49375

It is basically a bad idea to reset a id serial to any number, so you must be very carefully using the following.

especially when you have foreign keys to the id firld

the camand you are looking for is

ALTER SEQUENCE <table_name>_<id_column>_seq RESTART WITH 1

create table if not exists employee (
    id serial primary key,
    name varchar(255)
);

INSERT INTO employee (name) VALUES ('a'),('B'),('C')

3 rows affected

delete from employee;

3 rows affected

ALTER SEQUENCE employee_id_seq RESTART WITH 1

INSERT INTO employee (name) VALUES  ('a')

1 rows affected

SELECT * FROM employee

id | name
-: | :---
 1 | a

db<>fiddle here

Upvotes: 1

Related Questions