postgres_dba icon indicating copy to clipboard operation
postgres_dba copied to clipboard

fix: resolve operator ambiguity in i3 foreign key check

Open NikolayS opened this issue 5 months ago • 0 comments

Summary

Fixes the "operator is not unique" error in the i3 (foreign keys with missing or bad indexes) check by adding explicit type casting to the key_cols parameter.

Problem

Users reported encountering this error when running the i3 check:

ERROR:  operator is not unique: smallint[] pg_catalog.@> smallint[]
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

This occurred on line 66 of i3_non_indexed_fks.sql where the @> operator was being used with operator(pg_catalog.@>) syntax to avoid conflicts with the intarray extension (fixed in PR #35), but PostgreSQL still couldn't resolve which operator to use due to type ambiguity.

Solution

Added explicit ::int2[] cast to the key_cols parameter to match the left side of the comparison, ensuring PostgreSQL knows exactly which @> operator to use:

and (indkey::int2[])[0:(array_length(key_cols,1) -1)] operator(pg_catalog.@>) key_cols::int2[]

Both sides are now explicitly int2[] (smallint array), eliminating the ambiguity.

Fixes #62

NikolayS avatar Sep 30 '25 03:09 NikolayS