The following bug has been logged online:

Bug reference: 2306
Logged by: Andreas Jung
Email address: lists (AT) andreas-jung (DOT) com
PostgreSQL version: 7.4.9
Operating system: Linux
Description: Duplicate primary key
Details:

Ihave the following table (with 'id' as primary key:

Toolbox2Test=# \d hierarchy

Table "public.hierarchy"

Column | Type |
Modifiers

-------------------------+-----------------------------+----------------

-------------------------+-----------------------------+--------------------
-----------------------

id | integer | not null default
nextval('public.hierarchy_id_seq'::text)

parentid | bigint |

idprodukt | bigint |

bezeichnung | character varying(160) |

neudat | timestamp without time zone |

aedat | timestamp without time zone |

benutzer | character varying(32) |

pos | integer | default 0

linkindex | character varying(20) |

deleted | boolean | default false

visible | boolean |

sorting | boolean |

comment | character varying(265) |

idhierarchy_share | integer |

show_gattung_in_bauplan | boolean | default false

sortierung | character varying(10) |

Indexes:

"hierarchy_pkey" PRIMARY KEY, btree (id)

"hierarchy_deleted_idx" btree (deleted)

"hierarchy_idhierarchy_share_idx" btree (idhierarchy_share)

"hierarchy_idprodukt_idx" btree (idprodukt)



This gives me two rows with the same id=5077:

Toolbox2Test=# select * from hierarchy where id >= 5077 order by id;

id | parentid | idprodukt | bezeichnung
show_gattung_in_bauplan

-------+----------+-----------+---------------------------------------------
--------------------------+----------------------------+--------------------
---

-----+------------------+-----+-----------+---------+---------+---------+---
---------------------------------+-------------------+----------------------
---

+------------

5077 | 4062 | | Präsentieren

969 | RossmyU | 1 | LI353323 | f | | | CSV
import from test_tools.csv | | f


5077 | 4062 | | Präsentation

414 | RossmyU | 0 | LI353323 | t | | | CSV
import from test_tools.csv | | t


5078 | 4062 | | Rechner

7969 | RossmyU | 2 | LI353324 | f

Search for all rows with id=5077 returns this:

Toolbox2Test=# select * from hierarchy where id = 5077;



id | parentid | idprodukt | bezeichnung | neudat | aedat
sortierung

------+----------+-----------+--------------+--------+----------------------
------+----------+-----+-----------+---------+---------+---------+----------
----------------------+-------------------+-------------------------+-------
-----

5077 | 4062 | | Präsentieren | | 2005-11-23
12:03:38.617969 | RossmyU | 1 | LI353323 | f | | |
CSV import from test_tools.csv | | f
(1 row)


Any idea how to resolve this issue?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Mar 07, 2006 at 04:43:18PM +0000, Andreas Jung wrote:
7.4.12 is the latest in that branch; it contains several bug fixes
since 7.4.9.

What's the output of the following command?

SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077 ORDER BY id;

Does the same query return different results depending on whether
you use an index scan or a sequential scan? What do you get for
these queries?

SET enable_seqscan TO on;
SET enable_indexscan TO off;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077;

SET enable_seqscan TO off;
SET enable_indexscan TO on;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077;

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match