PostgreSQL Custom operator compare varchar and integer

Question

-- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit.

-- Installed from offical repository.

-- No any changes in postgresql.conf .

-- CentOS release 6.8.

-- User: postgres.

-- Used pgAdmin3 LTS by BigSQL.

-- No any unusual log in server.

I have many queries.

in that case I need compare character varying data type (may be a table field) with integer value.

--Result is True or False

select '10' = 10; 
select '10' = '10'; 
select '10'::character varying = '10'::character varying; 
select '10'::character varying = 'foo bar'; 
select '10'::character varying = 'foo bar'::character varying; 
select 'foo bar' = 'foo bar'; 
select '10'::character varying = '10';

--Result is "operator does not exist: character varying = integer"

select '10'::character varying = 10; 

so i create a custom operator for compare character varying and integer.

step 1: create simple function

CREATE OR REPLACE FUNCTION public.is_equal_char_int(character varying, integer) RETURNS boolean AS 
$BODY$ 
BEGIN 
    IF $1 = $2::character varying THEN
        RETURN TRUE;
    ELSE 
        RETURN FALSE; 
    END IF;
End;
$BODY$ 
LANGUAGE plpgsql VOLATILE COST 100;

step 2: create new operator

CREATE OPERATOR public.=( 
PROCEDURE = is_equal_char_int,
LEFTARG = character varying,
RIGHTARG = integer);

so i resoleved my problem and

select '10'::character varying = 10;

return true value.

and new problem is: when i compare character varying value with unkown data type value, postgresql use my custom operator.

select '10'::character varying = 'foo bar';

result is :

invalid input syntax for integer: "foo bar"

select pg_typeof('foo bar');

return unkown data type.

and next step I create new operator for compare character varying and unkown data type.

Step 1:

CREATE OR REPLACE FUNCTION public.is_equal_char_unknown(character varying, unknown)
RETURNS boolean AS
$BODY$
BEGIN
IF $1 = $2::character varying THEN
    RETURN TRUE;
ELSE
    RETURN FALSE;
END IF;
End;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

step 2:

CREATE OPERATOR public.=( 
PROCEDURE = is_equal_char_unknown,
LEFTARG = character varying,
RIGHTARG = unknown);

when I run

select '10'::character varying = 'foo bar';

I give

ERROR: operator is not unique: character varying = unknown.

So I'm in a hole.

Answer 1

There could be another option by defining how the cast between varchars and numerics should be made:

CREATE CAST (VARCHAR AS NUMERIC) WITH INOUT AS IMPLICIT;

This would make it possible to do comparisons like this:

SELECT '1'::character varying = 1::int;
> true

SELECT '01'::character varying = 1::int;
> true

SELECT '2'::character varying = 1::int;
> false

select '10'::character varying = 'foo bar';
> false

More about creating casts in postgresql here: https://www.postgresql.org/docs/current/sql-createcast.html

Answer 2

To understand how type resolution is done for operators in PostgreSQL, read the operator type resolution rules in the documentation.

In your special case, the following operators remain after step 3.a:

• Your custom operator (character varying = integer).

• character = character (implicit conversion from character varyingto character).

• name = name (implicit conversion from character varyingto name).

• text = text (implicit conversion from character varyingto text).

Rule 3.c then chooses your operator, because it is the only one with an exact type match. Without your operator, step 3.d would have chosen text = text, because text is the only preferred type of the string category.

What you are doing at the moment is discovering why certain operators are not defined in PostgreSQL, namely that defining new comparison operators for new type combinations results in ambiguities that lead to errors because PostgreSQL cannot decide which operator to use.

At the heart of the problem is PostgreSQL's ability to overload operators, i.e. to have several operators with the same name. Yet this is a good feature, and the system of casts and operators has been carefully balanced to make the experience as good as possible. The unknown type is also part of that system.

In other words, PostgreSQL is trying to guess what you mean, but this is not always possible. If you want to compare a (not unknown) string and a number, what do you want? Should they be compared as numbers or as strings? Should '010' be the same as 10 or not? PostgreSQL doesn't know what you mean and gives up.