Beginning SQL sequences using PostgreSQL and Oracle

[jeffro]

Basic sequences in SQL

My first exposure to a database system was MySQL. That little database was fast and fun, but it ended up creating a couple of bad habits that caused me some grief when I got a job and had to start connecting to Oracle and PostgreSQL databases. Rather than bore you with a bloated story about my ignorance of databases and structure I thought I would just start by explaining the one of the things that found to be foreign when I started using Oracle and PostgreSQL, sequences. This tutorial is merely an introduction to sequences in PostgreSQL and Oracle, it is by no means a complete reference.

In MySQL there is a nice little function that increments a field for you if you pass it a null value, auto_increment. While auto_increment was very convenient at first it quickly proved to be a bit limiting. For starters auto_increment columns will give the next available number upon insert, thats it, you don't have any control over where the increment starts or how it counts. So if you are trying to enforce some sort of referential intrgrity into your application, your stuck. When you move to other databases you will be hardpressed to find an auto_increment function, in fact, you most likely won't. So how do you create a field that had a unique and incremented value? You would use a sequence. Essentially, sequences are little programs that run when you ask them to and they return a number. That maybe an oversimplification but that is how I view them.

Lets look at how we create a very basic sequence:

create my_blog_seq start 1 increment 1 maxvalue 9999999;

Simple right? What this SQL statment just did was create a sequence called 'my_blog_seq' that starts counting at 1 and adds 1 to the the current sequence number every time it is called. The sequence number can't go over 9999999 because I set that as the maximum value, any attempts by the sequence to count over that number will return an error.

[ PostgreSQL ]
If you want to see the sequence you just created and the other options you can use in creating sequences use this SQL:

SELECT * FROM my_blog_seq;

[ Oracle ]
Oracle keeps all its sequences in one place so the above SQL wouldn't work when you are using a Oracle database, rather you would have to use the following SQL statement to view your sequence and the different options:

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'my_blog_seq';

You should get a listing of the sequence resembling the following fields (note: the following is from a PostgreSQL db)

sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called

Ok, great. Now we know how to create sequences, and select them, but how do we use them in an insert? Like most things, I am only going to scratch the surface on how to use sequences in an insert so if anyone has a question on a more advanced usage, just post it to the thread.

The following SQL statements show how using a sequence can differ depending on the database you are using.

[ PostgreSQL ]
INSERT INTO my_blog (id,comment) VALUES (NEXTVAL('my_blog_seq'),'my comment');

[ Oracle ]
INSERT INTOmy_blog (id,comment) VALUES (my_blog_seq.NEXTVAL,'my comment');

The NEXTVAL() function just increments the current value of the sequence by what every you specified.


There are different techniques for using sequences but I just wanted to scratch the surface. Let me know what you think!

jeffro

[Sir R4T IV]

This tutorial was very helpful. Thank you very much, jeffro.
but i'm sorry to say i'm still a novice in this area. i'd just like to know if like NEXTVAL, there are other functions or not.

My problem is, once i used NEXTVAL, what do i do to set the pointer back to the first values in the sequence? something like start value and endvalue must be there. If you could tell me the syntaxes, i'd be very grateful.