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(at)actimage(dot)fr
Lieu de travail : jml(at)obs(dot)coe(dot)int