Thread: Sequence - Use calculated start value?

I need to use a calculated start value for some sequences. eg:

CREATE SEQUENCE tblco_cntcoid_key start (SELECT (MAX(cntcoid) + 100)
FROM tblco) increment 1 maxvalue 2147483647 minvalue 1  cache 1;

when I try this I get:
   ERROR: parser: parse error at or near (

I am migrating an MS Access DB to postgresql and need to retain the
current value of the id columns (er cntcoid), but when I insert new
values I want the cntcoid to start at a number greater than the last one
in the table. When I use a normal sequence or serial type for the id,
any new records are inserted starting at id 1.

Anyone know how to do this?

Thanks

--
Ron
Syscor R&D

Aragorn wrote:

> I need to use a calculated start value for some sequences. eg:
>
> CREATE SEQUENCE tblco_cntcoid_key start (SELECT (MAX(cntcoid) + 100)
> FROM tblco) increment 1 maxvalue 2147483647 minvalue 1  cache 1;
>
> when I try this I get:
>   ERROR: parser: parse error at or near (
>
> I am migrating an MS Access DB to postgresql and need to retain the
> current value of the id columns (er cntcoid), but when I insert new
> values I want the cntcoid to start at a number greater than the last
> one in the table. When I use a normal sequence or serial type for the
> id, any new records are inserted starting at id 1.
>
> Anyone know how to do this?
>
> Thanks
>


--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com

On Wed, Oct 30, 2002 at 18:02:00 +0000,
  Aragorn <aragorn@gondor.com> wrote:
> I need to use a calculated start value for some sequences. eg:
>
> CREATE SEQUENCE tblco_cntcoid_key start (SELECT (MAX(cntcoid) + 100)
> FROM tblco) increment 1 maxvalue 2147483647 minvalue 1  cache 1;

I think you need to do this in two steps. First create the sequence
and then use setval to set the starting value. I suspect that the
start value is not allowed to use select. The documention doesn't
make this absolutely clear, but I would be surprised if you could use
one here.