See the WHERE clause of a partial index
Hello,
I created some indexes with WHERE clauses, so that only part of the
table would be indexed. Now I'd like to get a list of indexes, and
include the WHERE clause if an index has one. This is what I'm trying
right now:
SELECT indc.relname, ind.indpred
FROM pg_index ind, pg_class indc
WHERE indc.oid = ind.indexrelid;
But that gives results like this:
relname | indpred
--------------------+------------------------
. . .
index_pwords_on_language_id | NULL
index_user_languages_on_user_id_and_language_id |
{NULLTEST :arg {VAR :varno 1 :varattno 6 :vartype 1114 :vartypmod -1
:varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 6 :location 128}
:nulltesttype 0 :argisrow false}
index_user_lists_on_user_id | NULL
index_users_on_email | NULL
. . .
I'm not sure how to interpret that `indpred` column. Is there any way
to reconstruct the WHERE clause I originally passed to the CREATE
INDEX command?
Thanks,
Paul
--
_________________________________
Pulchritudo splendor veritatis.
Paul Jungwirth <pj@illuminatedcomputing.com> writes:
I'm not sure how to interpret that `indpred` column. Is there any way
to reconstruct the WHERE clause I originally passed to the CREATE
INDEX command?
pg_get_indexdef() should help. If you really want just the WHERE
clause, possibly pg_get_expr() would work, but I've not tried it on
index clauses.
regards, tom lane
pg_get_indexdef() should help. If you really want just the WHERE
clause, possibly pg_get_expr() would work, but I've not tried it on
index clauses.
Thank you for such a quick response!
pg_get_indexdef is very helpful:
select pg_get_indexdef(223630);
pg_get_indexdef
-----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX
index_user_languages_on_user_id_and_language_id ON user_languages
USING btree (user_id, language_id) WHERE (deleted_at IS NULL)
(1 row)
It'd be great to get just the WHERE clause if possible, although I can
work around it if not. I couldn't find much documentation re
pg_get_expr. Does this message mean I can't use it, or am I just doing
something wrong?:
select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6
:vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1
:varoattno 6 :location 128} :nulltesttype 0 :argisrow false}',
223630);
ERROR: cannot accept a value of type pg_node_tree
LINE 1: select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6...
Thank you for your help!
Paul
--
_________________________________
Pulchritudo splendor veritatis.
It'd be great to get just the WHERE clause if possible, although I can
work around it if not. I couldn't find much documentation re
pg_get_expr.
To answer my own question, this works:
select pg_get_expr(indpred, indrelid) from pg_index where
indexrelid = 223630;
pg_get_expr
----------------------
(deleted_at IS NULL)
(1 row)
So I guess the problem was trying to get a pg_node_tree out of a string.
Thanks again!
Paul
--
_________________________________
Pulchritudo splendor veritatis.
Paul Jungwirth <pj@illuminatedcomputing.com> writes:
It'd be great to get just the WHERE clause if possible, although I can
work around it if not. I couldn't find much documentation re
pg_get_expr. Does this message mean I can't use it, or am I just doing
something wrong?:
select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6
:vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1
:varoattno 6 :location 128} :nulltesttype 0 :argisrow false}',
223630);
ERROR: cannot accept a value of type pg_node_tree
LINE 1: select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6...
That's a security restriction (if you could pass random text to
pg_get_expr, you could probably crash it). If you feed the actual
pg_index.indpred column to it, that won't happen. I think the OID
argument will need to be the table not the index, but not 100% sure.
regards, tom lane