Data Types: The SQL Server → PostgreSQL Translation Table

Previously, we remapped instances, databases, schemas, and roles onto their PostgreSQL equivalents. Now for the part every migration actually stands on: the type system. Most of it translates one-to-one — and the handful of differences include some genuine upgrades.

The translation table

Computed columns come along too, as GENERATED ... AS (expression) STORED — and PostgreSQL 18 added virtual generated columns, computed on read like SQL Server's non-persisted flavor.

Strings: the N prefix retires

PostgreSQL databases are UTF-8 from the start, so the varchar-versus-nvarchar split simply doesn't exist. Every string type holds every language; the N'...' prefix has no job left to do.

The bigger habit to unlearn is length obsession. In Postgres, text, varchar, and varchar(n) all perform identically — the limit is purely a constraint, not a storage strategy. Idiomatic Postgres uses text everywhere and reserves varchar(n) for cases where the limit is a genuine business rule, like a two-letter country code.

The #1 migration bug: timestamp vs timestamptz

Two timestamp types, one letter apart, and picking the wrong one is the most common bug in SQL Server migrations. timestamp stores a wall-clock reading with no time zone attached — like DATETIME2. timestamptz stores the moment normalized to UTC and converts to the session's time zone on the way in and out.

The rule of thumb: if a column records a moment in time — created_at, paid_at, anything you'd compare across servers or users — use timestamptz. Plain timestamp is for wall-clock concepts like "the store opens at 09:00". When in doubt, timestamptz.

uuid, and the uuidv7() upgrade

UNIQUEIDENTIFIER becomes uuid, and your instincts about it carry over — including the painful one: random UUIDs (from gen_random_uuid(), the NEWID() equivalent) scatter inserts across an index just like they did in SQL Server. You used NEWSEQUENTIALID() to cope. PostgreSQL 18 ships a better answer: uuidv7(), generating standardized, time-ordered UUIDs that arrive in roughly increasing order and index beautifully.

CREATE TABLE events (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    happened_at timestamptz NOT NULL DEFAULT now()
);

IDENTITY is right where you left it

Auto-incrementing keys use the SQL-standard syntax, which SQL Server also supports these days:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    placed_at timestamptz NOT NULL DEFAULT now()
);

You'll also see serial in older tutorials — that's the legacy spelling of the same idea; prefer GENERATED ALWAYS AS IDENTITY in new code. And the SCOPE_IDENTITY() / @@IDENTITY dance has a cleaner replacement: INSERT ... RETURNING id hands you the new key directly from the insert itself.

The upgrade toys: jsonb and arrays

Two types with no real SQL Server counterpart are worth knowing on day one. First, jsonb: JSON stored in a binary form that Postgres can index (with a GIN index) and query natively — WHERE data->>'status' = 'active' can use an index rather than shredding strings the way NVARCHAR(MAX)-plus-JSON_VALUE does. If JSON itself is a rusty spot, our JSON explainer has you covered.

Second, arrays: any column can be an array of its type — tags text[], scores int[] — with operators to match, which can replace a whole junction table for small, simple lists. One honest caveat: if you'd ever join on it, report on it, or constrain it with foreign keys, it deserves a proper table — arrays are for atomic little lists, not for smuggling relations into a column.

Types sorted. Next comes the everyday layer on top of them: the SELECTs, string functions, date math, and pagination idioms you type a hundred times a day. In Part 4, we build the T-SQL to PostgreSQL phrasebook.