Re: [GENERAL] Reset Sequence number

[Wèi Cōngruì]

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

Re: [GENERAL] Reset Sequence number

[Raymond O'Donnell]

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
r...@iol.ie

Re: [GENERAL] Reset Sequence number

[amul sul]

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

[GENERAL] Reset Sequence number

[Brahmam Eswar]

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.