Manual Migration SQLSERVER to PostgreSQL

Following article explains a manual migration of a small SQL Server database structure to a postgres SQL
Create table scripts 

1.       Force to lower case (Notepad++  -> edit -> convert case to -> lower)

2.       Remove all square brackets ( [ and ] ),  owner prefixes (i.e. "dbo.").

3.       Remove all non-supported keywords (i.e. "WITH NOCHECK", "WITH CHECK",  "CLUSTERED", “ASC”, “with fillfactor = 90”, “ON PRIMARY”, “TEXTIMAGE_ON”)

4.       Remove unsupported commands (entire rows) using  following regular expression (Notepad++ for small scripts)

· ^.*//******.*$

· ^.*SET ANSI_NULLS.*$

· ^.*SET QUOTED_IDENTIFIER.*$

5.       Replace the T-SQL batch terminator "GO" with the PostgreSQL batch terminator ";" and manually remove additional “;”  OR remove all “GO” statements and add “;” for each sql statement.

6.       Replace all non-supported data types

• Nvarchar, text, ntext -- varchar
• DATETIME, Smalldatetime -- TIMESTAMP
• MONEY -- NUMERIC(19,4)
• Bit (Boolean type) -- smallint or boolean
• Smalldatetime -- TIMESTAMP
• Float -- bigint
• decimal -- numeric
• Tinyint -- smallint
• Int -- integer
• Image -- bytea

7.       uniqueidentifier data type

Operation	PostgreSQL	SQL Server
Create a table that has a UUID/GUID type automatically assigned	CREATE TABLE equipment( equip_id uuid PRIMARY KEY         DEFAULT uuid_generate_v1() , equip_name  varchar(255));	CREATE TABLE equipment(     equip_id uniqueidentifier PRIMARY KEY DEFAULT NEWID() , equip_name varchar(255)); CREATE TABLE equipment( equip_id uniqueidentifier PRIMARY KEY ROWGUIDCOL , equip_name varchar(255) );
Create a table that has text representation of UUID/GUID as primary for easier transportation	CREATE TABLE equipment( equip_id char(32) PRIMARY KEY DEFAULT LOWER( REPLACE(  CAST( uuid_generate_v1() As varchar(50))  , '-','')  ) ,  equip_name varchar(255));	CREATE TABLE equipment(     equip_id char(32) PRIMARY KEY DEFAULT LOWER( REPLACE( CAST(NEWID() As varchar(50)), '-','') ) , equip_name varchar(255));

8.   Instead of int IDENTITY(1,1) for an auto incrementing column, Postgres uses sequences, the easiest way to transform this is to use the serial (or bigserial for 8 byte numbers) data type If you need to start the sequence at a specified seed or have it decrement or increment by more than 1, you must create the sequence manually.

CREATE SEQUENCE <> START <>  INCREMENT <> ;
And then assign the default value for the column to use this sequence, using thenextval function:CREATE TABLE ( int default nextval('<>') not null;)

9. Find “DEFAULT (0)” value and add that manually at the end of relevant field in relevant table. Then remove “ALTER TABLE ……….. CHECK CONSTRAINT ……… DEFAULT (0) FOR …….” Rows

10. Remove “ALTER TABLE ……….. CHECK CONSTRAINT ………” rows

11.Change the index statements

SQL Server index

create nonclustered index ix_tblstudiesfieldstatus on studies (field_status_id );

Postgres Index

CREATE INDEX ix_tblstudiesfieldstatus  ON ccadmin.studies USING tree( field_status_id ); ALTER TABLE ccadmin.studies CLUSTER ON times_idx;

12.   Replace “getdate()” with “now()”

13.   Replace “len” with “length” 

Example

SQL Server Table

Postgre SQL Table

