Using Sequences to Number Rows

Configuring a sequence to uniquely number rows involves several steps:

1.

Create the sequence.

2.

Create the table, defining nextval() as the column default. 

3.

During the INSERT, do not supply a value for the sequenced column, or use nextval().

Figure  shows the use of a sequence for unique row numbering in the customer table.  

        test=> CREATE SEQUENCE customer_seq;
        CREATE
        test=> CREATE TABLE customer (
        test(>              customer_id INTEGER DEFAULT nextval('customer_seq'),
        test(>              name CHAR(30)
        test(> );
        CREATE
        test=> INSERT INTO customer VALUES (nextval('customer_seq'), 'Bread Makers');
        INSERT 19004 1
        test=> INSERT INTO customer (name) VALUES ('Wax Carvers');
        INSERT 19005 1
        test=> INSERT INTO customer (name) VALUES ('Pipe Fitters');
        INSERT 19008 1
        test=> SELECT * FROM customer;
         customer_id |              name              
        -------------+--------------------------------
                   1 | Bread Makers                  
                   2 | Wax Carvers                  
                   3 | Pipe Fitters                     
        (3 rows)  

The first statement creates a sequence counter named customer_seq. The second command creates the customer table, and defines nextval('customer_seq') as the default  for the customer_id column. The first INSERT manually supplies the sequence value for the column. The nextval('customer_seq') function call will return the next available sequence number, and increment the sequence counter. The second and third INSERTs allow the nextval('customer_seq') DEFAULT  to be used for the customer_id column. Remember, a column's DEFAULT  value is used only when a value is not supplied by an INSERT statement. (This is covered in Section .) The SELECT shows that the customer rows have been sequentially numbered.