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?
Best Answer
What you to do is
CREATE CAST
not an operator. This is the problem: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 essentiallytext
with a type-inconsequential length constraint and thus slower.And now you can try again.
For reference,
IMPLICIT
INOUT
That said, all of the views have to be recreated when an underlying type changes,
Now we try to change the type in
foo
That fails, so we drop
bar
change the type and recreate it,And we have joy.