PL/pgSQL restart generated sequence

Question

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; $$;

Answer 1

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).