UUID in MSSQL being read as char(36) complicating migration into Postgres.

[ak2766]

I have several tables in an MSSQL database where the id field is configured as uniqueidentifier. I'm now in the process of migrating to Postgres and decided on using Trino based on some tutorial I've seen online. In the process, I noticed that the MSSQL connector is reading this UUID based uniqueidentifier as char(36) and for the life of me I cannot figure out how to transform this into a Postgres UUID.

Everything ChatGPT has suggested fails for one reason or another. I tried running the migration on a small table with the defaults and this char(36) appears as bpchar(36) in Postgres. I was then able to modify the bpchar(36) to uuid. However, there are several tables with multiple hundreds of gigs of data and I'd imagine that the post conversion process could take an inordinate amount of time so I'm trying to limit that.

I've tried using Microsoft's DTSWizard on one table but I cancelled the process on account of it taking too long. Trino is supposedly much faster hence here I am.

Here are some of the suggestions from ChatGPT that I've tried already:

Suggestion 1:

CREATE TABLE postgresql.public.dummy AS
SELECT CAST(c.id AS UUID) AS id
FROM mssql.dbo.contents c

Result 1:

SQL Error [58]: Query failed (#20241118_220936_00128_7vgbs): line 2:8: Cannot cast char(36) to uuid

Suggestion 2:

CREATE TABLE postgresql.public.dummy AS
SELECT CAST(from_char(c.id, 'UUID') AS UUID) AS id
FROM mssql.dbo.contents c

Result2:

SQL Error [6]: Query failed (#20241118_221040_00129_7vgbs): line 2:13: Function 'from_char' not registered

Suggestion 3:

CREATE TABLE postgresql.public.dummy AS
SELECT UUID(c.id) AS id
FROM mssql.dbo.contents c

Result 3:

SQL Error [6]: Query failed (#20241118_221231_00130_7vgbs): line 2:9: Unexpected parameters (char(36)) for function uuid. Expected: uuid()

Suggestion 4:

CREATE TABLE postgresql.public.dummy AS
SELECT CAST(TRIM(c.id) AS UUID) AS id
FROM mssql.dbo.contents c
WHERE  c.id REGEXP '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$'

Result 4:

SQL Error [1]: Query failed (#20241118_221403_00131_7vgbs): line 4:13: mismatched input 'REGEXP'. Expecting: '%', '*', '+', '-', '.', '/', 'AND', 'AT', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', 'WINDOW', 'WITH', '[', '||', <EOF>, <predicate>

[hashhar]

The docs are not ideal.

You can cast to UUID from:

• VARCHAR
• VARBINARY

So the following works as expected:

trino> use memory.default;
USE
trino:default> create table source (col char(36));
CREATE TABLE
trino:default> create table target (col uuid);
CREATE TABLE
trino:default> insert into source values ('12151fd2-7586-11e9-8f9e-2a86e4085a59');
INSERT: 1 row
trino:default> insert into target select cast(cast(col as varchar) as uuid) from source;
INSERT: 1 row
trino:default> select * from target;
                 col
--------------------------------------
 12151fd2-7586-11e9-8f9e-2a86e4085a59
(1 row)

EDIT: To make it even more clear, the only bit you need is CAST(CAST(col AS VARCHAR) AS UUID) - there's no direct cast from CHAR to UUID but there is one from CHAR to VARCHAR and then from VARCHAR to UUID.

[hashhar]

@ebyhr is there any good reason to not implement a cast from char to UUID since it's effectively same semantics as casting from a varchar?

[ak2766]

Is there no way this can be done using CTAS?

ChatGPT had already suggested what you too suggested, but I discounted that as an answer to my question. I believe Microsoft's DTSWizard also uses that method - to create the database first then do the data insertion. However, that method is very slow taking over 80 minutes for a small table with just over 30GB's of data. Trino with CTAS does it in under 20 minutes hence the reason I'd prefer to stick with CTAS.

If you can show me how to use insert similar to CTAS and it is just as fast as CTAS, then maybe I'm amenable to changing all the current scripts. Otherwise, I'll just bite the bullet and do the char(36) to uuid conversion after migration.

Apologies. I had to unmark your suggestion as the answer as that wasn't the question. If it cannot be done with the CTAS command, I'll adjust my expectations.

[ak2766]

Oh my, apologies @hashhar.

I just realized that with CTAS, I can do the double cast and it works as I was hoping. So this works:

CREATE TABLE postgresql.public.dummy AS
SELECT CAST(CAST(c.id AS VARCHAR) as UUID) AS id
FROM mssql.dbo.contents c

As such, I have toggled your first response as the answer. It wasn't very clear on first read but now I get it.

[hashhar]

Was just about to post this. The meat of the solution is the double cast which is needed.

We'll discuss if we can implement a CAST from CHAR to UUID too. There's been similar ask for UUID to JSON as well in the past where a two-step cast works but a single one does not.