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

SQLines SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

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

Migration Reference

Identifiers

Converting identifiers:

	SQL Server		PostgreSQL
1	tab#name	# is valid in identifiers	"tab#name"	Identifiers must be quoted
2	#table	Temporary table name starts with #	tmp_table	# is not allowed in identifiers

SQL Language Elements

Operators:

	SQL Server		PostgreSQL
1	@var += value	Addition or concatenation assignment	var := var + value	var := var \|\| value

Constants and literals:

	SQL Server		PostgreSQL
1	TRUE, 'TRUE', FALSE, 'FALSE'	Boolean values	TRUE, 'TRUE', 't', 'yes', 'on', FALSE, 'FALSE', 'f', 'no', 'off'

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 character data types:

	SQL Server		PostgreSQL
1	CHAR(n)	Fixed-length character string, n ⇐ 8000	CHAR(n)	n ⇐ 10,485,760
2	NCHAR(n)	Fixed-length Unicode UCS-2/UTF-16 string, n ⇐ 4000	CHAR(n)
3	NTEXT	Variable-length Unicode UCS-2 data, 2 GB	TEXT
4	NVARCHAR(n)	Variable-length Unicode UCS-2/UTF-16 string, n ⇐ 4000	VARCHAR(n)	n ⇐ 10,485,760
5	NVARCHAR(max)	Variable-length Unicode UCS-2/UTF-16 data, 2 GB	TEXT
6	TEXT	Variable-length character data, 2 GB	TEXT
7	VARCHAR(n)	Variable-length character string, n ⇐ 8000	VARCHAR(n)	n ⇐ 10,485,760
8	VARCHAR(max)	Variable-length character data, 2 GB	TEXT

Converting numeric data types:

	SQL Server		PostgreSQL
1	BIGINT	64-bit integer	BIGINT
2	DECIMAL(p,s)	Fixed-point number	DECIMAL(p,s)
3	DOUBLE PRECISION	Synonym for double-precision FLOAT(53)	DOUBLE PRECISION
4	FLOAT(p)	Single and double-precision floating-point number	DOUBLE PRECISION
5	INT, INTEGER	32-bit integer	INT, INTEGER
6	MONEY	64-bit currency amount	MONEY
7	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
8	REAL	Synonym for single-precision FLOAT(24)	REAL
9	SMALLINT	16-bit integer	SMALLINT
10	SMALLMONEY	32-bit currency amount	MONEY
11	TINYINT	8-bit unsigned integer, 0 to 255	SMALLINT

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), 0 ⇐ p ⇐ 6
4	DATETIMEOFFSET(p)	Date and time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
5	SMALLDATETIME	Date and time	TIMESTAMP(0)
6	TIME(p)	Time (hour, minute, second and fraction)	TIME(p)

Converting spatial data types:

	SQL Server		PostgreSQL
1	GEOGRAPHY	Geography data	GEOGRAPHY
2	GEOMETRY	Geometry data	GEOMETRY

Converting other data types:

	SQL Server		PostgreSQL
1	BINARY(n)	Fixed-length byte string	BYTEA
2	BIT	1, 0 or NULL	BOOLEAN
3	IMAGE	Variable-length binary data, ⇐ 2G	BYTEA
4	ROWVERSION	Automatically updated binary data	BYTEA
5	TIMESTAMP	Automatically updated binary data	BYTEA
6	UNIQUEIDENTIFIER	16-byte GUID (UUID) data	UUID
7	VARBINARY(n)	Variable-length byte string, 1 ⇐ n ⇐ 8000	BYTEA
8	VARBINARY(max)	Variable-length binary data, ⇐ 2G	BYTEA
9	XML	XML data	XML

Built-in SQL Functions

Converting string functions:

	SQL Server		PostgreSQL
