Converting column values in postgresql from text to smallint throws error

Question

As you can see, the only column thats throwing error here is user_uuid

I want it to have datatype uuid. Previously before loading the data it was of the type object in python.

Simplified problem:

ALTER TABLE dim_users
    ALTER COLUMN user_uuid TYPE UUID USING (user_uuid::UUID);

ERROR:

ERROR: invalid input syntax for type uuid: "abc"

Answer 1

If your values are indeed UUID-compatible, then something like this works:

create table foo(a varchar(64));

insert into foo(a) values('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11');

alter table foo
alter column a type uuid using a::uuid;

so in that case your script should work. It will work with nulls as well:

create table foo(a varchar(64));

insert into foo(a) values('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'), (null);


alter table foo
alter column a type uuid using a::uuid;

but it won't work by itself on values that cannot be converted into uuid:

create table foo(a varchar(64));

insert into foo(a) values('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'), ('b');


alter table foo
alter column a type uuid using a::uuid;

so you have some values that cannot be converted into uuid. So, we need to determine whether a value is a proper uuid, convert it to uuid in that case and default to null otherwise:

create table foo(a varchar(64));

insert into foo(a) values('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'), ('b');


alter table foo
alter column a type uuid using case when a ~ E'^[[:xdigit:]]{8}-([[:xdigit:]]{4}-){3}[[:xdigit:]]{12}$' then a::uuid else null end;

Nice, is it not?

You are of course well-advised to back up your data before doing this and carefully check the results.