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.
Answer from JGH on Stack OverflowIs there a cleaner way to write an existence check?
Check if a key/value or value exists in an arbitrary jsonb.
Combining the comment from @Rory and info in @Zaytsev Dmitry's answer:
The CREATE INDEX CONCURRENTLY will not return until the index has finished building. So you know the index is done when your query returns.
However if you're building a large index that runs for hours you may wonder if it is 'really' still running.
You can use the query for 'invalid' indexes:
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
...if your index appears in the results then it either failed or is still in progress.
Another way to confirm the latter is to check the locks table:
SELECT a.datname,
l.relation::regclass,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) AS "age",
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock'
ORDER BY a.query_start;
Although CONCURRENTLY does not take exclusive lock on the table it will hold a weaker ShareUpdateExclusiveLock which you should see in the results from this query.
If there is no lock it implies that either the index has finished building, or the create failed and left an invalid index.
Between these two queries you can thus determine which of the three possible states (completed/in progress/failed) your index is in.
You can get list of invalid indexes.
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
If you see your index in this query it means the index won't work and you have to recreate index.
Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it with CONCURRENTLY flag
Currently, I have query like this:
SELECT (EXISTS(SELECT 1 FROM "user" WHERE password = <password> AND username = <username> LIMIT 1))::bool;
It works, but it is two sub queries, and I was wondering if there is a better way to write a query to check the existence of a value?