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?
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)
Indicates that the cast can be invoked implicitly in any context.
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
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 |
External links referenced by this document: