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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-admin by date

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