Azure SQL UUID to Postgres UUID

[jpmeijers]

Some of my constraints ended up in tables-unsure.sql. I manually fixed all the issues and the sql script ran without a problem. I however did not check that all the constraints work as expected. For interest sake I attach the original and the modified unsure.sql files.
tables-unsure-modified.sql.txt
tables-unsure.sql.txt

[jpmeijers]

(closing this as it is not a bug, but info for reference purposes)

[madtibo]

Thanks for the output. This is quite interesting!

I will try to detect the newid() function and transform it in uuid_generate_v4(). I then need to add the create extension...

There are as well 2 checks that I would like to try to correct:

ALTER TABLE "community"."communityperiod" ADD CONSTRAINT "ck_communityperiod_startend" CHECK ((start]<[end));
ALTER TABLE "device"."deviceport" ADD CONSTRAINT "ck_deviceport_opcode" CHECK ((len(opcode)=(2) OR ((opcode='e' OR (opcode='d' OR (opcode='c' OR (opcode='b' OR opcode='a')))))));

corrected to:

ALTER TABLE "community"."communityperiod" ADD CONSTRAINT "ck_communityperiod_startend" CHECK (("start"<"end"));
ALTER TABLE "device"."deviceport" ADD CONSTRAINT "ck_deviceport_opcode" CHECK ((LENGTH(opcode)=(2) OR ((opcode='e' OR (opcode='d' OR (opcode='c' OR (opcode='b' OR opcode='a')))))));

Can you give me the table definitions for these constraints?

[jpmeijers]

Summary of changes I had to make:

1. Remove ] and [
2. Quote end
3. len to LENGTH
4. newid() to uuid_generate_v4()
5. Add CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; to allow the use of uuids.
6. Cast uuid to varchar: uuid_generate_v4()::varchar

Without the cast to varchar I get an error:

ALTER TABLE "community"."community" ALTER COLUMN "communityguid" SET DEFAULT replace(uuid_generate_v4(),'-','')
[2021-01-14 18:51:51] [42883] ERROR: function replace(uuid, unknown, unknown) does not exist
[2021-01-14 18:51:51] Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I believe the issue here is that we used a [char](32) column in SQL Server for our GUID, rather than a UNIQUEIDENTIFIER. And in Postgres the replace function can't be used on a uuid, as it is not a string. We therefore need to cast. I don't know if sqlserver2pgsql should or want to handle this edge case.

---

I believe these are the definition you requested, from the original SQL Server export:

ALTER TABLE [Community].[CommunityPeriod]  WITH CHECK ADD  CONSTRAINT [CK_CommunityPeriod_StartEnd] CHECK  (([Start]<[End]))
GO
...
ALTER TABLE [Device].[DevicePort]  WITH CHECK ADD  CONSTRAINT [CK_DevicePort_OpCode] CHECK  ((len([OpCode])=(2) OR ([OpCode]='e' OR [OpCode]='d' OR [OpCode]='c' OR [OpCode]='b' OR [OpCode]='a')))
GO

/****** Object:  Table [Community].[CommunityPeriod]    Script Date: 2021/01/14 14:33:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Community].[CommunityPeriod](
	[CommunityPeriodID] [int] IDENTITY(1,1) NOT NULL,
	[CommunityPeriodGUID] [char](32) NOT NULL,
	[CommunityID] [int] NOT NULL,
	[Start] [datetime] NOT NULL,
	[End] [datetime] NOT NULL,
...
 CONSTRAINT [PK_CommunityPeriod] PRIMARY KEY CLUSTERED 
(
	[CommunityPeriodID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Device].[DevicePort](
	[DevicePortID] [int] IDENTITY(1,1) NOT NULL,
	[DevicePortGUID] [char](32) NOT NULL,
	[DeviceID] [int] NOT NULL,
	[OpCode] [nvarchar](2) NOT NULL,
...
 CONSTRAINT [PK_DevicePort] PRIMARY KEY CLUSTERED 
(
	[DevicePortID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO