Question
L&T Infotech
IN
Last activity: 27 May 2018 6:26 EDT
Clone pc_work table to store work objects in new table name?
Hi team,
I am trying to clone pc_work table to store work objects in new table name.Modified script to update the table name. After executing script, getting exception like
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pc_work_pk" for table "pc_work_externalinsert"
ERROR: relation "pc_work_pk" already exists
********** Error **********
ERROR: relation "pc_work_pk" already exists
SQL state: 42P07
Please let us know if any suggestions.
Thanks,
Hussain
Pegasystems Inc.
IN
Thanks for posting on PSC.
As per my understanding, there may be a situation when you just want to create an exact copy or clone of an existing table(i.e pc_work) to test or perform something without affecting the original table(new table:pc_work_ExternalInsert). If no, Could you elaborate more on your requirement?
If yes, I have reviewed the SQL query shared. I could observe multiple anomalies in the DROP command as well as CREATE command.
On first observation, there isn't any "CASCADE" keyword while executing "DROP" command(Query used:
DROP TABLE personaledition.pc_work_ExternalInsert;).
Ideally, it should work, as DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)
I could find similar errors reported in generic and found couple of articles with the solution.
Thanks for posting on PSC.
As per my understanding, there may be a situation when you just want to create an exact copy or clone of an existing table(i.e pc_work) to test or perform something without affecting the original table(new table:pc_work_ExternalInsert). If no, Could you elaborate more on your requirement?
If yes, I have reviewed the SQL query shared. I could observe multiple anomalies in the DROP command as well as CREATE command.
On first observation, there isn't any "CASCADE" keyword while executing "DROP" command(Query used:
DROP TABLE personaledition.pc_work_ExternalInsert;).
Ideally, it should work, as DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)
I could find similar errors reported in generic and found couple of articles with the solution.
- Relation "ABC" already exists.
Hope it helps you. Kindly notify if it does by marking this post as answered for larger Pega audience.
Regards,
Asif