Data Type Mapping SQL Server → PostgreSQL — What Converts Cleanly and What Breaks

A migration from SQL Server to PostgreSQL rarely fails at actually copying the data. It fails at datetime, where the choice between timestamp and timestamptz is anything but cosmetic, at bit, which is not a boolean, and at money, which you’d be better off not touching in PostgreSQL at all. The SQL Server to PostgreSQL data type mapping decides whether the data arrives cleanly — or is silently corrupted without a single error message.

The good news: most types convert one-to-one. The bad news: the handful that don’t are exactly the ones that hurt.

The essentials up front:

• What converts cleanly: integers, numeric/decimal, varchar, date — the easy half.
• What breaks: datetime, bit, money, uniqueidentifier, nvarchar and tinyint have no one-to-one equivalent.
• The invisible aspect: collation and case sensitivity behave differently in PostgreSQL than in the SQL Server default.
• What tooling takes off your plate: pgloader maps the standard cases automatically — the edge cases stay handwork.

Prerequisite: SQL Server 2017+ as the source, PostgreSQL 14+ as the target. PostgreSQL types are placed in context briefly on first mention — no prior Postgres knowledge required, T-SQL basics yes.

Contents

• Data Type Mapping SQL Server → PostgreSQL: the big picture
• The types that convert one-to-one
• The types that break
• Collation and case — the invisible type aspect
• A concrete CREATE TABLE — before and after
• What pgloader maps automatically — and where handwork remains
• FAQ
• Related articles

Data Type Mapping SQL Server → PostgreSQL: the big picture

Before we get into the traps, the overview. This table is the SQL Server to PostgreSQL data type mapping in short form — the rows in the upper block are uncritical, the ones in the lower block need attention.

The types that convert one-to-one

There’s little to tell here — and that’s the point. The following types can be carried over with no functional consequences:

• Integers. int, bigint and smallint have exactly the same names and exactly the same value ranges in PostgreSQL. integer is 4 bytes, bigint 8 bytes, smallint 2 bytes — just like on the other side.
• Decimals — with a catch. decimal(p, s) and numeric(p, s) are the same in both worlds when you give an explicit precision (numeric is the canonical name in PostgreSQL, decimal an alias). Watch out for the omitted argument: SQL Server decimal with no spec means decimal(18, 0) — that is, integer. PostgreSQL numeric with no spec, by contrast, means unbounded precision with arbitrary scale. Mapping a bare decimal one-to-one onto a bare numeric therefore changes the behaviour — write an explicit numeric(18, 0) if you want to keep the SQL Server semantics.
• Floating point. A bare SQL Server float is float(53) and corresponds to double precision, real stays real. One subtlety: float(n) is internally real (4 bytes) for n from 1 to 24, and double precision (8 bytes) from 25 on — map accordingly when the precision is given explicitly.
• Text with ASCII content. varchar(n) stays varchar(n). In PostgreSQL, though, text (with no length limit) is the idiomatic standard — you only set a length limit there when it’s a genuine business rule, not out of habit.
• Date (date only). date is identical on both sides.
• Binary data. Where SQL Server distinguishes between binary, varbinary and varbinary(max), PostgreSQL knows only bytea. Lossless in content.

This half is handled by any converter without thinking. The other half is where it gets interesting.

The types that break

datetime → timestamp / timestamptz. The real issue isn’t the value, it’s the time zone. The technical one-to-one mapping is datetime/datetime2 → timestamp (time-zone-naive → time-zone-naive) — the value carries over unchanged. PostgreSQL additionally offers timestamptz, which stores an absolute point in time. Moving to it, however, is not a pure type conversion but a design decision: timestamptz interprets the naive value relative to a time zone — and the database has no idea which time zone the source data was in (UTC? server time? Berlin?). Only switch to timestamptz if you can answer that question; otherwise timestamp is the safe choice. Second point: the value range — datetime starts only in 1753, while PostgreSQL timestamp reaches far beyond that (rarely relevant in practice, but good to know).

bit → boolean. SQL Server models yes/no as bit (0, 1 or NULL). PostgreSQL has the real type boolean for this (true/false/NULL). The column conversion itself is uncritical — pgloader handles it correctly. The trap is in the ported logic: in T-SQL, WHERE is_active = 1 is common. In PostgreSQL, is_active is already boolean, and comparing it to an integer is an error (operator does not exist: boolean = integer); likewise an integer cannot be used implicitly as a truth value (WHERE int_column is an error). The correct form is WHERE is_active or WHERE is_active = true. An explicit cast, on the other hand, works fine: 1::boolean yields true, 0::boolean yields false. So the trip-ups are the stored procedures and queries that treated bit columns like numbers (see the dedicated article on code porting).

