query = """
SELECT
i.relname, idxs.indexdef, idx.indisunique,
array_to_string(array_agg(cols.attname), ',')
FROM pg_catalog.pg_class AS t
INNER JOIN pg_catalog.pg_index AS idx ON t.oid = idx.indrelid
INNER JOIN pg_catalog.pg_class AS i ON idx.indexrelid = i.oid
INNER JOIN pg_catalog.pg_indexes AS idxs ON
(idxs.tablename = t.relname AND idxs.indexname = i.relname)
LEFT OUTER JOIN pg_catalog.pg_attribute AS cols ON
(cols.attrelid = t.oid AND cols.attnum = ANY(idx.indkey))
WHERE t.relname = %s AND t.relkind = %s AND idxs.schemaname = %s
GROUP BY i.relname, idxs.indexdef, idx.indisunique
ORDER BY idx.indisunique DESC, i.relname;"""
cursor = self.execute_sql(query, (table, 'r', schema))
Returns rows like:
relname | sharesoutstanding_for_date_instrument_id
indexdef | CREATE UNIQUE INDEX sharesoutstanding_for_date_instrument_id ON sharesoutstanding USING btree (for_date, instrument_id)
indisunique | t
array_to_string | instrument_id,for_date
which show that the array_to_string column represents the wrong order.
A more correct query is:
select table_class.relname,
index_class.relname,
index.indisunique,
idxs.indexdef,
array_agg(table_attribute.attname order by array_position(index.indkey, table_attribute.attnum))
from pg_class index_class
join pg_index index on index_class.oid = index.indexrelid
join pg_class table_class on table_class.oid = index.indrelid
join pg_attribute table_attribute on table_class.oid = table_attribute.attrelid and table_attribute.attnum = any(index.indkey)
join pg_indexes idxs on idxs.tablename = table_class.relname and idxs.indexname = index_class.relname
where table_class.relname = %s and table_class.relkind = %s and idxs.schemaname = %s
group by table_class.relname, index_class.relname, index.indisunique, idxs.indexdef;
Relevant code in
get_indexes, line 4172 atm.Returns rows like:
which show that the
array_to_stringcolumn represents the wrong order.A more correct query is:
PR incoming.