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.
Databases:
Microsoft
SQL Server 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000
PostgreSQL 14.x, 13.x, 12.x, 11.x, 10.x and 9.x
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 |
Converting SQL functions:
| SQL Server | PostgreSQL |
1 | DATEADD | Add an interval to datetime | INTERVAL expression |
2 | ISNULL(exp, replacement) | Replace NULL with the specified value | COALESCE(exp, replacement) |
Converting table definitions:
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 | NOCHECK
ADD CONSTRAINT … | Check existing data | WITH CHECK | NOCHECK options
not supported, removed |
Converting Transact-SQL statements from SQL Server to PostgreSQL:
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 (…) |
Converting SQL statements from SQL Server to PostgreSQL:
| SQL Server | PostgreSQL |
1 | USE name | Change the database | SET SCHEMA 'name' | If databases are mapped to schemas |
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' |