1	CHAR(ascii_code)	Convert ASCII code to character	CHR(ascii_code)
2	CHARINDEX(substring, string)	Get substring position in string	POSITION(substring IN string)
3	CONCAT(str, str2, ...)	Concatenate strings	CONCAT(str, str2, ...)
4	CONVERT(VARCHAR, datetime, style)	Convert datetime to string	TO_CHAR(datetime, format)
5	LEFT(string, n)	Get n left characters	LEFT(string, n)
6	LEN(string)	Length in characters excluding trailing spaces	LENGTH(RTRIM(string))
7	NCHAR(codepoint)	Get Unicode character from codepoint	CHR(codepoint)
8	PATINDEX(pattern, string)	Get position of pattern in string	REGEXP_INSTR(string, pattern)
9	REPLICATE(string, n)	Repeat string n times	REPEAT(string, n)
10	REVERSE(string)	Get string in reserse order	REVERSE(string)
11	STR(num, length, decimals)	Convert decimal number to string	TO_CHAR(num, 'FM999.99')
12	STRING_AGG(str, delim) order	Aggregate concatenation	STRING_AGG(str, delim order)
13	STRING_SPLIT(str, sep)	Split string to rows	UNNEST(STRING_TO_ARRAY(str, sep))
14	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	CURRENT_TIMESTAMP	Get the current date and time	CURRENT_TIMESTAMP
3	CONVERT(TIME, expr)	Converts expr to TIME	CAST(expr AS TIME)
4	DATEADD	Add an interval to datetime	INTERVAL expression
5	DATEDIFF(units, start, end)	Get datetime difference in units	EXTRACT and INTERVAL expressions
5	DATEDIFF_BIG(units, start, end)	Get datetime difference in units	EXTRACT and INTERVAL expressions
6	DATENAME(unit, datetime)	Extract unit from datetime as string	TO_CHAR(datetime, format)
7	DATEPART(unit, datetime)	Extract unit from datetime as number	DATE_PART('unit', datetime)
8	DAY(datetime)	Get the day of datetime	EXTRACT(DAY FROM datetime)
9	GETDATE()	Get the current date and time	NOW()
10	MONTH(datetime)	Get the month (1-12) of datetime	EXTRACT(MONTH FROM datetime)
11	SYSDATETIMEOFFSET()	Get the current datetime with time zone	NOW()
12	YEAR(datetime)	Get the year of datetime	EXTRACT(YEAR FROM datetime)

Numeric functions:

	SQL Server		PostgreSQL
1	CEILING(exp)	Round up to the nearest integer	CEILING(exp)
2	FLOOR(exp)	Round down to the nearest integer	FLOOR(exp)
3	ROUND(exp, len, trunc)	Round to specified precision	ROUND(exp, len)	TRUNC(exp, len)

Data type conversion functions:

	SQL Server		PostgreSQL
1	CAST(exp AS datatype)	Convert expression to another data type	CAST(exp AS datatype)

NULL handling functions:

	SQL Server		PostgreSQL
1	ISNULL(exp, replacement)	Replace NULL with the specified value	COALESCE(exp, replacement)
2	NULLIF(exp1, exp2)	Return NULL if exp1 is equal to exp2	NULLIF(exp1, exp2)

Math functions:

	SQL Server		PostgreSQL
1	ATAN(exp)	Arctangent	ATAN(exp)
2	ATN2(exp, exp2)	Arctangent of two numbers	ATAN2(exp)
3	COS(exp)	Cosine	COS(exp)
4	DEGREES(exp)	Convert radians to degrees	DEGREES(exp)
5	POWER(exp, p)	Raise to specified power	POWER(exp, p)
6	RADIANS(exp)	Convert degrees to radians	RADIANS(exp)
7	SIN(exp)	Sine	SIN(exp)
8	TAN(exp)	Tangent	TAN(exp)

Logical functions:

	SQL Server		PostgreSQL
1	IIF(cond, exp1, exp2)	Conditional expression	CASE WHEN cond THEN exp1 ELSE exp2 END

Identity functions:

	SQL Server		PostgreSQL
1	SCOPE_IDENTITY()	Get last inserted ID	INSERT with RETURNING

Aggregation and window functions:

	SQL Server		PostgreSQL
1	COUNT(exp)	Count of rows	COUNT(exp)
	COUNT_BIG(exp)
2	ROW_NUMBER() OVER (...)	Number rows	ROW_NUMBER() OVER (...)
3	STDEV(exp)	Get standard deviation	STDDEV(exp)

Transaction control functions:

	SQL Server		PostgreSQL
1	XACT_STATE()	Check if transaction is active	PG_CURRENT_XACT_ID_IF_ASSIGNED()

System functions:

	SQL Server		PostgreSQL
1	SERVERPROPERTY(property)	Get server property	Various functions and expressions

Other functions:

	SQL Server		PostgreSQL
1	GROUPING_ID(col,...)	Get GROUP BY level	GROUPING(col,...)

SELECT Statement

Converting SQL queries:

	SQL Server		PostgreSQL