money → numeric(19, 4). PostgreSQL does have a type called money — but you shouldn’t use it. It depends on the server’s lc_monetary locale, which makes input/output and therefore migrations fragile, and it’s awkward for calculations. The clean equivalent is numeric with a fixed scale: money → numeric(19, 4), smallmoney → numeric(10, 4). That keeps amounts exact and engine-independent.

uniqueidentifier → uuid. PostgreSQL has a native uuid type. The default value moves with it: DEFAULT NEWID() becomes DEFAULT gen_random_uuid() — that function has been in the core since PostgreSQL 13, with no extension needed. For NEWSEQUENTIALID() (sequential GUIDs) there’s no direct counterpart; here you have to choose a new strategy deliberately.

nvarchar → text / varchar. This one is often a relief. PostgreSQL databases usually run in UTF-8 — so all text is natively Unicode. There’s no separate “national” character type and no N'…' prefix for literals. nvarchar(100) becomes varchar(100) or simply text, nvarchar(max) becomes text. The N in front of strings drops away entirely. Special case JSON: if an nvarchar(max) column actually holds JSON (SQL Server had no dedicated type for it through 2022, storing it as nvarchar(max)), then jsonb — not text — is the better choice in PostgreSQL, bringing indexing, validation and JSON operators with it.

tinyint → smallint. Here you lose a property. SQL Server tinyint is 1 byte and unsigned (0–255). PostgreSQL has neither a 1-byte integer nor unsigned. The equivalent is smallint (2 bytes, −32,768 to 32,767). If you want to keep the original semantics, add a check: CHECK (column BETWEEN 0 AND 255).

rowversion / timestamp → no equivalent (and a naming trap). Careful: the SQL Server keyword timestamp is a synonym for rowversion — an auto-incrementing 8-byte binary value for concurrency control, not a date. Mapping it blindly onto the PostgreSQL timestamp (which is a date/time type) builds in a serious bug. There is no true counterpart; for optimistic locking, the xmin system column or a dedicated version column is the way to go.

The column defaults and IDENTITY in the example below are, strictly speaking, a schema topic, not a type topic — how IDENTITY becomes GENERATED/sequences is covered in the dedicated article on schema migration.

Collation and case — the invisible type aspect

One aspect that isn’t a data type of its own and yet belongs to type migration: sort and comparison behaviour. SQL Server is run in many installations with a case-insensitive default collation (…_CI_…) — 'Müller' = 'müller' is true there. PostgreSQL compares text case-sensitively by default and exactly by byte/locale. The same query that found a row in SQL Server suddenly finds none in PostgreSQL.

This is not a conversion error but a decision to be made deliberately. If you need case-insensitive columns, you have several options: a non-deterministic ICU collation (from PostgreSQL 12 on) directly on the column, the citext extension (a case-insensitive text type), or explicit LOWER(...) comparisons or a suitable COLLATE in the affected queries. Closely related is the question of how identifiers (table and column names) are treated with respect to case — that’s a topic of its own, covered in detail in the sister article on case sensitivity.

A concrete CREATE TABLE — before and after

To make the mapping tangible, a table that bundles all the critical types at once. First the source in T-SQL:

  1: CREATE TABLE dbo.customer
  2: (
  3:     customer_id     int               IDENTITY(1, 1)  NOT NULL
  4:    ,customer_guid   uniqueidentifier  NOT NULL  DEFAULT NEWID()
  5:    ,full_name       nvarchar(100)     NOT NULL
  6:    ,is_active       bit               NOT NULL  DEFAULT 1
  7:    ,credit_limit    money             NULL
  8:    ,rating          tinyint           NULL
  9:    ,created_at      datetime          NOT NULL  DEFAULT GETDATE()
 10:    ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
 11: );

  1: CREATE TABLE dbo.customer
  2: (
  3:     customer_id     int               IDENTITY(1, 1)  NOT NULL
  4:    ,customer_guid   uniqueidentifier  NOT NULL  DEFAULT NEWID()
  5:    ,full_name       nvarchar(100)     NOT NULL
  6:    ,is_active       bit               NOT NULL  DEFAULT 1
  7:    ,credit_limit    money             NULL
  8:    ,rating          tinyint           NULL
  9:    ,created_at      datetime          NOT NULL  DEFAULT GETDATE()
 10:    ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
 11: );

