The 'SEQ' part can't be substituted by variable.
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@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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.
