Creating indexes in Postgres r4811.

[expired user #8249]'s profile image [expired user #8249] posted 10 years ago in General Permalink
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's profile image ansgar posted 10 years ago Permalink
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?
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
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.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
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's profile image ansgar posted 10 years ago Permalink
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'
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
That now shows all the indexes in the grid
Still do not show under the indexes tab
ansgar's profile image ansgar posted 10 years ago Permalink
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.