Postgres Pro
Downloads
Home   >   mailing lists

Thread: See the WHERE clause of a partial index

See the WHERE clause of a partial index

From
Paul Jungwirth
Date:
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.

Re: See the WHERE clause of a partial index

From
Tom Lane
Date:
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

Re: See the WHERE clause of a partial index

From
Paul Jungwirth
Date:
> 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.

Re: See the WHERE clause of a partial index

From
Paul Jungwirth
Date:
> 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.

Re: See the WHERE clause of a partial index

From
Tom Lane
Date:
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

Re: See the WHERE clause of a partial index

From
Paul Jungwirth
Date:
> I think the OID
> argument will need to be the table not the index, but not 100% sure.

Yep, that's true. :-)

Paul


--
_________________________________
Pulchritudo splendor veritatis.