Skip site navigation (1) Skip section navigation (2)

Re: error: duplicate key

From: Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: error: duplicate key
Date: 2009-06-17 17:48:40
Message-ID: B0D7C0A3F35FE144A70312D086CBCA9B038513D030@ExchMailbox2.univ.dir.wwu.edu (view raw or flat)
Thread:
Lists: pgsql-admin
>It looks like to me that the id was reset to 1
>select * from classification_guide_cat_id_seq;
cswe2=# select * from classification_guide_cat_id_seq;
          sequence_name          | last_value | increment_by | max_value  | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------------------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
 classification_guide_cat_id_seq |          1 |            1 | 2147483647 |         1 |           1 |      32 | f         | t

>pg_dump of table classification_guide_jobs
  2 -- PostgreSQL database dump
  3 --
  4 
  5 SET client_encoding = 'SQL_ASCII';
  6 SET check_function_bodies = false;
  7 SET client_min_messages = warning;
  8 
  9 SET search_path = public, pg_catalog;
 10 
 11 SET default_tablespace = '';
 12 
 13 SET default_with_oids = true;
 14 
 15 --
 16 -- Name: classification_guide_jobs; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
 17 --
 18 
 19 CREATE TABLE classification_guide_jobs (
 20     id integer DEFAULT nextval(('"classification_guide_job_id_seq"'::text)::regclass) NOT NULL,
 21     cat_id integer,
 22     job_title character varying(200),
 23     job_desc text,
 24     state_ws_elig boolean DEFAULT false
 25 );
 26 
 27 
 28 ALTER TABLE public.classification_guide_jobs OWNER TO postgres;
 29 
 30 --
 31 -- Name: classification_guide_jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
 32 --
 33 
 34 ALTER TABLE ONLY classification_guide_jobs
 35     ADD CONSTRAINT classification_guide_jobs_pkey PRIMARY KEY (id);
 36 
 37 
 38 --
 39 -- Name: RI_ConstraintTrigger_1455169; Type: TRIGGER; Schema: public; Owner: postgres
 40 --
 41 
 42 CREATE CONSTRAINT TRIGGER "<unnamed>"
 43     AFTER INSERT OR UPDATE ON classification_guide_jobs
 44     FROM classification_guide_cats
 45     NOT DEFERRABLE INITIALLY IMMEDIATE
 46     FOR EACH ROW
 47     EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'classification_guide_jobs', 'classification_guide_cats', 'UNSPECIFIED', 'cat_id', 'id');
 48 
 49 
 50 --
 51 -- Name: RI_ConstraintTrigger_1455190; Type: TRIGGER; Schema: public; Owner: postgres
 52 --
 53 
 54 CREATE CONSTRAINT TRIGGER "<unnamed>"
 55     AFTER INSERT OR UPDATE ON classification_guide_jobs
 56     FROM classification_guide_cats
 57     NOT DEFERRABLE INITIALLY IMMEDIATE
 58     FOR EACH ROW
 59     EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'classification_guide_jobs', 'classification_guide_cats', 'UNSPECIFIED', 'cat_id', 'id');
 60 
 61 
 62 --
 63 -- Name: classification_guide_jobs; Type: ACL; Schema: public; Owner: postgres
 64 --
 65 
 66 REVOKE ALL ON TABLE classification_guide_jobs FROM PUBLIC;
 67 REVOKE ALL ON TABLE classification_guide_jobs FROM postgres;
 68 GRANT ALL ON TABLE classification_guide_jobs TO postgres;
 69 
 70 
 71 --
 72 -- PostgreSQL database dump complete
 73 --


-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com] 
Sent: Wednesday, June 17, 2009 10:10 AM
To: Marc Fromm
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] error: duplicate key

On Wed, Jun 17, 2009 at 10:51 AM, Marc Fromm<Marc(dot)Fromm(at)wwu(dot)edu> wrote:
> I am receiving this error when I try to add new records:
>
> INSERT INTO "public"."classification_guide_cats" ("id", "cat_title") VALUES
> (nextval(('"classification_guide_cat_id_seq"'::text)::regclass), 'temp')
>
> SQL error:
>
> ERROR:  duplicate key violates unique constraint
> "classification_guide_cats_pkey"
>
> id is a primary key
> nextval(('"classification_guide_job_id_seq"'::text)::regclass)
>
> Could the primary key count have been reset so that it is starting at 1
> again? The primary key id is up to 128.

What does

select * from classification_guide_cat_id_seq;

say?  Also, what's the DDL for creating this table?  You can get it
with pg_dump -s -t tablename


In response to

pgsql-admin by date

Next:From: Anj AduDate: 2009-06-17 21:55:31
Subject: permissions on inherited tables
Previous:From: Scott MarloweDate: 2009-06-17 17:09:46
Subject: Re: error: duplicate key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group