Hi
Your name : Arnold Mavromatis
Your email address : A.Mavromatis@bom.gov.au
System Configuration
---------------------
PostgreSQL info
Server version PostgreSQL 7.3.2 on hppa2.0w-hp-hpux11.11
Engine Version 7.3.2
Compiled by GCC 3.2.1
Please enter a FULL description of your problem:
------------------------------------------------
In postgresql 7.3.2 trying to insert a specific day
As
db1=> insert into sfc_days2 values (86071, to_date('1901-12-13',
'YYYY-MM-DD'), 3.0);
INSERT 1591881 1
db1=> insert into sfc_days2 values (86071, to_date('1901-12-14',
'YYYY-MM-DD'), 3.0);
ERROR: Cannot insert a duplicate key into unique index sfc_days2_pkey
db1=>
gives a Primary Key Violation even though the day in question is unique and
not currently loaded in the table..
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
Create sample table
CREATE TABLE "public"."sfc_days2"
(
"stn_num" numeric(6,0) NOT NULL,
"lsd" date NOT NULL,
"max_air_temp" numeric(7,1),
CONSTRAINT "sfc_days2_pkey" PRIMARY KEY ("stn_num", "lsd"),
CONSTRAINT "sfc_days_max_air_ck" CHECK ((((max_air_temp IS NOT
NULL) AND ((max_air_temp >= -70.0) AND (max_air_temp <= 60.0))) OR
(max_air_temp IS NULL)))
);
Table info "public.sfc_days2"
Column | Type | Modifiers
-----------------------+-----------------------+--------------
stn_num | numeric(6,0) | not null
lsd | date | not
null
max_air_temp | numeric(7,1) |
Indexes: sfc_days2_pkey primary key btree (stn_num, lsd)
Check constraints: "sfc_days_max_air_ck" (((max_air_temp IS NOT NULL) AND
((max_air_temp >= -70.0) AND (max_air_temp <= 60.0))) OR (max_air_temp IS
NULL))
Sample insert data log
db1=> delete from sfc_days2;
DELETE 1
db1=> commit;
WARNING: COMMIT: no transaction in progress
COMMIT
db1=> select count(*) from sfc_days2;
count
-------
0
(1 row)
db1=> insert into sfc_days2 values (86071, to_date('1901-12-13',
'YYYY-MM-DD'), 3.0);
INSERT 1591881 1
db1=> insert into sfc_days2 values (86071, to_date('1901-12-14',
'YYYY-MM-DD'), 3.0);
ERROR: Cannot insert a duplicate key into unique index sfc_days2_pkey
db1=>
Any help would be greatly appreciated as this bug is stopping us from
importing data that spans more than 200 years from 1800..
That we wish to use for an internet application using servlets..
