Could you tell me how to check what indexes are created for some table in postgresql ?
Add a comment
|
5 Answers
The view pg_indexes provides access to useful information about each index in the database, e.g.:
select *
from pg_indexes
where tablename = 'test'
The pg_index system view contains more detailed (internal) parameters, in particular, whether the index is a primary key or whether it is unique. Example:
select
c.relnamespace::regnamespace as schema_name,
c.relname as table_name,
i.indexrelid::regclass as index_name,
i.indisprimary as is_pk,
i.indisunique as is_unique
from pg_index i
join pg_class c on c.oid = i.indrelid
where c.relname = 'test'
See examples in db<>fiddle.
3 Comments
Vaggelis Manousakis
More of a silly question on my part but your solution will also print the autogenerated indexes of PostgreSQL on the PKs. Any ideas on how to print only the user-specific indexes ?
Nor.Z
be aware: Table names in pgsql is case sensitive.
If you are in psql, then:
\d tablename
show Indexes, Foreign Keys and references...
6 Comments
Chiel
For case sensitive table names, use
\d "Tablename"
evantkchong
Much easier to use than having to construct a query!
samshers
i tried
\d tablename in dbeaver and its not working - SQL Error [42601]: ERROR: syntax error at or near "\" Position: 1 Error position: line: 56 . Is there a way to run it in GUI/DBeaver.
helvete
@samshers : No, it is client feature of
psql.
prayagupadhyay
I get
ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... on psql CLI |
You can use this query:
select tablename,indexname,tablespace,indexdef from pg_indexes where tablename = 'your_table_name';
where has tablename is a field in pg_indexes ,you an get an accurate indices by matching user defined table at 'your_table_name' at WHERE clause . This will give you the desired details.
Comments
The command
\di
will list all indexes for the current schema.
Comments
You can find all the index related information inside the pg_indexes view. Sometimes, a table may be part of some schema ("owner") or might have had a different name in the past (see: PostgreSQL Rename Table).
So first find out what is the schema ("owner") of the table:
SELECT schemaname, tablename FROM pg_tables WHERE tablename='table_name';
and then query indexes on the table with either of these queries:
SELECT tablename, indexname FROM pg_indexes WHERE tablename='table_name';
-- or
SELECT * FROM pg_indexes WHERE tablename='schema_name.table_name';
As an alternative to all the above, you can also use \d:
\d table_name;
