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
DEFAULT uuid_generate_v1()
,
equip_name varchar(255));
|
CREATE TABLE equipment(
equip_id uniqueidentifier
PRIMARY
DEFAULT NEWID() ,
equip_name varchar(255));
CREATE TABLE equipment(
equip_id uniqueidentifier
PRIMARY
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
DEFAULT LOWER(
REPLACE( CAST(
uuid_generate_v1() As varchar(50))
, '-','')
) , equip_name
varchar(255));
|
CREATE TABLE equipment(
equip_id char(32) PRIMARY
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 <> And then assign the default value for the column to use this sequence, using thenextval function:CREATE TABLE (INCREMENT <> ; 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
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
SET
SET QUOTED_IDENTIFIER ON GO
SET
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
([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
SET
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+
