Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE OR REPLACE FUNCTION public.new_equality (real, real)  -- be explicit about the schema!
  RETURNS bool
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE COST 10
BEGIN ATOMIC
SELECT abs($1 - $2) < 0.2;
END;

CREATE OR REPLACE FUNCTION public.new_inequality (real, real)  -- be explicit about the schema!
  RETURNS bool
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE COST 10
BEGIN ATOMIC
SELECT abs($1 - $2) >= 0.2;
END;

CREATE OPERATOR public.= (             -- be explicit about the schema!
  LEFTARG    = real
, RIGHTARG   = real
, FUNCTION   = public.new_equality
, COMMUTATOR = OPERATOR(public.=)      -- be explicit about the schema!
, NEGATOR    = OPERATOR(public.!=)     -- must also exist
);

CREATE OPERATOR public.!= (
  LEFTARG    = real
, RIGHTARG   = real
, FUNCTION   = public.new_inequality
, COMMUTATOR = OPERATOR(public.!=)
, NEGATOR    = OPERATOR(public.=)
);
-- unqualified operator still picks default operator because pg_catalog comes first in default search_path:
WITH values(id, a, b) AS (
   VALUES
     (1, real '0.1', real '0.2')
   , (2, '1', '1')
   )
SELECT *
FROM (
   SELECT id, a, b, '=' AS opr, a = b                         FROM values
   UNION ALL
   SELECT id, a, b, '= (public.=)', a OPERATOR(public.=) b    FROM values

   UNION ALL
   SELECT id, a, b, '!=', a != b                              FROM values
   UNION ALL
   SELECT id, a, b, '!= (public.!=)', a OPERATOR(public.!=) b FROM values
   ) sub
ORDER BY id, opr COLLATE "C";


id
a
b
opr
?column?


1
0.1
0.2
!=
t

1
0.1
0.2
!= (public.!=)
f

1
0.1
0.2
=
f

1
0.1
0.2
= (public.=)
t

2
1
1
!=
f

2
1
1
!= (public.!=)
f

2
1
1
=
t

2
1
1
= (public.=)
t


-- != is automatic alias of <>
-- OPERATOR(pg_catalog.<>) is just the explicit way of saying <> with default search_path
-- so all of these are the same
WITH values(id, a, b) AS (
   VALUES
     (1, real '0.1', real '0.2')
   , (2, '1', '1')
   )
SELECT *
FROM (
   SELECT id, a, b, '!=' AS opr, a != b                               FROM values
   UNION ALL
   SELECT id, a, b, '<>', a <> b                                      FROM values
   UNION ALL
   SELECT id, a, b, '!= (pg_catalog.!=)', a OPERATOR(pg_catalog.!=) b FROM values
   UNION ALL
   SELECT id, a, b, '<> (pg_catalog.<>)', a OPERATOR(pg_catalog.<>) b FROM values
   ) sub
ORDER  BY id, opr COLLATE "C";


id
a
b
opr
?column?


1
0.1
0.2
!=
t

1
0.1
0.2
!= (pg_catalog.!=)
t

1
0.1
0.2
<>
t

1
0.1
0.2
<> (pg_catalog.<>)
t

2
1
1
!=
f

2
1
1
!= (pg_catalog.!=)
f

2
1
1
<>
f

2
1
1
<> (pg_catalog.<>)
f

id	a	b	opr	?column?
1	0.1	0.2	!=	t
1	0.1	0.2	!= (public.!=)	f
1	0.1	0.2	=	f
1	0.1	0.2	= (public.=)	t
2	1	1	!=	f
2	1	1	!= (public.!=)	f
2	1	1	=	t
2	1	1	= (public.=)	t

id	a	b	opr	?column?
1	0.1	0.2	!=	t
1	0.1	0.2	!= (pg_catalog.!=)	t
1	0.1	0.2	<>	t
1	0.1	0.2	<> (pg_catalog.<>)	t
2	1	1	!=	f
2	1	1	!= (pg_catalog.!=)	f
2	1	1	<>	f
2	1	1	<> (pg_catalog.<>)	f