Microsoft SQL Server (MS SQL) to PostgreSQL Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server (MSSQL, MS SQL), Azure SQL Database, Azure Synapse to PostgreSQL (Postgres).

Databases:

  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000
  • PostgreSQL 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

SQL Language Elements

Converting SQL language elements:

SQL Server PostgreSQL
1 SELECT c1 AS 'alias' Single-quoted aliases SELECT c1 AS "alias" Double quotes only
2 @@ROWCOUNT Get the number of affected rows ROW_COUNT

Data Types

Converting date and time data types:

SQL Server PostgreSQL
1 DATE Date (year, month and day) DATE
2 DATETIME Date and time with fraction (milliseconds) TIMESTAMP(3)
3 DATETIME2(p) Date and time with fraction, 0 <= p <= 7, default is 7 TIMESTAMP(p)
4 DATETIMEOFFSET(p) Date and time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE
5 SMALLDATETIME Date and time TIMESTAMP(0)

Converting data types:

SQL Server PostgreSQL
1 BIGINT 64-bit integer BIGINT
2 BINARY(n) Fixed-length byte string BYTEA
3 BIT 1, 0 or NULL BOOLEAN
4 CHAR(n), CHARACTER(n) Fixed-length character string, 1 ⇐ n ⇐ 8000 CHAR(n), CHARACTER(n)
5 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
6 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
7 FLOAT(p) Floating-point number DOUBLE PRECISION
8 IMAGE Variable-length binary data, ⇐ 2G BYTEA
9 INT, INTEGER 32-bit integer INT, INTEGER
10 MONEY 64-bit currency amount MONEY
11 NCHAR(n) Fixed-length Unicode UCS-2 string CHAR(n)
12 NTEXT Variable-length Unicode UCS-2 data, ⇐ 2G TEXT
13 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
14 NVARCHAR(n) Variable-length Unicode UCS-2 string VARCHAR(n)
15 NVARCHAR(max) Variable-length Unicode UCS-2 data, ⇐ 2G TEXT
16 REAL Single-precision floating-point number REAL
17 ROWVERSION Automatically updated binary data BYTEA
18 SMALLINT 16-bit integer SMALLINT
19 SMALLMONEY 32-bit currency amount MONEY
20 TEXT Variable-length character data, ⇐ 2G TEXT
21 TIME(p) Time (hour, minute, second and fraction) TIME(p)
22 TIMESTAMP Automatically updated binary data BYTEA
23 TINYINT 8-bit unsigned integer, 0 to 255 SMALLINT
24 UNIQUEIDENTIFIER 16-byte GUID (UUID) data CHAR(16)
25 VARBINARY(n) Variable-length byte string, 1 ⇐ n ⇐ 8000 BYTEA
26 VARBINARY(max) Variable-length binary data, ⇐ 2G BYTEA
27 VARCHAR(n) Variable-length character string, 1 ⇐ n ⇐ 8000 VARCHAR(n)
28 VARCHAR(max) Variable-length character data, ⇐ 2G TEXT
29 XML XML data XML

Built-in SQL Functions

Converting string functions:

SQL Server PostgreSQL
1 CHARINDEX(substring, string) Get substring position in string POSITION(substring IN string)
2 CONVERT(VARCHAR, datetime, style) Convert datetime to string TO_CHAR(datetime, format)
3 LEN(string) Length in characters excluding
trailing spaces
LENGTH(RTRIM(string))
4 STR(num, length, decimals) Convert decimal number to string TO_CHAR(num, 'FM999.99')
5 SUBSTRING(string, start, length) Return substring SUBSTRING(string, start, length)

Datetime functions:

SQL Server PostgreSQL
1 CONVERT(DATETIME, expr, style) Converts expr to datetime TO_TIMESTAMP(expr, format)
2 CONVERT(TIME, expr) Converts expr to TIME CAST(expr AS TIME)
3 DATEADD Add an interval to datetime INTERVAL expression
4 DATEDIFF(units, start, end) Get datetime difference in specified units INTERVAL expression
5 DATENAME(unit, datetime) Extract unit from datetime as string TO_CHAR(datetime, format)
6 DATEPART(unit, datetime) Extract unit from datetime as number DATE_PART('unit', datetime)
7 DAY(datetime) Get the day of datetime EXTRACT(DAY FROM datetime)
8 GETDATE() Get the current date and time NOW()
9 MONTH(datetime) Get the month (1-12) of datetime EXTRACT(MONTH FROM datetime)
10 YEAR(datetime) Get the year of datetime EXTRACT(YEAR FROM datetime)

Numeric functions:

SQL Server PostgreSQL
1 CONVERT(BIGINT, exp) Convert to integer CAST(exp AS BIGINT)
CONVERT(INT | INTEGER, exp) CAST(exp AS INT | INTEGER)
CONVERT(SMALLINT, exp) CAST(exp AS SMALLINT)
CONVERT(TINYINT, exp) CAST(exp AS SMALLINT)
2 CONVERT(NUMERIC(p,s), exp) Convert to number CAST(exp AS NUMERIC(p,s))

NULL handling functions:

SQL Server PostgreSQL
1 ISNULL(exp, replacement) Replace NULL with the specified value COALESCE(exp, replacement)

CREATE TABLE Statement

Converting table definitions:

SQL Server PostgreSQL
1 IDENTITY(start, increment) Identity column GENERATED ALWAYS AS IDENTITY
(INCREMENT BY increment START WITH start)
since PostgreSQL 10
CREATE SEQUENCE and DEFAULT NEXTVAL
IDENTITY can be defined on
DECIMAL/NUMERIC columns
Integer columns must be used
3 PRIMARY KEY (col ASC, … ) Primary key PRIMARY KEY (col, … ) ASC, DESC cannot
be specified

