Checking for existence of index in PostgreSQL
You can get the list of indexes, their table and column using this query:
select
t.relname as table_name,
i.relname as index_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'
order by
t.relname,
i.relname;
From there, you can check existence by index name or involved column(s) and decide to create/skip the index.
How to list indexes created for table in postgres
The view pg_indexes provides access to useful information about each index in the database, eg.
select *
from pg_indexes
where tablename not like 'pg%';
List columns with indexes in PostgreSQL
Create some test data...
create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));
List indexes and columns indexed:
select
t.relname as table_name,
i.relname as index_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 'test%'
order by
t.relname,
i.relname;
table_name | index_name | column_name
------------+------------+-------------
test | pk_test | a
test | pk_test | b
test2 | uk_test2 | b
test2 | uk_test2 | c
test3 | uk_test3ab | a
test3 | uk_test3ab | b
test3 | uk_test3b | b
test3 | uk_test3c | c
Roll up the column names:
select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as column_names
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 'test%'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;
table_name | index_name | column_names
------------+------------+--------------
test | pk_test | a, b
test2 | uk_test2 | b, c
test3 | uk_test3ab | a, b
test3 | uk_test3b | b
test3 | uk_test3c | c
How do you check if a certain index exists in a table?
You can do it using a straight forward select like this:
SELECT *
FROM sys.indexes
WHERE name='YourIndexName' AND object_id = OBJECT_ID('Schema.YourTableName')
how to to first check whether the table exist or not before creating it in postgresql?
PostgreSQL uses the following syntax:
CREATE TABLE IF NOT EXISTS mytable (
-- Column definitions...
)
Fastest check if row exists in PostgreSQL
Use the EXISTS key word for TRUE / FALSE return:
select exists(select 1 from contact where id=12)
Related Topics
Aspentech Infoplus 21 - How to Connect and Query Data
Reshape from Wide to Long in Big Query (Standard Sql)
Id Best Practices for Databases
Query Temp Table in Stored Proc Whilst Debugging in SQL 2008 Management Studio
Aggregate Function Over a Given Time Interval
Sql Server Search for a Column by Name
How to Calculate Average of a Column and Then Include It in a Select Query in Oracle
Atomically Mark and Return a Group of Rows in Database
Join and Left Join Equivalent in Linq
How to Return Rows from a Declare/Begin/End Block in Oracle
Cannot Drop a System-Generated Sequence
Read Text File to Insert Data into Oracle SQL Table
Count Number of Occurrences for Each Unique Value
Sql Server 2012 Sp_Helptext Extra Lines Issue
How Much Real Storage Is Used with a Varchar(100) Declaration in MySQL
Cannot Connect to Azure SQL Database, Even with Whitelisted Ip