Search code examples
sqlarrayspostgresqltypescomparison

How can I test if int2vector contains exactly one specific value?

My current code looks like this:

SELECT
  1
FROM pg_namespace   sch
JOIN pg_class       tab ON tab.relnamespace = sch.oid
JOIN pg_index       idx ON idx.indrelid     = tab.oid
JOIN pg_class       icl ON icl.oid          = idx.indexrelid
JOIN pg_attribute   col ON col.attrelid     = tab.oid
WHERE
      sch.nspname      = 'my_schema'
  AND tab.relkind      = 'r'
  AND idx.indisprimary
  AND icl.relname      = 'pk_my_table'
  AND col.attname      = 'my_table_id'
  AND idx.indkey       = ARRAY[col.attnum] -- <-- The problematic comparison
;

This doesn't work, because: ERROR: operator does not exist: int2vector = smallint[].

I tried various combinations of:

  • casting indkey to an array like idx.indkey::smallint[]
  • casting ARRAY[col.attnum] to an int2vector
  • using the ALL operator
  • using the @> operator

How to check whether indkey contains exactly one entry which is exactly col.attnum?

Solution

  • You could cast to text and convert to an integer array:

    AND string_to_array(idx.indkey::text, ' ')::int2[] = ARRAY[col.attnum]

    Actually, since pg_attribute.attnum is a smallint (int2), use int2[].

    And since you are only interested in a single column, you can simplify:

    AND idx.indkey::text = col.attnum::text

    SQL Fiddle.

    Careful when dealing with more than one column in the index. Consider this related answer: