Your Linux Data Center Experts

Recently, we had the need to be able to allocate an incrementing number from a shell script. Of course, we could just save the number in a file, read it and increment it from the script. However, the script may have multiple instances running at once and we would need to do locking, to ensure that multiple instances don't get the same number. Here's what I decided to do…

On this system we have PostgreSQL running, and PostgreSQL includes a sequence type. This guarantees to provide each call with a unique, incrementing number. This is how auto-increment fields are implemented in PostgreSQL.

First you need to create a database, then create a sequence within it. Then you can run a “psql” command to get the next value:

guin:~$ createdb seq
CREATE DATABASE
guin:~$ psql -d seq -c 'CREATE SEQUENCE nextval START 400000'
CREATE SEQUENCE
guin:~$ psql -d seq -t -c "select nextval('nextval');" | head -1
  400000
guin:~$ psql -d seq -t -c "select nextval('nextval');" | head -1
  400001
guin:~$ psql -d seq -t -c "select nextval('nextval');" | head -1
  400002

I love PostgreSQL.

comments powered by Disqus

Join our other satisfied clients. Contact us today.