And the same as a PostgreSQL target:

  1: CREATE TABLE customer
  2: (
  3:     customer_id     integer         GENERATED BY DEFAULT AS IDENTITY
  4:    ,customer_guid   uuid            NOT NULL  DEFAULT gen_random_uuid()
  5:    ,full_name       text            NOT NULL
  6:    ,is_active       boolean         NOT NULL  DEFAULT true
  7:    ,credit_limit    numeric(19, 4)
  8:    ,rating          smallint        CHECK (rating BETWEEN 0 AND 255)
  9:    ,created_at      timestamptz     NOT NULL  DEFAULT now()
 10:    ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
 11: );

  1: CREATE TABLE customer
  2: (
  3:     customer_id     integer         GENERATED BY DEFAULT AS IDENTITY
  4:    ,customer_guid   uuid            NOT NULL  DEFAULT gen_random_uuid()
  5:    ,full_name       text            NOT NULL
  6:    ,is_active       boolean         NOT NULL  DEFAULT true
  7:    ,credit_limit    numeric(19, 4)
  8:    ,rating          smallint        CHECK (rating BETWEEN 0 AND 255)
  9:    ,created_at      timestamptz     NOT NULL  DEFAULT now()
 10:    ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
 11: );

Seven columns, seven decisions:

• Line 3: int IDENTITY(1, 1) → integer GENERATED BY DEFAULT AS IDENTITY — the auto-value column is moved to the SQL-standard identity mechanism.
• Line 4: uniqueidentifier → uuid with gen_random_uuid() instead of NEWID().
• Line 5: nvarchar(100) → text.
• Line 6: bit → boolean with true instead of 1.
• Line 7: money → numeric(19, 4).
• Line 8: tinyint → smallint with a preserving CHECK constraint.
• Line 9: datetime → timestamptz (deliberately here, since it’s a pure audit column) with now() instead of GETDATE().

No converter that merely swaps the column types gets all of these decisions right automatically — money and tinyint in particular need the human correction.

What pgloader maps automatically — and where handwork remains

A widely used free tool for this migration is pgloader — it takes schema and data over in a single run directly from SQL Server to PostgreSQL, casting the types along the way. It’s not the only one: bcp/COPY, ETL pipelines and commercial tools all play a part depending on data volume and downtime — the comparison of methods is covered in Transferring Data: bcp, COPY, pgloader, ETL — Which Method When. For the uncritical half from the first section it’s a no-brainer: integers, numeric, varchar, date land correctly without you writing a single rule. pgloader also covers many of the breaking cases with its defaults — bit → boolean or nvarchar → text, for instance.

Handwork remains wherever a business decision is needed that no tool can know:

• datetime → timestamp or timestamptz is the time-zone decision from above — whether the tool’s default fits needs checking, not blind adoption.
• money is often cast to numeric by default — but whether the scale is right, and whether the unsuitable money type sneaks in, needs checking.
• tinyint → smallint loses the 0–255 semantics; no converter adds the preserving CHECK constraint by itself.
• rowversion/timestamp, sql_variant, hierarchyid and geo types have no standard mapping and must be decided one by one.
• Stored procedures, triggers and default expressions with a type reference (GETDATE(), NEWID(), bit arithmetic) are pure handwork anyway.

The rule of thumb: pgloader takes the mechanical 80% off your plate. The last 20% are exactly the types that break — and they cost the thinking this article is about.

FAQ

Related articles

This article is part of a series on migrating from SQL Server to PostgreSQL. The other parts:

• Overview: Data Migration SQL Server to PostgreSQL — the Complete Guide
• Schema & DDL: Schema Migration SQL Server → PostgreSQL — Identity, Constraints, Defaults, Sequences
• Data transfer: Transferring Data: bcp, COPY, pgloader, ETL — Which Method When
• Code porting: Porting T-SQL to PL/pgSQL — Migrating Procedures and Functions
• Verification: Verifying the Migration — Data Quality and Row Reconciliation

For more on type conversion itself:

• Data Quality // Fundamentals of Type Conversion with T-SQL
• Design Pattern // Safe Type Conversion with T-SQL
• Data quality in SQL Server // TRY_CONVERT for date, datetime, datetime2 and time done safely