distal-attribute
distal-attribute
distal-attribute
distal-attribute

Creating indexes in Postgres r4811.

roger_ackroyd posted 2 months ago in General
Creating a primary key works fine.
When attempting to creating any other type of key, fails when clicking on the Save button with the following:-

ALTER TABLE "pcd11_par11_wd11_lad11_ew_lu"
ADD INDEX "Index 2" ("pcds");
/* ERROR: type "Index 2" does not exist
LINE 2: ADD INDEX "Index 2" ("pcds")

If I create an index in pgAdmin, it does not show up in Heidi.
ansgar posted 2 months ago
Yes, the primary and unique keys are analyzed with such a query in HeidiSQL:

SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND K.TABLE_NAME='mytable' AND K.TABLE_SCHEMA='myschema' WHERE C.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') ORDER BY K.ORDINAL_POSITION;


I guess I need to extend ParseTableStructure again, to get all other keys into the table editor too.

Is that above query without the "CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')" valid to get all keys?
roger_ackroyd posted 2 months ago
With or without the "CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')" it only returns the PRIMARY key. There are two others on this table but neither are listed in Heidi.
roger_ackroyd posted 2 months ago
This (copied from pgAdmin) runs ok in Heidi but index is not recognised under the Indexes tab.

CREATE INDEX "IDX_1"
ON public.pcd11_par11_wd11_lad11_ew_lu
USING btree
(wd11cd COLLATE pg_catalog."default", par11cd COLLATE pg_catalog."default");
ansgar posted 2 months ago
Yes, the question is here, how to detect those indexes. What about this one:

SELECT * FROM pg_catalog.pg_indexes WHERE schemaname='myschema' AND tablename='mytable'

roger_ackroyd posted 2 months ago
That now shows all the indexes in the grid
Still do not show under the indexes tab
ansgar posted 2 months ago
Well, I did not implement that in HeidiSQL yet. It's also quite a bit of effort, as the indexed columns must be parsed out of the "indexdef" SQL command. Sigh...

Please login to leave a reply, or register at first.