PGTS PGTS Pty. Ltd.   ACN: 007 008 568

point Site Navigation

point Other Blog Threads



  Valid HTML 4.01 Transitional

   Give Windows The Boot!
   And Say Goodbye To Viruses!

   Ubuntu

   If you own a netbook/laptop~
   Download Ubuntu Netbook!






PGTS Humble Blog

Thread: Tips/Tricks For Programming etc

Author Image Gerry Patterson. The world's most humble blogger
Edited and endorsed by PGTS, Home of the world's most humble blogger

Using Postgres Sequences


Chronogical Blog Entries:



Date: Tue, 14 Feb 2012 23:49:57 +1100

Since I don't often manipulate sequence in postgres, whenever the need arises, I find myself re-reading the postgres documentation about currval and setval.

The following script illustrates the use of setval, currval and nextval. If you want to run it, iyou can use copy and paste it into your psql command line.

/*

 The following has been copied from the Postgres documentation

 nextval
    Advance the sequence object to its next value and return that value.
    This is done atomically: even if multiple sessions execute nextval
    concurrently, each will safely receive a distinct sequence value.

 currval
    Return the value most recently obtained by nextval for this sequence
    in the current session. (An error is reported if nextval has never
    been called for this sequence in this session.) Notice that because
    this is returning a session-local value, it gives a predictable
    answer even if other sessions are executing nextval meanwhile.

 setval
    Reset the sequence object's counter value. The two-parameter form
    sets the sequence's last_value field to the specified value and sets
    its is_called field to true, meaning that the next nextval will
    advance the sequence before returning a value. In the
    three-parameter form, is_called may be set either true or false. If
    it's set to false, the next nextval will return exactly the
    specified value, and sequence advancement commences with the
    following nextval. For example,

 The code below illustrates the use of the true/false flag in setval()

*/

create SEQUENCE temp_seq;

/* NOTE: This is equivalent to:
select setval('temp_seq', 210, true); */
select setval('temp_seq', 210);
/*
 setval 
--------
    210
(1 row) */

select currval('temp_seq');
/*
 currval 
---------
     210
(1 row) */

select nextval('temp_seq');
/*
 nextval 
---------
     211
(1 row) */

select currval('temp_seq');
/*
 currval 
---------
     211
(1 row) */

select setval('temp_seq', 999, false);
/*
 setval 
--------
    999
(1 row) */

select currval('temp_seq');
/*
 currval 
---------
     211
(1 row) */

select nextval('temp_seq');
/*
 nextval 
---------
     999
(1 row) */

select currval('temp_seq');
/*
 currval 
---------
     999
(1 row) */

drop SEQUENCE temp_seq;

Other Blog Posts In This Thread:

Copyright     2012, Gerry Patterson. All Rights Reserved.