[GENERAL] ERROR: relation xxx already exists but where????

[Joost Kraaijeveld]

I managed to drop a table without apparently droppig it's primary key
After recreating the table I try to recreate the primary key.

If I run the following:

ALTER TABLE case_histories
  ADD CONSTRAINT case_histories_pkey PRIMARY KEY(case_history_id);

Postgresql responds with:

NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
case_histories_pkey for table case_histories

ERROR: relation case_histories_pkey already exists
SQL state: 42P07

The table does not have this key. Assuming that the response is correct, where 
can I find and/or delete this relation? I have deleted an entry inpg_constraint 
with that name but that did not do the trick

TIA

Joost

Joost


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] ERROR: relation xxx already exists but where????

[Scott Marlowe]

On 8/20/07, Joost Kraaijeveld [EMAIL PROTECTED] wrote:
 I managed to drop a table without apparently droppig it's primary key
 After recreating the table I try to recreate the primary key.

 If I run the following:

 ALTER TABLE case_histories
   ADD CONSTRAINT case_histories_pkey PRIMARY KEY(case_history_id);

 Postgresql responds with:

 NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
 case_histories_pkey for table case_histories

 ERROR: relation case_histories_pkey already exists
 SQL state: 42P07

 The table does not have this key. Assuming that the response is correct, 
 where can I find and/or delete this relation? I have deleted an entry 
 inpg_constraint with that name but that did not do the trick

A couple of things.

1:  Can you reproduce this, or is it a one time freak accident?
2:  What does \d from psql say about this table as it is?

If \d doesn't show a primary key but you can't add one, then something
is not right, and we'll have to go snooping through the system
catalogs to figure out what's what.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings