From: | "Jean-Marc Libs" <jean-marc(dot)libs(at)obs(dot)coe(dot)int> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SERIAL type does not generate new ID ? |
Date: | 2000-06-10 17:28:22 |
Message-ID: | Pine.LNX.4.21.0006101911550.29725-100000@centaure.obs.coe.int |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_id4 bpchar,
film_ec_certif bool,
film_ce_certif bool,
film_prod_year int4,
film_eur_support bool,
film_media_support bool,
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(at)actimage(dot)fr
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2000-06-11 11:45:51 | Re: Client Logging |
Previous Message | Daniel Fairs | 2000-06-10 12:43:29 | Re: postmaster: init.d/start |