1

Given a table:

CREATE TABLE IF NOT EXISTS test."Op" 
("id" TEXT PRIMARY KEY);

I want to create a query to check for the existence of an index. I created a query based off of Checking for existence of index in PostgreSQL:

SELECT exists(SELECT * FROM pg_catalog.pg_index WHERE "indkey" @> (
  SELECT "attrelid" FROM pg_catalog.pg_attribute WHERE "attrelid" = (
    SELECT "oid" FROM pg_catalog.pg_class WHERE "relname" = 'Op_pkey' AND "relnamespace" = (
      SELECT "oid" FROM pg_catalog.pg_namespace WHERE "nspname" = 'test'
    )
  )
))

but it's currently giving this error:

ERROR:  operator does not exist: int2vector @> integer
LINE 1: ...exists(SELECT * FROM pg_catalog.pg_index WHERE "indkey" @> (
                                                                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 64

pg_catalog.pg_index."indkey" is a vector but I'm not sure how to construct a query that says "if any element in the vector matches X"

zcaudate
  • 13,998
  • 7
  • 64
  • 124
  • I think the "core" query can be simplified to `select * from pg_catalog.pg_index where indrelid = 'stuff."Op"'::regclass and indexrelid = 'stuff."Op_pkey"'::regclass ` –  Feb 17 '21 at 08:33

1 Answers1

4

Cast the int2vactor (which is kind of an internal data type) to an array of smallint:

... WHERE WHERE indkey::smallint[] @> (...)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263