SQL Server to PostgreSQL
SQL Server to PostgreSQL migration
Last updated:
Datalobe assesses your SQL Server source, converts T-SQL procedures, functions and triggers to PL/pgSQL, recreates views, compiles every object on PostgreSQL before deploy, copies data with change data capture (CDC) until cut-over, and validates every table with 17 checks in six groups.
Source
SQL Server
- Code
- T-SQL
Target
PostgreSQL
- Code
- PL/pgSQL
Conversion
What converts automatically, and what needs review?
T-SQL procedures, functions and triggers convert to PL/pgSQL, and views are recreated.
Fixed rules convert tables, types and keys. AI converts stored code, every AI conversion is checked, and anything that cannot be proven goes to a named person.
Every converted statement is compile-checked on PostgreSQL before deploy: Datalobe replays it inside a transaction that is rolled back.
Assessment
Which differences does the assessment look for?
Behaviour differences, such as a procedure whose callers must change, are flagged rather than hidden.
| SQL Server | Common PostgreSQL equivalent |
|---|---|
| T-SQL procedures and functions | PL/pgSQL procedures and functions |
| Procedure that returns a result set | Set-returning function, so callers change from EXEC to SELECT |
| IDENTITY column | Identity column (GENERATED AS IDENTITY) |
| BIT | BOOLEAN |
| UNIQUEIDENTIFIER | UUID |
| DATETIME, DATETIME2 | TIMESTAMP (up to 6 fractional digits; DATETIME2 has up to 7) |
| DATETIMEOFFSET | TIMESTAMPTZ |
| NVARCHAR, VARCHAR(MAX) | VARCHAR or TEXT |
| Temporary tables (#name) | Temporary tables, created differently |
| Case-insensitive default collation | Case-sensitive comparison by default |
Example run
Billing-SQLServer to Billing-PG in numbers
SQL Server 2019 to PostgreSQL, in the example run.
- Tables
- 286
- Rows
- 410,882,019
- Verdict
- Passed
Validation
How is the migration validated?
17 checks on every table in six groups, with deterministic read-only functions run on both databases and the results compared.
Questions
Frequently asked questions
Can T-SQL stored procedures be converted to PostgreSQL?
Yes. AI converts stored code, every AI conversion is checked, and anything that cannot be proven goes to a named person. Each procedure must compile on PostgreSQL before deploy.
What happens to procedures that return result sets?
In PostgreSQL they usually become set-returning functions, so callers change from EXEC to SELECT. Datalobe flags each procedure whose callers must change.
Will case-insensitive queries still work?
Not always. SQL Server collations are often case-insensitive and PostgreSQL compares text case-sensitively by default. The assessment flags affected columns so you can decide how to handle them.
How much downtime does the migration need?
With keep-in-sync (CDC), downtime is limited to the cut-over window. Data copies in checkpointed batches that resume after an interruption, and SQL Server stays in use.
Sources (29)
- PostgreSQL 16 Documentation: 43.1 Overview (PL/pgSQL), checked
- PostgreSQL 16 Documentation: Chapter 42 Procedural Languages, checked
- SQL Server Transact-SQL Reference: CREATE PROCEDURE (Transact-SQL), checked
- SQL Server Database Engine: Stored Procedures (Database Engine), checked
- SQL Server Transact-SQL Reference: CREATE FUNCTION (Transact-SQL), checked
- SQL Server Transact-SQL Reference: CREATE TRIGGER (Transact-SQL), checked
- SQL Server Database Engine: Return Data From a Stored Procedure, checked
- PostgreSQL 16 Documentation: 38.4 User-Defined Procedures, checked
- SQL Server Transact-SQL Reference: CREATE TABLE (Transact-SQL) IDENTITY (Property), checked
- PostgreSQL 16 Documentation: CREATE TABLE, checked
- SQL Server Transact-SQL Reference: bit (Transact-SQL), checked
- PostgreSQL 16 Documentation: 8.6 Boolean Type, checked
- SQL Server Transact-SQL Reference: uniqueidentifier (Transact-SQL), checked
- PostgreSQL 16 Documentation: 8.12 UUID Type, checked
- SQL Server Transact-SQL Reference: datetime (Transact-SQL), checked
- SQL Server Transact-SQL Reference: datetime2 (Transact-SQL), checked
- PostgreSQL 16 Documentation: 8.5 Date/Time Types, checked
- SQL Server Transact-SQL Reference: datetimeoffset (Transact-SQL), checked
- SQL Server Transact-SQL Reference: nchar and nvarchar (Transact-SQL), checked
- SQL Server Transact-SQL Reference: char and varchar (Transact-SQL), checked
- PostgreSQL 16 Documentation: 8.3 Character Types, checked
- SQL Server Transact-SQL Reference: CREATE TABLE (Transact-SQL), checked
- SQL Server Database Engine: Collation and Unicode Support, checked
- SQL Server Database Engine: Set or Change the Server Collation, checked
- PostgreSQL 16 Documentation: 24.2 Collation Support, checked
- PostgreSQL 16 Documentation: CREATE COLLATION, checked
- PostgreSQL 16 Documentation: BEGIN, checked
- PostgreSQL 16 Documentation: ROLLBACK, checked
- PostgreSQL wiki: Transactional DDL in PostgreSQL: A Competitive Analysis, checked
Next step
Assess your SQL Server database
Send a read-only connection or a schema export and get an Assessment Report.