Transforming specific column from int(sqlserver) to UUID(postgres)

[pan15jain]

Belowis my mssql.load file.

load database from mssql://sa:yourStrong(!)Password@host.docker.internal:51315/SiteDb_Test_d242baf3-47d9-4108-a562-16645f54d210 into pgsql://postgres:yourStrong(!)Password@host.docker.internal:51235/sitedb_test_b5416f54_c62a_4c8a_b422_165821dcd578

WITH include no drop, create no tables, create no indexes, reset no sequences, create no schemas, no foreign keys, no truncate

SET work_mem to '128MB', maintenance_work_mem to '512 MB'

MATERIALIZE VIEWS view_pointhistory AS $$ SELECT IntervalEndTime, PointId, AvgValue, MinValue, MaxValue, LastValue, NumReadings, IntervalDataQuality AS DataQuality, IntervalSeconds FROM PointHistory $$, view_pointchanges AS $$ SELECT ChangeEventTime as EventTime, PointId, Value, PreviousValue, DataQuality FROM PointChanges $$

ALTER TABLE NAMES MATCHING 'PointHistory' RENAME TO 'skip_pointhistory' ALTER TABLE NAMES MATCHING 'PointChanges' RENAME TO 'skip_pointchanges'

ALTER TABLE NAMES MATCHING 'view_pointhistory' RENAME TO 'pointhistory' ALTER TABLE NAMES MATCHING 'view_pointchanges' RENAME TO 'pointchanges'

EXCLUDING TABLE NAMES LIKE 'skip_pointhistory', 'skip_pointchanges' in schema 'dbo'

ALTER schema 'dbo' rename to 'public'

CAST type tinyint to smallint, type float to float using float-to-string, type real to real using float-to-string, --type double to double precision using float-to-string, type numeric to numeric using float-to-string, type decimal to numeric using float-to-string, type money to numeric using float-to-string, type smallmoney to numeric using float-to-string,

type char      to text drop typemod,
type nchar     to text drop typemod,
type varchar   to text drop typemod,
type nvarchar  to text drop typemod,
type xml       to text drop typemod,

type binary    to bytea using byte-vector-to-bytea,
type varbinary to bytea using byte-vector-to-bytea,

type datetime    to timestamptz,
type datetime2   to timestamptz,

type bit to boolean,
type hierarchyid to bytea,
type geography to bytea,
type uniqueidentifier to uuid using sql-server-uniqueidentifier-to-uuid,

column alarmdefinitions.SiteId to uuid using sql-server-uniqueidentifier-to-uuid

before load do $$ drop schema if exists dbo cascade; $$;

I need to convert my siteid column of alarmdefinitions table to uuid while converting to postgres. below is my code. column alarmdefinitions.SiteId to uuid using sql-server-uniqueidentifier-to-uuid I am getting the below error 2024-05-13T21:54:15.569999Z ERROR Error while formatting a row from "public.alarmdefinitions": 2024-05-13T21:54:15.569999Z ERROR The value 100 is not of type (OR NULL (VECTOR (UNSIGNED-BYTE 8) 16)) when binding PGLOADER.TRANSFORMS::ID 2024-05-13T21:54:15.569999Z ERROR The value NIL is not of type NUMBER