postgresql - Retrieve partial index WHERE expression

Question

How would I run a query (on PostgreSQL internal tables) to retrieve the conditional expression for the partial index (roles_user_group_role_idx below in this case).

# \d roles
                                Table "public.roles"
    Column     |            Type             |               Modifiers               
---------------+-----------------------------+---------------------------------------
 id            | character varying(15)       | not null default next_id('r'::bpchar)
 user_id       | character varying(15)       | not null
 group_id      | character varying(15)       | not null
 role          | character varying(255)      | not null
 language_code | character varying(2)        | 
 created_at    | timestamp without time zone | not null
 updated_at    | timestamp without time zone | not null
Indexes:
    "roles_pkey" PRIMARY KEY, btree (id)
    "roles_user_group_role_idx" UNIQUE, btree (user_id, group_id, role) WHERE language_code IS NULL

According to PostgreSQL pg_index doc, pg_index.indpred seems to be the field I need to look into. Running this query:

SELECT
    C.oid,
    I.indpred
FROM pg_catalog.pg_class C,
     pg_catalog.pg_namespace N,
     pg_catalog.pg_index I
WHERE C.relname = 'roles_user_group_role_idx'
  AND C.relnamespace = N.oid
  AND I.indexrelid = C.oid
  AND N.nspname = 'public';

gives me this, which is not quite there.

   oid   |                                                                                  indpred                                                                                  
---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1235504 | {NULLTEST :arg {VAR :varno 1 :varattno 5 :vartype 1043 :vartypmod 6 :varcollid 100 :varlevelsup 0 :varnoold 1 :varoattno 5 :location 95} :nulltesttype 0 :argisrow false}

Am I on the right direction? How do I get the WHERE clause of the partial index? I'm using PG 9.2

huy · Accepted Answer · 2013-04-15 03:34:40Z

8

OK after a bit of googling I found the answer here, correct query:

SELECT
    C.oid,
    pg_get_expr(I.indpred, I.indrelid)
FROM pg_catalog.pg_class C,
     pg_catalog.pg_namespace N,
     pg_catalog.pg_index I
WHERE C.relname = 'roles_user_group_role_idx'
  AND C.relnamespace = N.oid
  AND I.indexrelid = C.oid
  AND N.nspname = 'public';

result:

   oid   |       pg_get_expr       
---------+-------------------------
 1235504 | (language_code IS NULL)

answered Apr 15, 2013 at 3:34

huy

4,9347 gold badges38 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql - Retrieve partial index WHERE expression

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related