Thread: sequences and currval()

Hello,

I'm migrating a db schema in an automated fashion, using this

UPDATE clients_client
     SET icp_id = null
     WHERE icp_id = 1;
UPDATE icps_icp
     SET id = nextval('public.icps_icp_id_seq')
     WHERE id = 1;
UPDATE clients_client
     SET icp_id = currval('public.icps_icp_id_seq')
     WHERE icp_id = null;

So essentially, clients have an icp_id that is set to null if it was 1,
and then the icps_icp table is updated to move icp 1 to whatever is next
in the sequence.

I then want to adjust the clients such that they reference where the icp
was moved to using currval() on the sequence. But, this part doesn't
seem to be working. The clients continue to have an icp_id of null.

I've noticed this on a fresh pgsql session.

tugdb=# select currval('icps_icp_id_seq');
ERROR:  currval of sequence "icps_icp_id_seq" is not yet defined in this
session

I don't understand this, as I only want the current value of the
sequence. I suppose I can get it this way

tugdb=# select last_value from icps_icp_id_seq;
  last_value
------------
           2
(1 row)

but I'd like to understand why currval() doesn't work.

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It
takes a touch of genius - and a lot of courage to move in the opposite
direction." --Albert Einstein

On Mon, Jun 02, 2008 at 11:55:14AM -0400, Michael P. Soulier wrote:
> Hello,
>
> I'm migrating a db schema in an automated fashion, using this
>
> UPDATE clients_client
>     SET icp_id = null
>     WHERE icp_id = 1;
> UPDATE icps_icp
>     SET id = nextval('public.icps_icp_id_seq')
>     WHERE id = 1;
> UPDATE clients_client
>     SET icp_id = currval('public.icps_icp_id_seq')
>     WHERE icp_id = null;

Your problem is that this should be: WHERE icp_id IS NULL.

> I've noticed this on a fresh pgsql session.
>
> tugdb=# select currval('icps_icp_id_seq');
> ERROR:  currval of sequence "icps_icp_id_seq" is not yet defined in this
> session
>
> I don't understand this, as I only want the current value of the
> sequence. I suppose I can get it this way

currval() returns the value last returned by nextval() *in this
session*. If you havn't called it in this session ofcourse it won't
work. This is for transaction safety.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.