I did have this problem in the past. I have a table with three fields set for the primary key. Somehow I would get dups in that table. I'm not sure how as I am too busy to test it. I changed my insert queries to check for existing data before the insert so I could get back to work.
Someone did reply that it was from a bug in vacuum. I run vacuum each night on my database. I'm running: PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4 from Debian packages On: Linux 2.4.17 #1 SMP Sat Dec 22 09:18:33 PST 2001 i686 unknown Debian Sid (unstable) and Woody (testing). Hope this helps. |On 020114 13:01 |steve boyle ([EMAIL PROTECTED]) wrote the following... | |Tony / Phil, | |my 2c on the Primary Key definition / usage. If you have any SQL / DDL code |that disagrees with the definitions below could you please repost them. | |"Tony Reina" <[EMAIL PROTECTED]> wrote in message |[EMAIL PROTECTED]">news:[EMAIL PROTECTED]... |> [EMAIL PROTECTED] (Phill Kenoyer) wrote in message |news:<[EMAIL PROTECTED]>... |> > Here is a good one. I have three fields set for my primary key. Now I |> > thought that a primary key was unique, and dups can not be inserted. |> > |> |> I don't think primary keys per se are unique, but rather can be made |> unique by specifying that option. IIRC primary keys just allow you to |> index searches within the database. So to make a unique primary key |> from your db schema: |> | |The definition of a primary key was that it WAS a unique identifier for the |table | |Definition: The primary key of a relational table uniquely identifies each |record in the table. It can either be a normal attribute that is guaranteed |to be unique (such as Social Security Number in a table with no more than |one record per person) or it can be generated by the DBMS (such as a |globally unique identifier, or GUID, in Microsoft SQL Server). | |Ref: http://databases.about.com/library/glossary/bldef-primarykey.htm | |Also | |PRIMARY KEY | |This column is a primary key, which implies that uniqueness is enforced by |the system and that other tables may rely on this column as a unique |identifier for rows. See PRIMARY KEY for more information. | |AND | |The PRIMARY KEY column constraint specifies that a column of a table may |contain only unique (non-duplicate), non-NULL values. The definition of the |specified column does not have to include an explicit NOT NULL constraint to |be included in a PRIMARY KEY constraint. | |Ref: | |http://www.ninthwonder.com/info/postgres/user/sql-createtable.htm | |Regards | |sb | |> |> CREATE TABLE "inventory" ( |> "stock" character varying(50) NOT NULL, |> "inventory_type" character varying(20) DEFAULT 'unknown' NOT |> NULL, |> "client_id" integer NOT NULL, |> [...] |> UNIQUE ("stock", "inventory_type", "client_id") |> Constraint "inventory_pkey" |> Primary Key ("stock", "inventory_type", "client_id") |> ); |> |> HTH, |> -Tony |> |> |> BTW, I'm using Google newsgroups to view the Postgres messages, but |> haven't seen posted messages in several days on the Hackers list. Does |> anyone know if this is a Google glitch or is the hackers list just not |> very active during the holiday? -- BOFH Reason of the day: Maintence window broken _ | _ (_()(|('.|)('||.|()|`|( ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org