Claire De Longchamp <cdl@abdmf.com> writes:
> Table "hre_gaz"
> Column | Type | Modifiers
> --------+--------------------------------+-----------
> poste | smallint | not null
> gaz | smallint | not null
> temps | timestamp(0) without time zone | not null
> valeur | integer |
> qual | integer |
> type | character(1) |
> Primary key: hre_gaz_pkey
> Triggers: RI_ConstraintTrigger_462203,
> RI_ConstraintTrigger_462209
> RsqaDb=# insert into hre_gaz (poste,gaz,temps,valeur,qual,type)
> RsqaDb-# values (3,1,'1998-04-05 02:00:00'::timestamp,6,1,'S') ;
> ERROR: Cannot insert a duplicate key into unique index hre_gaz_pkey
What PG version is this?
If it's 7.2, then I think you're getting burnt by the fact that
::timestamp means coercion to timestamp *with* time zone. When that
value is then coerced to timestamp without time zone, you can get funny
behavior right around the times of daylight-savings transitions.
In particular, I don't think '1998-04-05 02:00:00' is actually a valid
time, at least under US DST rules --- it comes out as '03:00:00' for me.
Either leave off the explicit coercion, or coerce to "timestamp without
time zone", or update to 7.3 in which "timestamp" means "timestamp without
time zone".
regards, tom lane