Hello All,

I am new to PostgreSQL. For testing purposes I am copying data from an
Oracle database to a PostgreSQL database via Access.
When I encountered an insert problem in Access, I tried it directly in
PostgreSQL and here is the result.

I am very confused. Can anyone give me a hint?

Thanks,
Claire

P.S. I have encountered the problem with the following dates:
1998-04-05 02:00:00, 2000-04-02 02:00:00 and 2002-04-07 02:00:00 (it's a
bit fishy, isn't it)
______________________________________________________________________________

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

RsqaDb=# select * from hre_gaz
RsqaDb-# where temps between '1998-04-05 02:00:00' and '1998-04-05 02:59:59'
RsqaDb-# ;
poste | gaz | temps | valeur | qual | type
-------+-----+-------+--------+------+------
(0 rows)

RsqaDb=# select * from hre_gaz where temps
RsqaDb-# between '1998-04-05 02:00:00'::timestamp
and '1998-04-05 02:59:59'::timestamp ;
poste | gaz | temps | valeur | qual | type
-------+-----+---------------------+--------+------+------
3 | 1 | 1998-04-05 01:00:00 | 54 | 1 | S
3 | 3 | 1998-04-05 01:00:00 | 10 | 4 | S
3 | 4 | 1998-04-05 01:00:00 | 4 | 1 | S
3 | 8 | 1998-04-05 01:00:00 | 39 | 12 | S
3 | 9 | 1998-04-05 01:00:00 | 35 | 4 | S
3 | 10 | 1998-04-05 01:00:00 | 6 | | S
28 | 1 | 1998-04-05 01:00:00 | 8 | 1 | S
28 | 4 | 1998-04-05 01:00:00 | 5 | 2 | S
28 | 5 | 1998-04-05 01:00:00 | 13 | 18 | S
28 | 8 | 1998-04-05 01:00:00 | 6 | 2 | S
28 | 9 | 1998-04-05 01:00:00 | 62 | 7 | S
28 | 10 | 1998-04-05 01:00:00 | 47 | | S
61 | 1 | 1998-04-05 01:00:00 | 5 | 2 | S
61 | 4 | 1998-04-05 01:00:00 | 12 | 4 | S
61 | 8 | 1998-04-05 01:00:00 | 15 | 4 | S
61 | 9 | 1998-04-05 01:00:00 | 47 | 5 | S
61 | 10 | 1998-04-05 01:00:00 | 47 | | S
99 | 6 | 1998-04-05 01:00:00 | 2 | 8 | S
99 | 8 | 1998-04-05 01:00:00 | 29 | 9 | S
99 | 9 | 1998-04-05 01:00:00 | 18 | 2 | S
99 | 10 | 1998-04-05 01:00:00 | 1 | | S
(21 rows)

______________________________________________________________________________

Search Discussions

  • Tom Lane at Feb 4, 2003 at 11:09 pm

    Claire De Longchamp 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
  • Claire De Longchamp at Feb 5, 2003 at 6:39 pm
    Thank you very much Tom. Your remark did clarify the question.
    ___________________________________________________________________
    Tom Lane a écrit:
    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

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 4, '03 at 7:19p
activeFeb 5, '03 at 6:39p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Claire De Longchamp: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase