Thread: postgresql 7.3.2 bug on date '1901-12-13' and '1901-12-13' - PK v iolation

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..

On Wed, 20 Aug 2003, Arnold Mavromatis wrote:

> 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

I couldn't duplicate on Redhat9 using 7.3.4 or 7.4 beta.

What does a plain select to_date('1901-12-13', 'YYYY-MM-DD')
and select to_date('1901-12-14', 'YYYY-MM-DD') give you?
And did you give --enable-integer-datetimes to configure, and
what compilation options were used on the files (specifically was
-O2 used or a higher level, was -ffast-math used, etc...)?