Site Navigation
|
|
PGTS Humble BlogThread: Tips/Tricks For Programming etc |
|
 |
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 +1100Since 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;