SERIAL type does not generate new ID ? - Mailing list pgsql-sql

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,
film_country_id CHAR(2),
film_country_id2 CHAR(2),
film_country_id3 CHAR(2),
film_country_id4 CHAR(2),
film_ec_certif BOOL DEFAULT 'false',
film_ce_certif BOOL DEFAULT 'false',
film_prod_year INTEGER,
film_eur_support BOOL DEFAULT 'false',
film_media_support BOOL DEFAULT 'false',
film_budgetnat DECIMAL,
film_budgetnat_rate_id CHAR(3),
film_budget DECIMAL,
film_provisoire BOOL DEFAULT 'false',
film_production_id INTEGER,
film_production_id2 INTEGER,
film_production_id3 INTEGER,
film_production_id4 INTEGER
);

CREATE UNIQUE INDEX film_uidx ON film ( film_id );
CREATE INDEX film_production_1 ON film (film_production_id);
CREATE INDEX film_production_2 ON film (film_production_id2);
CREATE INDEX film_production_3 ON film (film_production_id3);
CREATE INDEX film_production_4 ON film (film_production_id4);

Now if I look at it with postgresadmin, I see:
-- postgresAdmin PostgreSQL-Dump
--
-- Serveur: localhost:5432 Base de données: admissions12
---------------------------------------------------------

--
-- Structure de la table 'film'
--

DROP SEQUENCE film_film_id_seq;
CREATE TABLE film (  film_id serial,  film_country_id bpchar,  film_country_id2 bpchar,  film_country_id3 bpchar,
film_country_id4bpchar,  film_ec_certif bool,  film_ce_certif bool,  film_prod_year int4,  film_eur_support bool,
film_media_supportbool,  film_budgetnat numeric,  film_budgetnat_rate_id bpchar,  film_budget numeric,  film_provisoire
bool, film_production_id int4,  film_production_id2 int4,  film_production_id3 int4,  film_production_id4 int4
 
)
;
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 ?

Right now, I can do it with  $sql_query_film="insert into film
(film_id,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
(MAX(film_id)+1,'$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')";

and it works fine as long as there are no concurrent accesses, but
there must be a better way :-(

This is very puzzling because it seems that this is the only table which 
gives such errors.

Pointers on relevant online documentation accepted, too :-)
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