Re: error: duplicate key - Mailing list 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.marlowe@gmail.com]
Sent: Wednesday, June 17, 2009 10:10 AM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] error: duplicate key

On Wed, Jun 17, 2009 at 10:51 AM, Marc Fromm<Marc.Fromm@wwu.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