There is a lot of information, that can be retrieved from information_schema and pg_catalog in postgresql. I would like to retrieve information about columns indexed by a certain index, similar to what I would achieve with pragma index_info(<index_name>) in sqlite3. How can this be achieved without parsing create index statement?
-
@OMG Ponies: Because the answer is not as simple as it sounds.intgr– intgr2011-03-20 21:43:10 +00:00Commented Mar 20, 2011 at 21:43
Add a comment
|
2 Answers
Those things are pretty easy to find out.
Simply run psql with the -E option and it will show you the SQL statements that are used. So when running \d index_name the following statement (among others) is used to retrieve the index columns:
SELECT a.attname,
pg_catalog.format_type (a.atttypid,a.atttypmod),
(SELECT SUBSTRING (pg_catalog.pg_get_expr (d.adbin,d.adrelid) FOR 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum
AND a.atthasdef),
a.attnotnull,
a.attnum,
pg_catalog.pg_get_indexdef (a.attrelid,a.attnum,TRUE) AS indexdef
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT oid FROM pg_class WHERE relname = 'index_name')
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum;
1 Comment
gruszczy
I had no idea, that you can display an index information with
\d. That's very helpful, as well as '-E' option. Thanks a lot.Accepted answer didn't work for me (error occured on execution).
Anyway, you can list all columns in the database and mark all indexed in some way columns (abilities to limit result rowset are mentioned as comments):
WITH
table_select as (
select row_number() over(ORDER BY relname) as rownum,
c.relname, c.oid, c.reltuples
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'r'::"char"
--AND n.nspname = '%MyNameSpaceHere%'
ORDER BY c.relname
),
indxs as (
select distinct t.relname as table_name, a.attname as column_name
from pg_class t, pg_class i, pg_index ix, pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
--and t.relname like 'mytable here'
and cast (i.oid::regclass as text) like '%MyNameSpaceHere%'
order by
t.relname --, i.relname
),
cols as (
select a.attname, a.attrelid, c.oid, col.TABLE_NAME, col.COLUMN_NAME
FROM table_select c
JOIN pg_attribute a ON (a.attrelid = c.oid) AND (a.attname <> 'tableoid')
LEFT JOIN information_schema.columns col ON
(col.TABLE_NAME = c.relname AND col.COLUMN_NAME = a.attname )
WHERE
( a.attnum >= 0 ) --attnum > 0 for real columns
)
--select * from table_select t
select c.TABLE_NAME, c.COLUMN_NAME,
case when i.column_name is not null then 'Y' else '' end as is_indexed
from cols c
left join indxs i on (i.table_name = c.table_name and i.column_name = c.column_name)
The example result:
table_name column_name is_indexed
'events id "Y"
events type "Y"
events descr "" '
