Problems inserting data into a table with a sequence

Поиск
Список
Период
Сортировка
От RNG
Тема Problems inserting data into a table with a sequence
Дата
Msg-id 83f41ebd0710151458v42e8ac8h6f21c3d8a63251a9@mail.gmail.com
обсуждение исходный текст
Ответы Re: Problems inserting data into a table with a sequence  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Problems inserting data into a table with a sequence  (Greg Smith <gsmith@gregsmith.com>)
Список pgsql-general
Hi,

we have a table whose definition looks like this (using postgres 8.2.5
on Linux):

CREATE TABLE pn_categories_category (
    cat_id integer NOT NULL,
    cat_parent_id integer DEFAULT 1 NOT NULL,
    cat_is_locked smallint DEFAULT 0 NOT NULL,
    cat_is_leaf smallint DEFAULT 0 NOT NULL,
    cat_name character varying(255) DEFAULT ''::character varying NOT NULL,
    cat_sort_value integer DEFAULT 0 NOT NULL,
    cat_display_name text,
    cat_display_desc text,
    cat_path text,
    cat_ipath character varying(255) DEFAULT ''::character varying NOT NULL,
    cat_status character varying(1) DEFAULT 'A'::character varying NOT NULL,
    cat_obj_status character varying(1) DEFAULT 'A'::character varying NOT NULL,
    cat_cr_date timestamp without time zone DEFAULT '1970-01-01
00:00:00'::timestamp without time zone NOT NULL,
    cat_cr_uid integer DEFAULT 0 NOT NULL,
    cat_lu_date timestamp without time zone DEFAULT '1970-01-01
00:00:00'::timestamp without time zone NOT NULL,
    cat_lu_uid integer DEFAULT 0 NOT NULL
);

ALTER TABLE public.pn_categories_category OWNER TO postgres;
CREATE SEQUENCE pn_categories_category_cat_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER TABLE public.pn_categories_category_cat_id_seq OWNER TO postgres;
ALTER SEQUENCE pn_categories_category_cat_id_seq OWNED BY
pn_categories_category.cat_id;
ALTER TABLE pn_categories_category ALTER COLUMN cat_id SET DEFAULT
nextval('pn_categories_category_cat_id_seq'::regclass);


Trying to insert data into this table using the following SQL
INSERT INTO pn_categories_category
(cat_id,cat_parent_id,cat_name,cat_display_name,cat_display_desc,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES
(DEFAULT,'2','FAQ','a:1:{s:3:\"eng\";s:3:\"FAQ\";}','a:1:{s:3:\"eng\";s:26:\"Frequently
Asked Questions\";}','2007-10-15 23:47:59',0,'2007-10-15 23:47:59',0)

gives us the following error:
 ERROR:  duplicate key violates unique constraint "pn_categories_category_pkey"

From reading the docs, it would seem that using the 'DEFAULT' keyword
is correct when used this way. Even more puzzling, we have other
tables which are also created with an integer ID field mapped to a
sequence value where the same construct works just fine.

Can someone offer a tip as to what we're doing wrong here?

Greetings/Thanks
R

В списке pgsql-general по дате отправления:

Предыдущее
От: Lee Keel
Дата:
Сообщение: Re: Convert bytea to Float8
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Problems inserting data into a table with a sequence