Hello,
Using Toad Data Modeler 5.3 with PostgreSQL, I created an index on a table in schema called “ua”. I also added description.
Related part of the generated DDL is below:
CREATE TABLE “ua”.“ua_entity”( … );
COMMENT ON COLUMN “ua”.“ua_entity”.“id” IS ‘Entity id no.’;
…
CREATE UNIQUE INDEX “ua_idx_entity_unique_url_for_org” ON “ua”.“ua_entity” (“organization_id”,“url”) WHERE url IS NOT NULL;
COMMENT ON INDEX “ua_idx_entity_unique_url_for_org” IS ‘Index for speed.’;
This DDL causes error, because schema prefix is not added to index description. However schema prefix added column description as seen above. This behavior of Toad Data Modeler is inconsistent and results in error below:
ERROR: relation “ua_idx_entity_unique_url_for_org” does not exist.
Adding schema name to index name also does not help, because Toad Data Modeler generates names in quotation marks.
Is there any workaround until this gets fixed?
PS. This behavior does not affect descriptions in “public” schema, because PostgreSQL assumes any non prefixed object in “public” schema.
Hello Ozum,
You are right, TDM does not seem to generate Schema names in COMMENT ON INDEX statements. This issue has been noted and our dev team is already working on solution.
As for workaround, you can add the Schema name to the statement manually:
COMMENT ON INDEX “ua”.“ua_idx_entity_unique_url_for_org” IS ‘Index for speed.’
I am not sure how did you try to add Schema name to Index name, but if you have used the following statement:
CREATE UNIQUE INDEX “ua”.“ua_idx_entity_unique_url_for_org” ON “ua”.“ua_entity” (“organization_id”,“url”) WHERE url IS NOT NULL;
it will not work, since Index is automatically created in the same Schema as its parent table.
Also please note that if you don’t want to use quotation marks in generated script, you can go to DDL Script Generation | Detail Settings and uncheck Use Quotation Marks.
Anyway, thank you for bringing this issue to our attention.
Regards,
Lukas
Hello Lukas,
As your suggested workaround, unfortunatelly, altering generated DDL is not possible in our workflow. I didn’t see Use Quotation Marks. Until dev team fixes schema names in COMMENT ON INDEX statements, I will use this workaround:
I will disable Use Quotation Marks and name my indexes such as ua.ua_idx_entity_unique_url_for_org. This way index names contain schema names.
Caveat for anybody using this workaround: After dev team fixes schema name in COMMENT ON INDEX statements, this workaround breaks the generated DDL, because this time they will be generated with double schema name (one auto generated and one manually added) like ua.ua.ua_idx_entity_unique_url_for_org. Workaround should be cancelled in fixed versions.
Thank you Lukas,
Ragards,
Ozum