PostgreSQL custom operator UUID to varchar

Question

I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as Postgres doesn't have a built in operator for varchar = uuid. Rather rewrite all my views or attempt a single massive migration, I wanted to temporarily create a uuid = varchar operator until the migration is completed.

I've never created a custom operator before and my attempt to below is not working:

CREATE OR REPLACE FUNCTION uuid_equal_varchar (varchar, uuid)
RETURNS boolean AS 'SELECT $1::text = $2::text;' LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    leftarg = character varying,
    rightarg = uuid,
    procedure = uuid_equal_varchar,
    commutator = =
);

However this operator breaks everything. Including a simple varchar = varchar comparison (see below):

SELECT * FROM test WHERE pk_test = '123';
ERROR:  invalid input syntax for uuid: "123"

Can someone explain to me what I am doing wrong? Am I trying to attempt something that is not possible?

Answer 1

What you to do is CREATE CAST not an operator. This is the problem:

SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq
FROM gen_random_uuid() AS t(uuid);
ERROR:  operator does not exist: uuid = character varying
LINE 1: SELECT pg_typeof(uuid), uuid = uuid::varchar FROM gen_random...
                                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

So we need to create a CAST. This promotes varchar to uuid when needed. Though you could go the other way if you really wanted. If you do this you need to create the cast (uuid AS text). The type system doesn't know varchar: we don't use that in PostgreSQL; it's essentially text with a type-inconsequential length constraint and thus slower.

CREATE CAST (varchar AS uuid)
  WITH INOUT
  AS IMPLICIT;

And now you can try again.

 pg_typeof | eq
-----------+----------
 uuid      | t
(1 row)

For reference,

• IMPLICIT

  Indicates that the cast can be invoked implicitly in any context.

• INOUT

  Indicates that the cast is an I/O conversion cast, performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type.

That said, all of the views have to be recreated when an underlying type changes,

CREATE TABLE foo(uuid)
AS
  VALUES (gen_random_uuid()::varchar);

CREATE VIEW bar AS TABLE foo;

Now we try to change the type in foo

ALTER TABLE foo
  ALTER uuid
  SET DATA TYPE uuid;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view bar depends on column "uuid"

That fails, so we drop bar change the type and recreate it,

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo ALTER uuid SET DATA TYPE uuid;
  CREATE VIEW bar AS TABLE foo;
COMMIT;

And we have joy.

 \d bar;
     View "public.bar"
 Column | Type | Modifiers 
--------+------+-----------
 uuid   | uuid | 

Answer 2

I was looking into something similar and landed on this post. I also thought why "create operator" is not working in your case.

First check what all equal ("=") operators are available for these data types (char, varchar, text, uuid):

select oid,typname from pg_type where typname in ('char','varchar','text','uuid');
 oid  | typname 
------+---------
   18 | char
 1043 | varchar
 2950 | uuid
   25 | text

select oprname,oprleft,oprright from pg_operator where oprleft in (18,1043,25,2950) and oprright in (18,1043,25,2950) and oprname='=';
 oprname | oprleft | oprright 
---------+---------+----------
 =       |      18 |       18
 =       |      25 |       25
 =       |    2950 |       25
 =       |    2950 |     1043
 =       |    2950 |     2950

Now how a normal "varchar" = "varchar" is working before as there is no "=" operator for varchar data type. I believe this is because of a binary coercible cast is available from varchar to text and then for text comparison we have equal operator (oid 25 to 25).

postgres=# \dC+ text
                                      List of casts
    Source type    |    Target type    |      Function      |   Implicit?   | Description 
-------------------+-------------------+--------------------+---------------
 character varying | text              | (binary coercible) | yes           

Now once you created that "=" operator, varchar does not need to convert to binary coercible text because it finds a "=" operator with oprleft as varchar and it tries to find a uuid on oprright and hence failed.

Instead if we create a "text" to "uuid" operator then your use case works fine because for text there is already a "=" operator for oprleft='text' and oprright='text' and it chooses that instead of the new operator.

Ofcourse this is more complex so just adding a cast is easy.

CREATE OR REPLACE FUNCTION uuid_equal_varchar (text, uuid)
RETURNS boolean AS 'SELECT $1::text = $2::text;' LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    leftarg = text
    rightarg = uuid,
    procedure = uuid_equal_varchar,
    commutator = =
);