Monday, June 2, 2008

[GENERAL] sequences and currval()

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIRBfmKGqCc1vIvggRAhoTAJ9vwQ0rWP/zF8POuO/7LehDzdDYTACghR4I
9wDKOzHrkGA+dX7Mke4I7OM=
=3eUa
-----END PGP SIGNATURE-----
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

No comments: