postgres get names of jsonb fields that comprise a unique index

Question

I'm using postgres 12.5. I've been researching answers on how to get the names of the columns that comprise a unique index. After combining a few answer on StackOverflow and elsewhere I came up with this solution

select i.relname as index_name,
    a.attname as column_name
from pg_class c
    inner join pg_index ix on c.oid=ix.indrelid
    inner join pg_class i on ix.indexrelid=i.oid
    inner join pg_attribute a on a.attrelid=c.oid and a.attnum=any(ix.indkey)
where c.oid='public.accounts'::regclass::oid
    and ix.indisunique is true
order by array_position(ix.indkey, a.attnum) asc

That solution worked great for unique indexes on traditional column types but didn't work for unique indexes on fields within a jsonb column type. See the example in this SQLFiddle

http://sqlfiddle.com/#!17/22f6d/4

In that SQLFiddle you can see how the table is created and two different unique indexes are created. You can see how the above query gets all regular column names that comprise a unique index but doesn't get the jsonb field name. But you can also see that the unique index on the jsonb field actually does its job.

How would I modify (or replace) the above query to return the names of the columns that comprise a compound unique index including jsonb fields?

p.s. Please don't suggest I move the field out of jsonb to a dedicated column. This is a legacy schema that I have inherited and need to make it work as-is

I get this error on the fiddle "Unable to get host connection: Connections could not be acquired from the underlying database!" (Postgresl 9.6) — Dri372
– Dri372, Commented Jun 7, 2021 at 22:35

Laurenz Albe · Accepted Answer · 2021-06-08 01:00:27Z

1

If you have an index on an expression, that expression is stored in the indexprs column of pg_index in the form of a parsed node tree.

To deparse that into an SQL string, you can use the pg_get_expr function:

SELECT i.relname AS index_name,
    coalesce(
       a.attname,
       pg_get_expr(ix.indexprs, ix.indrelid)
    ) AS indexed_expression
FROM pg_class c
    INNER JOIN pg_index ix ON c.oid = ix.indrelid
    INNER JOIN pg_class i ON ix.indexrelid = i.oid
    LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY (ix.indkey)
WHERE c.oid = 'public.accounts'::regclass
    AND ix.indisunique
ORDER BY array_position(ix.indkey, a.attnum) ASC;

    index_name     │    indexed_expression     
═══════════════════╪═══════════════════════════
 accounts_pkey     │ id
 accounts_expr_idx │ (data ->> 'member'::text)
(2 rows)

But I don't think that there is a way to extract the column names used in that expression from the node tree in SQL.

The only possible path would be via pg_depend, where dependencies between an index and the columns of the underlying table are stored.

answered Jun 8, 2021 at 1:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

HairOfTheDog Over a year ago

I'm not sure I understand your answer. The two rows you show in the code section suggests that the SELECT statement will get the name of the jsonb field but you also say, "But I don't think that there is a way to extract the column names" which suggests it's not possible. Also that SELECT statement doesn't work with this SQLFiddle sqlfiddle.com/#!17/22f6d/9 nor does it work in my local dev env with Postgres version 13.1. But there has to be a way to get it because DBeaver can display it.

Laurenz Albe Over a year ago

"Does not work" is an inadequate description. pg_get_expr gets the indexed expression in text form, but I don't see how you could get the involved columns.

Collectives™ on Stack Overflow

postgres get names of jsonb fields that comprise a unique index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related