Home / Guides
Migration guide

SQL Server to PostgreSQL: Types, Code and Tools

By · November 5, 2026 · 15 min read

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:

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)
SELECT setval(pg_get_serial_sequence('dbo.orders','orderid'), (SELECT max(orderid) FROM dbo.orders));
-- 2, and the next insert got orderid 3

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