Thread: [GENERAL] Reset Sequence number

Firstly, anonymous procedures are not supported in PostgreSQL, you need to
embed this block in a plpgsql function[1] body & call that function if you want
reset sequence value manually, or you could use CYCLE option[2] of a
sequence to auto reset.


1] https://www.postgresql.org/docs/9.6/static/plpgsql-structure.html
2] https://www.postgresql.org/docs/devel/static/sql-createsequence.html


Regards,
Amul

On Wed, Nov 22, 2017 at 3:06 PM, Brahmam Eswar <brahmam1234@gmail.com> wrote:
>
> we are in process of migrating to postgres and need to reset the sequence
> number with highest value of table key . I want to make it procedural to do
> that as mentioned below,But it's throwing an error .
>
>
>
> DO $$
>  DECLARE
>  SEQ BIGINT;
>  BEGIN
>
>   SEQ:=(SELECT MAX(ID) FROM TABLE_1);
>   ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;
>
>  END$$;
>
> Error : syntax error at or near "SEQ"
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.

On 22/11/17 09:36, Brahmam Eswar wrote:
> 
> we are in process of migrating to postgres and need to reset the 
> sequence number with highest value of table key . I want to make it 
> procedural to do that as mentioned below,But it's throwing an error .
> 
> 
> 
> DO $$
>   DECLARE
>   SEQ BIGINT;
>   BEGIN
> SEQ:=(SELECT MAX(ID) FROM TABLE_1);

select max(id) into seq from table_1;

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Brahmam Eswar schrieb am 22.11.2017 um 10:36:
> 
> we are in process of migrating to postgres and need to reset the
> sequence number with highest value of table key. I want to make it
> procedural to do that as mentioned below, But it's throwing an error

> DO $$
>  DECLARE
>  SEQ BIGINT;
>  BEGIN 
>  
>  SEQ:=(SELECT MAX(ID) FROM TABLE_1);
>  ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;
>   
>  END$$; 
>  
> Error : syntax error at or near "SEQ"

No need for a PL/pgSQL block.

You can do that with a plain SQL statement using setval():
  select setval('table_1_seq', (select max(id) from table1));