Out of sync Primary Key Index

----- "Dave Coventry" <dgcoventry@gmail.com> wrote:

I have deleted a record from a table and am now getting errors:

INSERT INTO content_node_field (field_name, type, global_settings,
required, multiple, db_storage, module, db_columns, active, locked)
VALUES ('field_client_name', 'text',
'a:4:{s:15:"text_processing";s:0:"";s:10:"max_length";s:0:"";s:14:"allowed_values";s:0:"";s:18:"allowed_values_php";s:0:"";}',
0, 0, 1, 'text',
'a:1:{s:5:"value";a:5:{s:4:"type";s:4:"text";s:4:"size";s:3:"big";s:8:"not
null";b:0;s:8:"sortable";b:1;s:5:"views";b:1;}}', 1, 0);
ERROR: duplicate key value violates unique constraint
"content_node_field_pkey"

My table is as follows:

\d content_node_field;
Table "public.content_node_field"
Column | Type | Modifiers
-----------------+------------------------+----------------------------------------
field_name | character varying(32) | not null default
''::character varying
type | character varying(127) | not null default
''::character varying
global_settings | text | not null
required | smallint | not null default 0
multiple | smallint | not null default 0
db_storage | smallint | not null default 1
module | character varying(127) | not null default
''::character varying
db_columns | text | not null
active | smallint | not null default 0
locked | smallint | not null default 0
Indexes:
"content_node_field_pkey" PRIMARY KEY, btree (field_name)

From googling I need to reset my Primary Key, but

reindex index content_node_field_pkey;
REINDEX

Doesn't make any difference (no error is returned)

Nor does
reindex table content_node_field;
REINDEX

No what it is telling you is that the field_name ('field_client_name') is already in the table.

Adrian Klaver
aklaver@comcast.net

In response to Dave Coventry <dgcoventry@gmail.com>:

I have deleted a record from a table and am now getting errors:

INSERT INTO content_node_field (field_name, type, global_settings,
required, multiple, db_storage, module, db_columns, active, locked)
VALUES ('field_client_name', 'text',
'a:4:{s:15:"text_processing";s:0:"";s:10:"max_length";s:0:"";s:14:"allowed_values";s:0:"";s:18:"allowed_values_php";s:0:"";}',
0, 0, 1, 'text',
'a:1:{s:5:"value";a:5:{s:4:"type";s:4:"text";s:4:"size";s:3:"big";s:8:"not
null";b:0;s:8:"sortable";b:1;s:5:"views";b:1;}}', 1, 0);
ERROR: duplicate key value violates unique constraint "content_node_field_pkey"

What do you see if you do:
SELECT * FROM content_node_field WHERE field_name='field_client_name';

My table is as follows:

\d content_node_field;
Table "public.content_node_field"
Column | Type | Modifiers
-----------------+------------------------+----------------------------------------
field_name | character varying(32) | not null default
''::character varying
type | character varying(127) | not null default
''::character varying
global_settings | text | not null
required | smallint | not null default 0
multiple | smallint | not null default 0
db_storage | smallint | not null default 1
module | character varying(127) | not null default
''::character varying
db_columns | text | not null
active | smallint | not null default 0
locked | smallint | not null default 0
Indexes:
"content_node_field_pkey" PRIMARY KEY, btree (field_name)

From googling I need to reset my Primary Key, but

reindex index content_node_field_pkey;
REINDEX

Doesn't make any difference (no error is returned)

Nor does
reindex table content_node_field;
REINDEX

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/