The short version
For SQL Server to PostgreSQL data types mapping, most columns are mechanical: int stays integer, nvarchar(n) becomes varchar(n), nvarchar(max) becomes text, varbinary becomes bytea, uniqueidentifier becomes uuid, datetime2 becomes timestamp and IDENTITY becomes GENERATED BY DEFAULT AS IDENTITY. The ones that bite are money (use numeric(19,4), never PostgreSQL money), bit (use boolean), datetimeoffset (PostgreSQL keeps the instant and drops the offset), rowversion (no equivalent) and collation: SQL Server is usually case-insensitive and PostgreSQL is not. For code, an automatic translator gets the expressions right and the procedures wrong, so plan to hand-write PL/pgSQL.
Everything below that says "I ran" was run on PostgreSQL 16.14 with sqlglot 30.17.0 doing the translation. I had no SQL Server instance for this one, so the SQL Server behaviour is quoted from Microsoft's documentation, and the AWS tools are described from AWS's documentation, not from a run.
SQL Server to PostgreSQL data types mapping table
| SQL Server | PostgreSQL | Watch out for |
|---|---|---|
int, bigint, smallint |
integer, bigint, smallint |
nothing |
decimal(p,s), numeric(p,s) |
numeric(p,s) |
nothing |
money |
numeric(19,4) |
PostgreSQL money rounds to the locale's decimals |
bit |
boolean |
= 0 and = 1 stop working |
uniqueidentifier |
uuid |
sort order of GUIDs differs |
nvarchar(n), varchar(n) |
varchar(n) |
n means different things |
nvarchar(max), varchar(max) |
text |
values over about 1 GB |
varbinary(n), varbinary(max) |
bytea |
bytea(max) is a syntax error |
datetime |
timestamp(3) |
|
datetime2(7) |
timestamp(6) |
seventh digit is rounded away |
datetimeoffset |
timestamptz (plus an offset column if you need it) |
original offset is lost |
rowversion |
xmin or a counter you maintain |
no automatic version stamp |
INT IDENTITY(1,1) |
integer GENERATED BY DEFAULT AS IDENTITY |
sequence does not move when you load IDs |
AS (expr) PERSISTED |
type GENERATED ALWAYS AS (expr) STORED |
PostgreSQL needs the type |
_CI_AS collation |
nondeterministic ICU collation | LIKE support depends on version |
The documentation behind each row:
- SQL Server
moneyis accurate to a ten-thousandth of a unit, and Microsoft itself warns against it for calculations and recommendsdecimalwith at least four decimal places. PostgreSQLmoneygets its fractional precision fromlc_monetary, and its output is locale-sensitive. The longer story is in Money Stored as Float: Fix It During the Migration. - SQL Server
bitis an integer type that takes 1, 0 or NULL. EveryWHERE IsPaid = 0in your code has to change once it is aboolean. uniqueidentifieris a 16-byte GUID, and Microsoft notes that ordering is not implemented by comparing the bit patterns. PostgreSQLuuidholds the same 128 bits, but do not expectORDER BYon that column to return rows in the same order after the move.- In
nvarchar(n),ncounts byte-pairs, not characters, and a supplementary character takes two. PostgreSQLvarchar(n)counts characters, not bytes, so the samennever gets tighter. In the other direction,nvarchar(max)goes up to 2 GB while the longest string PostgreSQL stores is about 1 GB. varbinary(max)holds up to 2^31-1 bytes. In PostgreSQL it is plainbytea, a variable-length binary string; give it a length and PostgreSQL rejects it, as the run below shows.datetimeis rounded to increments of .000, .003 or .007 seconds, sotimestamp(3)holds it.datetime2goes to 7 digits (100 ns), and PostgreSQL's timestamp precision stops at 6.datetimeoffsetis stored as UTC but the offset is preserved for retrieval. PostgreSQLtimestamptzis stored as UTC and the original zone is not retained.rowversionis an 8-byte number from a per-database counter, bumped on every insert and update. PostgreSQL has nothing that fills a column by itself like that.- A computed column is virtual unless marked
PERSISTED. PostgreSQL has stored and virtual generated columns, and in the current release virtual is the default, so writeSTOREDfor a persisted one.
What sqlglot does with a real table
The source table, written the way SQL Server shops write them:
CREATE TABLE dbo.Orders (
OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
OrderGuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
CustomerName NVARCHAR(100) NOT NULL,
Notes NVARCHAR(MAX) NULL,
Amount MONEY NOT NULL,
TaxRate DECIMAL(5,4) NOT NULL,
Total AS (Amount * (1 + TaxRate)) PERSISTED,
IsPaid BIT NOT NULL DEFAULT 0,
PlacedAt DATETIME NOT NULL DEFAULT GETDATE(),
ShippedAt DATETIME2(7) NULL,
PlacedAtTz DATETIMEOFFSET NULL,
Photo VARBINARY(MAX) NULL,
RowVer ROWVERSION
);
sqlglot.transpile(src, read="tsql", write="postgres", pretty=True) returned:
CREATE TABLE dbo.Orders (
OrderID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL PRIMARY KEY,
OrderGuid UUID NOT NULL DEFAULT GEN_RANDOM_UUID(),
CustomerName VARCHAR(100) NOT NULL,
Notes VARCHAR(MAX) NULL,
Amount MONEY NOT NULL,
TaxRate DECIMAL(5, 4) NOT NULL,
Total GENERATED ALWAYS AS ((
Amount * (
1 + TaxRate
)
)) STORED,
IsPaid BIT NOT NULL DEFAULT 0,
PlacedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
ShippedAt TIMESTAMP(7) NULL,
PlacedAtTz TIMESTAMPTZ NULL,
Photo BYTEA(MAX) NULL,
RowVer BYTEA
);
The identity, the UUID default, datetimeoffset and PERSISTED came out right. Then I ran it against PostgreSQL in a loop, fixing whatever the error pointed at and running again (the LINE 1: context lines trimmed):
ERROR: syntax error at or near "MAX"
fix: VARCHAR(MAX) -> TEXT
ERROR: syntax error at or near "ALWAYS"
fix: Total GENERATED -> Total NUMERIC(19,4) GENERATED
ERROR: schema "dbo" does not exist
ERROR: type modifier is not allowed for type "bytea"
fix: BYTEA(MAX) -> BYTEA
ERROR: column "ispaid" is of type bit but default expression is of type integer
HINT: You will need to rewrite or cast the expression.
fix: BIT NOT NULL DEFAULT 0 -> BOOLEAN NOT NULL DEFAULT false
OK
WARNING: TIMESTAMP(7) precision reduced to maximum allowed, 6
(I created schema dbo after the third error.) Five errors (one of them only the missing schema) and one warning in thirteen columns. The warning matters: PostgreSQL accepted the table and quietly made the column timestamp(6).
Where the table loads but the meaning changes
These are the dangerous ones, because nothing fails. With sqlglot's table in place (money and all):
> SHOW lc_monetary
('en_US.utf8',)
> SELECT '19.9999'::money AS m, 19.9999::numeric(19,4)::money AS m2
('$20.00', '$20.00')
> INSERT ... (OrderID, ...) OVERRIDING SYSTEM VALUE VALUES (1,'Alice',100,0.2)
> INSERT ... (OrderID, ...) VALUES (2,'Bob',19.9999,0.0825)
> INSERT ... (CustomerName, Amount, TaxRate) VALUES ('Carol',5,0)
ERROR: duplicate key value violates unique constraint "orders_pkey"
> SELECT orderid, customername, amount, total, rowver FROM dbo.Orders ORDER BY 1
(1, 'Alice', '$100.00', Decimal('120.0000'), None)
(2, 'Bob', '$20.00', Decimal('21.6500'), None)
- Bob's
19.9999, a legal SQL Servermoneyvalue, was stored as$20.00. Nothing warned. totalcame from amoneytimes anumeric.EXPLAIN VERBOSEshows how PostgreSQL did it:Output: (amount * (('1'::numeric + taxrate))::double precision). Money arithmetic through a float, which the PostgreSQL docs themselves advise against.- Loading rows with their original IDs does not move the identity's sequence, so the first ordinary insert collided with row 1. Every table needs this after the load:
SELECT setval(pg_get_serial_sequence('dbo.orders','orderid'), (SELECT max(orderid) FROM dbo.orders));
-- 2, and the next insert got orderid 3
rowverisNULLon every row, and stays that way.'2026-11-05 10:00:00.1234567'::timestamp(6)came back as10:00:00.123457, and'2026-11-05 10:00:00 +05:30'::timestamptzcame back as04:30:00+00. The+05:30is gone. If anything downstream needs the customer's local offset, keep it in a column of its own.
Case sensitivity and collation
SQL Server's default collation for an English (United States) install is SQL_Latin1_General_CP1_CI_AS, and CI means case-insensitive. Applications quietly depend on it. In PostgreSQL:
> SELECT count(*) FROM dbo.Orders WHERE CustomerName = 'alice'
(0,)
PostgreSQL's answer is a nondeterministic ICU collation:
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
-- customer_name varchar(100) COLLATE ci NOT NULL
> SELECT order_id, customer_name FROM dbo.orders WHERE customer_name = 'ALICE'
(1, 'Alice')
> SELECT order_id FROM dbo.orders WHERE customer_name LIKE 'al%'
ERROR: nondeterministic collations are not supported for LIKE
> SELECT order_id FROM dbo.orders WHERE lower(customer_name) LIKE 'al%'
ERROR: nondeterministic collations are not supported for LIKE
> SELECT order_id FROM dbo.orders WHERE customer_name COLLATE "default" ILIKE 'al%'
(1,)
That error is from 16. The current PostgreSQL docs say LIKE supports nondeterministic collations, while ILIKE, SIMILAR TO and regular expressions do not, so on a newer server plain LIKE should work. Either way, the collation docs warn of a performance cost, and B-tree deduplication does not work on such indexes.
Identifiers fold the other way. Unquoted CustomerName became customername, which is why my hand-written DDL below uses snake_case and never quotes.
Converting T-SQL code: the procedure
The procedure has the usual T-SQL habits: TOP, ISNULL, GETDATE(), + for strings, IIF, a #temp table, OUTPUT and TRY/CATCH:
CREATE PROCEDURE dbo.MarkPaid @MinAmount MONEY, @Top INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
CREATE TABLE #batch (OrderID INT, Label NVARCHAR(200));
INSERT INTO #batch (OrderID, Label)
SELECT TOP (@Top) OrderID,
CustomerName + ' / ' + ISNULL(Notes, 'no notes') + ' / ' + IIF(Amount > 1000, 'big', 'small')
FROM dbo.Orders
WHERE IsPaid = 0 AND Amount >= @MinAmount
ORDER BY PlacedAt;
UPDATE o SET IsPaid = 1, ShippedAt = GETDATE()
OUTPUT inserted.OrderID, b.Label, inserted.ShippedAt
FROM dbo.Orders o JOIN #batch b ON b.OrderID = o.OrderID;
DROP TABLE #batch;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS ErrMsg;
THROW;
END CATCH
END
sqlglot printed Unsupported Stored Procedure syntax and three contains unsupported syntax. Falling back to parsing as a 'Command' warnings, then:
CREATE PROCEDURE AS BEGIN
SET NOCOUNT = ON; BEGIN TRY
CREATE TABLE #batch (OrderID INT, Label NVARCHAR(200)); INSERT INTO batch (
...
RETURNING inserted.OrderID, b.Label, inserted.ShippedAt; DROP TABLE batch; END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS ErrMsg; THROW; END CATCH
END;
The procedure lost its name and parameters, and TRY/CATCH passed through as raw text. Worse, the CREATE TABLE #batch stayed raw while the INSERT and DROP now target batch, a permanent table. Fix only the line that errors and you get a procedure that writes to, and then drops, a real table.
Fed one statement at a time, the body translates much better:
CREATE TEMPORARY TABLE batch (OrderID INT, Label VARCHAR(200));
INSERT INTO batch (OrderID, Label)
SELECT
OrderID,
CustomerName + ' / ' + COALESCE(Notes, 'no notes') + ' / ' + CASE WHEN Amount > 1000 THEN 'big' ELSE 'small' END
FROM dbo.Orders
WHERE
IsPaid = 0 AND Amount >= $MinAmount
ORDER BY
PlacedAt NULLS FIRST
LIMIT $Top;
UPDATE o SET IsPaid = 1, ShippedAt = CURRENT_TIMESTAMP
FROM dbo.Orders AS o
JOIN batch AS b
ON b.OrderID = o.OrderID
RETURNING inserted.OrderID, b.Label, inserted.ShippedAt;
ISNULL, IIF and TOP are right. NULLS FIRST is a careful touch: SQL Server treats NULL as the lowest value, while PostgreSQL sorts NULLs last in ascending order. Running it (with literals in place of $MinAmount and $Top, which are not valid outside a function), each fix revealed the next error:
ERROR: operator does not exist: character varying + unknown
ERROR: operator does not exist: money > integer (after + became ||)
ERROR: relation "o" does not exist (the UPDATE)
ERROR: missing FROM-clause entry for table "inserted" (after UPDATE dbo.Orders AS o ... WHERE)
And once IsPaid is a boolean, two separate probes show what = 0 and SET IsPaid = 1 turn into:
> SELECT true = 0
ERROR: operator does not exist: boolean = integer
> CREATE TEMP TABLE b1 (x boolean); INSERT INTO b1 VALUES (1)
ERROR: column "x" is of type boolean but expression is of type integer
Two of these change meaning when fixed carelessly. Swap + for || and NULL behaves as before: SQL Server has CONCAT_NULL_YIELDS_NULL always ON since 2017, and in my run 'Alice' || NULL returned NULL. Swap it for concat() and NULL arguments are ignored: concat('Alice', NULL) returned 'Alice'. And CURRENT_TIMESTAMP in PostgreSQL is the start of the transaction; use clock_timestamp() where you truly want the wall clock.
The hand-written PostgreSQL version
Table and function, as they ran:
CREATE TABLE dbo.orders (
order_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_guid uuid NOT NULL DEFAULT gen_random_uuid(),
customer_name varchar(100) COLLATE ci NOT NULL,
notes text,
amount numeric(19,4) NOT NULL,
tax_rate numeric(5,4) NOT NULL,
total numeric(19,4) GENERATED ALWAYS AS (amount * (1 + tax_rate)) STORED,
is_paid boolean NOT NULL DEFAULT false,
placed_at timestamp(3) NOT NULL DEFAULT localtimestamp,
shipped_at timestamp(6),
placed_at_tz timestamptz,
placed_at_offset interval,
photo bytea,
row_ver bigint NOT NULL DEFAULT 0
);
CREATE FUNCTION dbo.mark_paid(p_min_amount numeric, p_top integer)
RETURNS TABLE (order_id integer, label text, shipped_at timestamp)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
WITH batch AS (
SELECT o.order_id,
o.customer_name || ' / ' || coalesce(o.notes, 'no notes') || ' / '
|| CASE WHEN o.amount > 1000 THEN 'big' ELSE 'small' END AS label
FROM dbo.orders o
WHERE NOT o.is_paid AND o.amount >= p_min_amount
ORDER BY o.placed_at NULLS FIRST
LIMIT p_top
)
UPDATE dbo.orders o
SET is_paid = true, shipped_at = localtimestamp, row_ver = o.row_ver + 1
FROM batch b
WHERE b.order_id = o.order_id
RETURNING o.order_id, b.label::text, o.shipped_at;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'mark_paid failed: % %', SQLSTATE, SQLERRM;
RAISE;
END;
$$;
The temp table became a CTE, OUTPUT inserted.* became RETURNING, and the procedure became a function, so the caller gets the rows with SELECT * FROM. Results:
> SELECT order_id, amount, total FROM dbo.orders ORDER BY 1
(1, Decimal('1500.0000'), Decimal('1800.0000'))
(2, Decimal('19.9999'), Decimal('21.6499'))
(3, Decimal('5.0000'), Decimal('5.0000'))
> SELECT * FROM dbo.mark_paid(10, 2)
(1, 'Alice / no notes / big', datetime.datetime(2026, 10, 25, 21, 27, 36, 685440))
(2, 'Bob / rush / small', datetime.datetime(2026, 10, 25, 21, 27, 36, 685440))
> SELECT * FROM dbo.mark_paid(10, -1)
ERROR: LIMIT must not be negative
WARNING: mark_paid failed: 2201W LIMIT must not be negative
> SELECT count(*) FILTER (WHERE is_paid) FROM dbo.orders
(0,)
Bob keeps his 19.9999 and his total is 21.6499 instead of 21.65. The EXCEPTION block plays the part of CATCH: PostgreSQL rolls back the block's changes and exposes SQLSTATE and SQLERRM, and the same page warns such blocks are significantly more expensive, so do not wrap every function in one by reflex.
For rowversion I kept a counter bumped in the UPDATE. If the column only existed for optimistic concurrency checks, the system column xmin, the transaction ID that wrote the current row version, can often serve instead.
SQL Server to PostgreSQL migration tools
sqlglot is what I ran. It handled types and expressions well and procedural T-SQL badly. Use it for views and queries, read every line of what it gives you, and never feed it a whole procedure.
AWS Schema Conversion Tool (not run here) converts SQL Server 2008 R2 through 2022 to PostgreSQL or Aurora PostgreSQL, including code, and gives guidance as action items for what it cannot convert automatically. Its SQL Server to PostgreSQL page documents an option to convert procedures to functions, rewriting GOTO into blocks and loops, and an extension pack that emulates SQL Server functions: PATINDEX, for example, becomes aws_sqlserver_ext.patindex(...). That means the converted code keeps calling the aws_sqlserver_ext schema, so it has to come along to every environment.
Babelfish for Aurora PostgreSQL (not run here) takes a different route. It lets an Aurora PostgreSQL cluster accept SQL Server clients over the TDS protocol on port 1433, with PostgreSQL on 5432, so an application can keep its T-SQL and its drivers. It is an Aurora PostgreSQL feature, and AWS keeps a list of differences from SQL Server worth reading before you bet on it.
If PostgreSQL is not a hard requirement, staying on SQL Server in the cloud avoids all of the above; see SQL Server to Azure SQL Database, Step by Step.
I dropped the scratch database afterwards.
Sources
- Microsoft: money and smallmoney
- Microsoft: bit
- Microsoft: uniqueidentifier
- Microsoft: nchar and nvarchar
- Microsoft: binary and varbinary
- Microsoft: datetime
- Microsoft: datetime2
- Microsoft: datetimeoffset
- Microsoft: rowversion
- Microsoft: Specify computed columns in a table
- Microsoft: Collation and Unicode support
- Microsoft: ORDER BY clause
- Microsoft: SET CONCAT_NULL_YIELDS_NULL
- PostgreSQL: Monetary types
- PostgreSQL: UUID type
- PostgreSQL: Character types
- PostgreSQL: Binary data types
- PostgreSQL: Date/time types
- PostgreSQL: Generated columns
- PostgreSQL: Collation support
- PostgreSQL: Pattern matching
- PostgreSQL: Sorting rows
- PostgreSQL: String functions
- PostgreSQL: Date/time functions
- PostgreSQL: PL/pgSQL control structures
- PostgreSQL: System columns
- AWS: What is the AWS Schema Conversion Tool
- AWS: SQL Server to PostgreSQL with AWS SCT
- AWS: Using Babelfish for Aurora PostgreSQL
- AWS: Differences between Babelfish and SQL Server