[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 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

The 'SEQ' part can't be substituted by variable.
https://www.postgresql.org/docs/10/static/plpgsql-implementation.html

DO $$
DECLARE
SEQ BIGINT;
BEGIN
SEQ := (SELECT MAX(ID) FROM TABLE_1);
EXECUTE format('ALTER SEQUENCE TABLE_1_SEQ RESTART WITH %s', SEQ);
END
$$;

On Wed, Nov 22, 2017 at 6:26 PM, amul sul <sulamul@gmail.com> wrote:

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