Re: SERIAL type does not generate new ID ?

On Sat, 10 Jun 2000, Jean-Marc Libs wrote:

Hi all,

> I don't really understand what happens, so I put context, then problem:
> 
> 1/ Context
> ----------
> I have this table:
> 
> CREATE TABLE film (
> film_id SERIAL PRIMARY KEY,
snip
> )
> ;
> SELECT setval ('film_film_id_seq', 6);
> 
> 2/ Problem:
> 
> I have this query in PHP:
> insert into film
>
(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values('FR','','','','2000','f','f','f','f','f')
 
> 
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
> 
> 3/ Question:
> 
> Shouldn't it automagically create an appropriate film_id ?
> 
OK, I guess I figured out what happens: this serial word just creates
some sequence which acts as a counter of sort, which increments whenever
I insert stuff whithout specifying a value for film_id. Only, when
I insert with a specified film_id (as I did when I imported my 
legacy values), the sequence doesn't budge, so it is now out of sync
with the actual count of items in the database.

I'll just SELECT setval to max(film_id) and everything should be OK.

Thanks to those who answered me,

Jean-Marc Libs

-- 
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURG    http://www.actimage.net
Professionnel : jeanmarc@actimage.fr 
Lieu de travail : jml@obs.coe.int