How to specify on error behavior for postgresql conversion to UUID-CodePudding

I need to write a query to join 2 tables based on UUID field. Table 1 contains user_uuid of type uuid. Table 2 has this user_uuid in the end of url, after the last slash. The problem is that sometimes this url contains other value, not castable to uuid.

My workaround like this works pretty good.

LEFT JOIN table2 on table1.user_uuid::text = regexp_replace(table2.url, '.*[/](.*)$', '\1')

However i have a feeling that better solution would be to try to cast to uuid before joining.

And here i have a problem. Such query:

LEFT JOIN table2 on table1.user_uuid = cast (regexp_replace(table2.url, '.*[/](.*)$', '\1') as uuid)

gives ERROR: invalid input syntax for type uuid: "rfa-hl-21-014.html" SQL state: 22P02

Is there any elegant way to specify the behavior on cast error? I mean without tons of regexp checks and case-when-then-end...

Appreciate any help and ideas.

CodePudding user response：

You could cast the uuid from table 1 to text and join that with the suffix from table 2. That will never give you a type conversion error.

This might require an extra index on the expression in the join condition if you need fast nested loop joins.

CodePudding user response：

There are additional considerations when converting a uuid to text. Postgres will yield a converted value in standard form (lower case and hyphened). However there are other formats for the same uuid value that could occur in you input. For example upper case and not hyphened. As text these would not compare equal but as uuid they would. See demo here.

select * 
  from table1  t1
  join table2  t2
    on replace(t_uuid::text, '-','') = replace(lower(t2.t_stg),'-','') ;

Since your data clearly contains non-uuid values, you cannot assume standard uuid format either. There are also additional formats (although not apparently often used) for a valid UUID. You may want to review UUID Type documentation