Thread: alter sequence

alter sequence

From
Peter Kroon
Date:
ALTER SEQUENCE (select pg_get_serial_sequence('table', 'id')) RESTART WITH 1;

The query fails:
ALTER SEQUENCE (select pg_get_serial_sequence('table...
it's because of the (

I guess I'm using the wrong syntax.
It is possible this way? Would be great!

Best,
Peter 

Re: alter sequence

From
Pavel Stehule
Date:
Hello

you can't to do it directly. Expression cannot be used instead object name

you can use a plpgsql DO in this moment

DO $$
BEGIN
  EXECUTE format('ALTER SEQUENCE %I RESTART WITH 1',
pg_get_serial_sequence('table', 'id'));
END;
$$ LANGUAGE plpgsql;

Regards

Pavel Stehule

2012/11/24 Peter Kroon <plakroon@gmail.com>:
> ALTER SEQUENCE (select pg_get_serial_sequence('table', 'id')) RESTART WITH
> 1;


Re: alter sequence

From
Jasen Betts
Date:
On 2012-11-24, Peter Kroon <plakroon@gmail.com> wrote:
> --f46d04389321c8f47d04cf3c0f32
> Content-Type: text/plain; charset=ISO-8859-1
>
> ALTER SEQUENCE (select pg_get_serial_sequence('table', 'id')) RESTART WITH
> 1;

> The query fails:
> ALTER SEQUENCE (select pg_get_serial_sequence('table...
> it's because of the (

you can end-run this problem by using the (undocumented ?) function setval:

select pg_catalog.setval(pg_get_serial_sequence('table','id') ,1,false);

--
⚂⚃ 100% natural

Re: alter sequence

From
Guillaume Lelarge
Date:
On Sun, 2012-12-02 at 11:42 +0000, Jasen Betts wrote:
> On 2012-11-24, Peter Kroon <plakroon@gmail.com> wrote:
> > --f46d04389321c8f47d04cf3c0f32
> > Content-Type: text/plain; charset=ISO-8859-1
> >
> > ALTER SEQUENCE (select pg_get_serial_sequence('table', 'id')) RESTART WITH
> > 1;
>
> > The query fails:
> > ALTER SEQUENCE (select pg_get_serial_sequence('table...
> > it's because of the (
>
> you can end-run this problem by using the (undocumented ?) function setval:
>
> select pg_catalog.setval(pg_get_serial_sequence('table','id') ,1,false);
>

It's documented:
http://www.postgresql.org/docs/9.2/interactive/functions-sequence.html
for setval and
http://www.postgresql.org/docs/9.2/static/functions-info.html for
pg_get_serial_sequence.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com