/****** Object:  Table [dbo].[WEBINC_TRACK]    Script Date: 05/03/2012 11:07:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[WEBINC_TRACK]( [SESSIONID] [uniqueidentifier] NOT NULL CONSTRAINT  [DF_WEBINC_TRACK_SESSIONID]  DEFAULT (newid()), [ID] [int] IDENTITY(1,1) NOT NULL, [TrackDate] [smalldatetime] NOT NULL CONSTRAINT [DF_WEBINC_TRACK_TrackDate]  DEFAULT (getdate()),  [pathinfo] [varchar](1000) NULL, [loginpathinfo] [varchar](1000) NULL, [EMAILSENT] [tinyint] NULL, CONSTRAINT [PK_WEBINC_TRACK] PRIMARY KEY CLUSTERED ([ID] ASC )WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] GO

Drop table if exists webinc_track; create table webinc_track( sessionid varchar(36) not null constraint df_webinc_track_sessionid default upper(cast(uuid_generate_v4() as varchar(36))), id serial not null, trackdate TIMESTAMP not null constraint df_webinc_track_trackdate  default (now()), puin varchar(255) null, pathinfo varchar(1000) null, loginpathinfo varchar(1000) null, emailsent smallint null,  constraint pk_webinc_track primary key  (id));

SQL Server Function

Postgre SQL function

/****** Object:  UserDefinedFunction [dbo].[GetGenderFromSalutation]    Script Date: 05/09/2012 16:52:18 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION  [dbo].[GetGenderFromSalutation](@SalutationID smallint) RETURNS smallint  AS  BEGIN declare @result smallint set @result = 0 if @SalutationID in (3,4,7,2)                  set @result =  2 else if (@SalutationID = 1)                 set @result =  1 return @result END

CREATE OR REPLACE FUNCTION GetGenderFromSalutation(SalutationID smallint) RETURNS smallint  AS $BODY$ DECLARE                 result smallint := 0 ; BEGIN IF (SalutationID in (3,4,7,2)) THEN                 result :=  2; ELSIF (SalutationID = 1) THEN                 result :=  1; END IF; RETURN result; END; $BODY$ LANGUAGE plpgsql VOLATILE

Procedures and functions

Apply same rules for procedures and function as applied in table creation scripts. Additionally follow following point.

1)      Instead of “create function” use “Create or replace function”

2)      Add “AS $BODY$ DECLARE” after returning data type at beginning

3)      Remove individual declare phase from variable declaration and add “;” at end of each variable declaration statement.

4)      Add “then”  and “END IF;” word in all if conditional blocks. Further replace “else if” phase with “ELSIF”

5)      Remove “SET” and “@” words

6)      Add “$BODY$  LANGUAGE plpgsql VOLATILE” at the end of the function

7) PostgreSQL functions do not support output parameters, avoid them.

8) PostgreSQL functions do not support default values, all parameters must be passed to the function (although function overtyping is supported, allowing wrapper functions that allow defaults).

9) Use FOUND instead of @@FETCH_STATUS. Found is true if the last SELECT, SELECT INTO, or FETCH statement returned a value.

10) Instead of @@IDENTITY use currval(sequence name) by default the sequence name is __seq.

11) Remove DEALLOCATE cursor statements, the function is accomplished with CLOSE and DEALLOCATE has a different use.

12) Use PERFORM instead of EXEC to call stored procedures/functions.

13) Forvariable declarations, SELECT foo = foo column won't work. Use either SELECT INTO or the PL/pgsql definition := (as in foo := foocolumn.)

14) Error handling is different, when a PL/pgsql program encounters a nerror the execution is immediately stopped(can't check @@ERROR) or execution isnt to the EXCEPTION block (if it exists) after the current BEGIN block. Thus, if you want to return specific error values, you will need an EXCEPTION block.

Application level changes

1.Change "SELECT TOP 10 * FROM table" queries in to "SELECT * FROM table LIMIT 10" structure

2. LIKE statements are case sensitive in postgresql

3. concatination so "SELECT firstname + ' ' + lastname AS fullname" becomes "SELECT firstname || ' ' || lastname AS fullname"

Reference - http://www.postgresonline.com/journal/archives/179-Universal-Unique-Identifiers-PostgreSQL-SQL-Server-Compare.html+

http://wiki.postgresql.org/images/e/e4/5.pdf