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,516
  • 7
  • 60
  • 111
  • 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 ` – a_horse_with_no_name Feb 17 '21 at 08:33

1 Answers1

1

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

... WHERE WHERE indkey::smallint[] @> (...)
Laurenz Albe
  • 167,868
  • 16
  • 137
  • 184