Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Creating indexes in Postgres r4811.

roger_ackroyd posted 5 years 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 5 years 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 5 years 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.


Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.
roger_ackroyd posted 5 years 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 5 years 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 5 years ago
That now shows all the indexes in the grid
Still do not show under the indexes tab
ansgar posted 5 years 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.