Temporary tables:

SQL Server PostgreSQL
1 CREATE TABLE #name Temporary table name starts with # CREATE TEMPORARY TABLE name

ALTER TABLE Statement

Converting table modifications:

SQL Server PostgreSQL
1 ALTER TABLE name
ADD CONSTRAINT cns
DEFAULT expr FOR col
Add default for a column ALTER TABLE name ALTER COLUMN col
SET DEFAULT expr
2 ALTER TABLE name WITH CHECK
ADD CONSTRAINT …
Check existing data WITH CHECK removed, it's default
3 ALTER TABLE name NOCHECK
ADD CONSTRAINT …
Don't check existing data ALTER TABLE name ADD CONSTRAINT …
NOT VALID
4 ALTER TABLE name
CHECK CONSTRAINT cns
Validate the constraint ALTER TABLE name
VALIDATE CONSTRAINT cns

SELECT Statement

Converting SQL queries:

SQL Server PostgreSQL
1 SELECT TOP n … FROM … Select n rows only SELECT … FROM … LIMIT n
2 SELECT ... INTO #tmp_table Create a temporary table using SELECT SELECT ... INTO TEMPORARY tmp_table
3 SELECT alias = expr ... Non-standard column alias form SELECT expr alias ...
4 PIVOT clause Pivoting rows into columns CASE expressions
5 OFFSET n ROWS
FETCH NEXT k ROWS ONLY
Rows offset and limit OFFSET n LIMIT k

DELETE Statement

Converting DELETE statement:

SQL Server PostgreSQL
1 DELETE [FROM] tab Delete rows DELETE FROM tab FROM keyword is required

MERGE Statement

Converting MERGE statement:

SQL Server PostgreSQL
1 MERGE [INTO] tab Update or insert rows MERGE [INTO] tab INTO keyword is required

CREATE PROCEDURE Statement

Converting Transact-SQL stored procedures functions:

SQL Server PostgreSQL
1 CREATE PROCEDURE | ALTER PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 @param [AS] datatype = default OUT | OUTPUT p_param IN | INOUT datatype = default
3 Optional () for procedure parameters () required
4 AS Changed AS $$
5 Declarations inside BEGIN block DECLARE block is between AS and BEGIN clauses
6 END End of procedure block END; $$ LANGUAGE plpgsql;
7 Standalone SELECT Return a result set OPEN cursor FOR SELECT

For further information, see Conversion of Transact-SQL Statements.

CREATE FUNCTION Statement

Converting Transact-SQL user-defined functions:

SQL Server PostgreSQL
1 CREATE FUNCTION | ALTER FUNCTION name CREATE OR REPLACE FUNCTION name
2 RETURNS TABLE Table valued function RETURNS TABLE

For further information, see Conversion of Transact-SQL Statements.

CREATE TRIGGER Statement

Converting triggers:

SQL Server PostgreSQL
1 CREATE TRIGGER Create a trigger CREATE FUNCTION … RETURNS TRIGGER
CREATE TRIGGER … EXECUTE FUNCTION
2 AFTER UPDATE, INSERT, DELETE List of events AFTER UPDATE OR INSERT OR DELETE
3 ON table AFTER INSERT … Table name before event AFTER INSERT ON table

Transact-SQL Statements

Converting Transact-SQL statements.

Variable declaration and assignment:

SQL Server PostgreSQL
1 DECLARE @var [AS] type [= default_value] DECLARE var type [= | := | DEFAULT default_value]
2 DECLARE @tab [AS] TABLE (…) CREATE TEMPORARY TABLE tab (…)
3 SET @var = expression var := expression
4 SET @var = (SELECT expression FROM …) var := (SELECT expression FROM …)
5 SELECT @var = exp, @var2 = exp2 FROM … SELECT exp, exp2 INTO var, var2 FROM …

Flow-of-control statements:

SQL Server PostgreSQL
1 IF condition BEGIN … END IF statement IF condition THEN … END IF
IF … ELSE IF … IF ELSE IF statement IF THEN … ELSIF … END IF

Cursors operations and attributes:

SQL Server PostgreSQL
1 CLOSE cur Close cursor CLOSE cur
2 DEALLOCATE cur Deallocate cursor Removed

Exception block:

SQL Server PostgreSQL
1 BEGIN TRY … END TRY
BEGIN CATCH … END CATCH
Exception block BEGIN …
EXCEPTION WHEN OTHERS THEN …
END

Stored procedure calls:

SQL Server PostgreSQL
1 EXEC sp_name @param1 = value1,… Execute a procedure CALL sp_name(p_param1 => value1,…)

SQL Statements

Converting SQL statements from SQL Server to PostgreSQL:

SQL Server PostgreSQL
1 INSERT [INTO] table Insert a row INSERT INTO table INTO keyword is required
2 USE name Change the database SET SCHEMA 'name' If databases are mapped to schemas

System Procedures

Converting system procedure calls from SQL Server to PostgreSQL:

SQL Server PostgreSQL
1 sp_addextendedproperty 'MS_Description',
'Table comment', … 'table', 'tab_name'
Comment on table COMMENT ON TABLE tab_name IS 'Table comment'
2 sp_addextendedproperty 'MS_Description',
'Column comment', … 'table', 'tab_name',
'column', 'col_name'
Comment on column COMMENT ON COLUMN tab_name.col_name
IS 'Column comment'
3 sp_addextendedproperty 'MS_DiagramPane1', … Removed
4 sp_addextendedproperty 'MS_DiagramPaneCount', … Removed