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 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	DATE	Date (year, month and day)	DATE
6	DATETIME	Date and time with fraction	TIMESTAMP(3)
7	DATETIME2(p)	Date and time with fraction	TIMESTAMP(p)
8	DATETIMEOFFSET(p)	Date and time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
9	DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
10	DOUBLE PRECISION	Double-precision floating-point number	DOUBLE PRECISION
11	FLOAT(p)	Floating-point number	DOUBLE PRECISION
12	IMAGE	Variable-length binary data, ⇐ 2G	BYTEA
13	INT, INTEGER	32-bit integer	INT, INTEGER
14	MONEY	64-bit currency amount	MONEY
15	NCHAR(n)	Fixed-length Unicode UCS-2 string	CHAR(n)
16	NTEXT	Variable-length Unicode UCS-2 data, ⇐ 2G	TEXT
17	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
18	NVARCHAR(n)	Variable-length Unicode UCS-2 string	VARCHAR(n)
19	NVARCHAR(max)	Variable-length Unicode UCS-2 data, ⇐ 2G	TEXT
20	REAL	Single-precision floating-point number	REAL
21	ROWVERSION	Automatically updated binary data	BYTEA
22	SMALLDATETIME	Date and time	TIMESTAMP(0)
23	SMALLINT	16-bit integer	SMALLINT
24	SMALLMONEY	32-bit currency amount	MONEY
25	TEXT	Variable-length character data, ⇐ 2G	TEXT
26	TIME(p)	Time (hour, minute, second and fraction)	TIME(p)
27	TIMESTAMP	Automatically updated binary data	BYTEA
28	TINYINT	8-bit unsigned integer, 0 to 255	SMALLINT
29	UNIQUEIDENTIFIER	16-byte GUID (UUID) data	CHAR(16)
30	VARBINARY(n)	Variable-length byte string, 1 ⇐ n ⇐ 8000	BYTEA
31	VARBINARY(max)	Variable-length binary data, ⇐ 2G	BYTEA
32	VARCHAR(n)	Variable-length character string, 1 ⇐ n ⇐ 8000	VARCHAR(n)
33	VARCHAR(max)	Variable-length character data, ⇐ 2G	TEXT
34	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	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	TO_CHAR(datetime, format)
6	DAY(datetime)	Get the day of datetime	EXTRACT(DAY FROM datetime)
7	GETDATE()	Get the current date and time	NOW()
8	MONTH(datetime)	Get the month (1-12) of datetime	EXTRACT(MONTH FROM datetime)
9	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)

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 ...

DELETE Statement

Converting DELETE statement:

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

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

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.

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
1	IF … ELSE IF …	IF ELSE IF statement	IF THEN … ELSIF … END IF

Exception block:

	SQL Server		PostgreSQL
1	BEGIN TRY … END TRY BEGIN CATCH … END CATCH	Exception block	BEGIN … EXCEPTION … 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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to SingleStore

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference