[SOLVED] Alter column type deleting those that can't be converted

Alter column type deleting those that can't be converted

I need to change the type of a column from varchar(255) to uuid. I am using:

ALTER TABLE table_name
    ALTER COLUMN col TYPE UUID USING col::UUID;

But if there is some value in that column, which is not in uuid format, I will get this error: SQL Error [22P02]: ERROR: invalid input syntax for type uuid: "some_text"

Is it possible to delete those rows which have values that cannot be converted to uuid?

Solution

Using uuid_or_null function that @JuliusTuskenis suggests you can simply do this:

ALTER TABLE table_name ALTER COLUMN col TYPE UUID USING uuid_or_null(col);
delete from table_name where col is null;

You have to define the function before that.

create function uuid_or_null(s text) returns uuid immutable AS
$$
begin
  return s::uuid;
exception when others then
  return null;
end;
$$ language plpgsql;

The way uuid_or_null is built is quite generic and more or less a pattern for safe casting - try to cast and if it bangs then react accordingly. There are several SO threads using it.

You can also sanitize the table upfront and then alter column type like this:

delete from table_name 
 where col !~* '^[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}$';
ALTER TABLE table_name ALTER COLUMN col TYPE UUID USING col::UUID;