1	SELECT ... INTO #tmp_table	Create a temporary table using SELECT	SELECT ... INTO TEMPORARY tmp_table
2	SELECT alias = expr ...	Non-standard column alias form	SELECT expr alias ...
3	SELECT ... FROM func()	Select from function	SELECT ... FROM func()
4	CROSS JOIN	Cartesian product	CROSS JOIN
5	CROSS APPLY	Correlated inner join	CROSS JOIN LATERAL
6	OUTER APPLY	Correlated outer join	LEFT OUTER JOIN LATERAL
7	PIVOT	Pivoting rows into columns	CASE expressions
8	GROUPING SETS	Grouping expressions	GROUPING SETS

Row limitation:

	Oracle		PostgreSQL
1	SELECT TOP n … FROM …	Select n rows only	SELECT … FROM … LIMIT n
2	OFFSET k ROWS FETCH FIRST n ROWS ONLY	Row limiting	OFFSET k LIMIT n

Quering XML data:

	SQL Server		PostgreSQL
1	SELECT ... FOR XML PATH	Get query result as XML	SELECT with XMLAGG, XMLELEMENT and XMLFOREST
2	SELECT ... FROM @xml.nodes(xpath)	Select XML items as rows	SELECT with XPATH and UNNEST

CREATE INDEX Statement

Converting indexes:

	SQL Server		PostgreSQL
1	CREATE [UNIQUE] INDEX name ON tab	Create an index	CREATE [UNIQUE] INDEX name ON tab cols
2	(col [ASC \| DESC], …)	Index columns	(col [ASC \| DESC], …)
3	INCLUDE (ncol, … )	Non-key columns to include	INCLUDE (ncol, … )

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		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
8	EXECUTE('SELECT...')	Return a dynamic result set	OPEN cursor FOR EXECUTE '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	@param [AS] datatype = default		p_param datatype = default
3	()	Required for empty parameters	()
4	RETURNS datatype(len)		RETURNS datatype(len)
5	RETURNS TABLE	Table valued function	RETURNS TABLE
6	Optional AS before the function body		AS $$

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 expr FROM ...)	var := (SELECT expr FROM ...)
4	SET @var = (SELECT expr)	var := (SELECT expr)
5	SELECT @var = exp, @var2 = exp2 FROM …	SELECT exp, exp2 INTO var, var2 FROM …

Cursors declarations, operations and attributes:

	SQL Server		PostgreSQL
1	DECLARE cur CURSOR FOR select	Cursor declaration	cur CURSOR FOR select
2	OPEN cur	Open cursor	OPEN cur
3	FETCH cur INTO var, …	Fetch row	FETCH cur INTO var, …
3	FETCH NEXT FROM cur …	Fetch row	FETCH NEXT FROM cur …
4	@@FETCH_STATUS	Status of last fetch	FOUND
5	CLOSE cur	Close cursor	CLOSE cur
6	DEALLOCATE cur	Deallocate cursor	Removed

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
2	RETURN exp	Return value from function	RETURN exp

Calling user-defined functions:

	SQL Server		PostgreSQL
1	func(DEFAULT, DEFAULT)	Using default parameters	func()	DEFAULT keyword is not allowed

Stored procedure calls:

	SQL Server		PostgreSQL
1	EXEC sp_name @param1 = value1,...	Execute procedure	CALL sp_name(p_param1 => value1,...)
2	EXEC sp_name	Procedure without parameters	CALL sp_name()

Transaction control statements:

	SQL Server		PostgreSQL
1	BEGIN TRANSACTION	Start transaction	BEGIN TRANSACTION	Not allowed in procedure
2	COMMIT	Commit transaction	COMMIT
3	ROLLBACK	Rollback transaction	ROLLBACK

Exception block:

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

SQL Statements

Converting SQL statements:

	SQL Server		PostgreSQL
1	DELETE [FROM] tab …	Delete rows	DELETE FROM tab …	FROM keyword is required
2	DROP TABLE [IF EXISTS] name	Drop table	DROP TABLE [IF EXISTS] name
3	INSERT [INTO] table …	Insert a row	INSERT INTO table …	INTO keyword is required
4	MERGE [INTO] tab …	Update or insert rows	MERGE INTO tab …	INTO keyword is required
5	UPDATE alias SET … FROM tab alias, tab2, … WHERE …	Update from another table	UPDATE tab alias SET … FROM tab2, … WHERE …
6	USE name	Change the database	SET SCHEMA 'name'

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 Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference