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?
Using uuid_or_null function that @JuliusTuskenis suggests you can simply do this:
You have to define the function before that.
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: