SQL Server to PostgreSQL Data Type Mapping
On this page
Why data type mapping matters
Choosing column types is the part of a migration you can't easily undo. Get it wrong and you'll find out months later, when a money report is off by a cent or a timestamp silently lost its time zone. Most SQL Server types have an obvious PostgreSQL equivalent. A handful don't, and those are the ones this lesson focuses on.
SQL Server to PostgreSQL data type mapping table
| SQL Server | PostgreSQL | Note |
|---|---|---|
int, bigint, smallint | integer, bigint, smallint | Same sizes and ranges |
tinyint | smallint | No 1-byte integer. Add CHECK (x BETWEEN 0 AND 255) if the range matters. |
bit | boolean | A true logical type, see below |
decimal(p,s), numeric(p,s) | numeric(p,s) | Same exact arithmetic |
money, smallmoney | numeric(19,4), numeric(10,4) | Never float, see below |
float, real | double precision, real | Both approximate |
varchar(n), nvarchar(n) | varchar(n) or text | All PostgreSQL text is Unicode, so there's no separate N type |
varchar(max), nvarchar(max) | text | No length limit, no speed penalty |
char(n), nchar(n) | char(n) | Padded with spaces, rarely a good idea |
datetime, datetime2, smalldatetime | timestamp | Precision is microseconds. datetime2(7) loses its last digit. |
datetimeoffset | timestamptz | Stores the instant, not the original offset |
date, time | date, time | Same |
uniqueidentifier | uuid | Native 16-byte type |
varbinary(n), varbinary(max), image | bytea | Raw bytes |
xml | xml | Querying uses XPath functions instead of XQuery methods |
JSON in nvarchar(max) | jsonb | A real upgrade, see below |
One more choice to make for date columns: timestamp or timestamptz. SQL Server's datetime2 has no time zone, so timestamp is the literal translation. But if the values are real moments, like "when the order was placed", and your users or servers span time zones, migrating to timestamptz fixes a whole class of bugs. Day 4 of the PostgreSQL course covers the difference.
money, bit, uniqueidentifier, datetime and JSON
money becomes numeric. SQL Server's money is an exact type with four decimal places. Its replacement must be exact too, so use numeric(19,4). Mapping it to real or double precision introduces binary rounding errors that show up as cent differences in financial totals.
bit becomes boolean. PostgreSQL booleans are true, false or NULL, not 0 and 1. Queries read more naturally, but code that compares with numbers needs a small fix:
SELECT * FROM customers WHERE is_active; -- instead of is_active = 1
SELECT * FROM customers WHERE NOT is_active; -- instead of is_active = 0is_active = 1 fails in PostgreSQL with "operator does not exist: boolean = integer". Application code that reads the column as a number needs updating too.
uniqueidentifier becomes uuid. It's the same 16 bytes. NEWID() becomes gen_random_uuid(), built in since PostgreSQL 13. If you used NEWSEQUENTIALID() to keep inserts in index order, PostgreSQL 18's uuidv7() serves the same purpose: its values start with a timestamp, so they arrive roughly in order.
datetime becomes timestamp. The types match, but defaults don't. DEFAULT GETDATE() becomes DEFAULT now(), and remember from Day 4 that now() is fixed for the whole transaction.
JSON in nvarchar(max) becomes jsonb. Most SQL Server systems store JSON as plain text and parse it with JSON_VALUE() on every query. SQL Server 2025 adds a native json type, but most databases you'll migrate predate it. PostgreSQL's jsonb stores JSON parsed into a binary form. It's faster to query, it has operators like ->> and @>, and a GIN index can cover the whole document:
-- T-SQL
SELECT event_id FROM dbo.events WHERE JSON_VALUE(payload, '$.eventType') = 'signup';-- PostgreSQL
CREATE TABLE events (
event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
CREATE INDEX ON events USING gin (payload);
SELECT event_id FROM events WHERE payload @> '{"eventType": "signup"}';If a text column contains invalid JSON, converting it to jsonb fails. That's useful, because it finds bad rows during the migration rather than in production. Run a validation query against the source before you migrate.
Converting a CREATE TABLE column by column
Here's a typical SQL Server table:
-- T-SQL
CREATE TABLE dbo.Customers (
CustomerId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
CustomerCode INT IDENTITY(1,1) NOT NULL,
CompanyName NVARCHAR(200) NOT NULL,
IsActive BIT NOT NULL DEFAULT 1,
CreditLimit MONEY NOT NULL DEFAULT 0,
RiskTier TINYINT NOT NULL DEFAULT 1,
Notes NVARCHAR(MAX) NULL,
Logo VARBINARY(MAX) NULL,
Preferences NVARCHAR(MAX) NULL, -- JSON as text
CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);And its PostgreSQL translation:
CREATE TABLE customers (
customer_id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
customer_code integer GENERATED ALWAYS AS IDENTITY,
company_name varchar(200) NOT NULL,
is_active boolean NOT NULL DEFAULT true,
credit_limit numeric(19,4) NOT NULL DEFAULT 0,
risk_tier smallint NOT NULL DEFAULT 1 CHECK (risk_tier BETWEEN 0 AND 255),
notes text,
logo bytea,
preferences jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO customers (company_name, preferences)
VALUES ('Acme', '{"theme": "dark", "newsletter": true}')
RETURNING customer_id, customer_code, preferences ->> 'theme' AS theme;Besides the types, notice the names. CustomerId became customer_id, following Day 4's advice to use lowercase snake_case and skip quoted names.
Check yourself
double precision is binary floating point and can't store most decimal amounts exactly, so rounding errors build up. Change the column to numeric(19,4).
The bit column became a real boolean, and PostgreSQL doesn't silently compare booleans with integers. Write WHERE NOT is_deleted or WHERE is_deleted = false. Check application code and ORMs too: parameters sent as 0 and 1 must become false and true.
It's useful news. Those rows contain invalid JSON that SQL Server accepted as text. Find and fix them in the source, or decide how to handle them, before cutting over.
If the values represent moments in time, use timestamptz, which stores an absolute instant and converts on display. If they're local wall-clock times with no zone, such as a store's opening time, use timestamp. Getting this wrong causes off-by-hours bugs once servers or users span time zones.
Yes. SQL Server sorts uniqueidentifier values in an unusual byte order, while PostgreSQL sorts uuid values bytewise. Any query that relied on ordering by a GUID, or on sequential GUIDs for index locality, behaves differently. PostgreSQL 18's uuidv7() generates time-ordered UUIDs if you need locality.