Auto Increase

Hello All,

I am building a database and I am in troubles to get a field
autonumbering itself.

I have:

% -- Begin -----------------------------------------------
CREATE SEQUENCE patr_seq;

CREATE TABLE patr_local (
local_cod INTEGER default nextval('patr_seq')
local_descr CHAR(30) not null
);
% -- End --------------------------------------------------

And:

% -- Begin ------------------------------------------------
CREATE TABLE patr_local (
local_cod SERIAL,
local_descr CHAR(30) UNIQUE not null
);
% -- End --------------------------------------------------

In both cases, it runs properly, the field local_cod is automatically
written; in both case happens:

=> insert into patr_local (local_descr) values ('local 1');
INSERT
=> insert into patr_local (local_descr) values ('local 2');
INSERT

=> select * from patr_local;
1 - local 1
2 - local 2

BUT, if I try to insert a wrong tupple (duplicating the value of the field
local_descr, that is unique), the counter is also added +1, so the table
become:

=> insert into patr_local (local_descr) values ('local 1');
INSERT
=> insert into patr_local (local_descr) values ('local 1');
ERROR: duplicated key local_descr
=> insert into patr_local (local_descr) values ('local 2');
INSERT

=> select * from patr_local;
1 - local 1
3 - local 2

As you can see, the #2 wasn't used. So, as many errors I get while
inserting tupples in the table will be number of 'numbers' not used.

How can I create a field that avoid this action? I would like the field
local_cod had the sequence:

1,2,3,4,5,6,7,8,9,10,...,N,...

...whatever happens while inserting tupples.

Thanks in advance,

Best regards,

Marcelo Pereira

-- Remember that only God and Esc+:w saves.
__
(_.\ Marcelo Pereira |
/ / ___ |
/ (_/ _ \__ Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/