Using sequences are common practice in order to have auto numbering in a PostgreSQL database. The easiest way to create an auto numbered identity field is to use the SERIAL data type macro.
Here is an example:
CREATE TABLE customer
(
customer_id SERIAL NOT NULL PRIMARY KEY,
name varchar(50)
);
When running this script, PostgreSQL automatically will create a SEQUENCE called customer_id_seq and assign the default value of the customer_id field to the next available value from the sequence. This is done by calling nextval(’customer_id_seq’::regclass).
Upon inserting new records in the customer table, the sequence will work just fine as long as you leave the customer_id field alone. In this the sequence will be incremented twice, generating numbers 1 and 2.
INSERT INTO customer (name) values('Customer 1');
INSERT INTO customer (name) values('Customer 2');
But what will happen to the next value on our sequence when we explicitly present data to the customer_id field? For example when we are loading data from an external source.
INSERT INTO customer (customer_id,name) values(3,'Customer 3');
INSERT INTO customer (customer_id,name) values(4,'Customer 4');
INSERT INTO customer (customer_id,name) values(5,'Customer 5');
As expected the value of customer_id_seq will not increment as we insert data with the example above. This is because nextval(‘customer_id_seq’) will only be triggered when customer_id is NULL.
As expected the value of customer_id_seq will not increment as we explicitly insert data with the example above. It is because nextval(‘customer_id_seq’) will only be triggered when customer_id is NULL. This means when you try to insert another customer record without explicitly providing a unique customer_id, the nextval(‘customer_id_seq’) will incremented to 3 which is already used, resulting a nice duplicate key exception:
INSERT INTO customer (name) values('Customer 6');
----------------------------------------------------------------------
ERROR: duplicate key value violates unique constraint "customer_pkey"
********** Error **********
ERROR: duplicate key value violates unique constraint "customer_pkey"
SQL state: 23505
This can easily break your application unexpectedly. In In order to solve this problem we have to synchronize the value of customer_id_seq with the latest value existing in the customer_id column: The following function provides just the functionality needed. Just provide the table name and the the name of the identity column which the sequence is bound to.
CREATE OR REPLACE FUNCTION sync_sequence(
table_name VARCHAR,idfield VARCHAR)
RETURNS VOID AS
$$
DECLARE
max_id BIGINT;
BEGIN
-- retriving max id from table_name
EXECUTE 'select max(' || idfield || ') from '
|| table_name into max_id;
-- resetting the default sequence on table_name
EXECUTE 'alter sequence ' || table_name || '_' ||
idfield || '_seq restart with ' || (max_id+1);
END;
$$
LANGUAGE PLPGSQL;
SELECT sync_sequence('customer','customer_id');
Have fun with PostgreSQL 
admin :: Jun.18.2008 ::
PostgreSQL ::
No Comments »
