A conversion tool will map every column in minutes. The mapping it picks is a default, not a decision. This guide lists the mappings that are safe, the ones that change behavior, and the checks that prove the data still means the same thing after the move.

Mappings that are safe

  • INT, BIGINT, SMALLINT map to integer, bigint, smallint.
  • DECIMAL(p,s) and NUMERIC(p,s) map to numeric(p,s).
  • FLOAT maps to double precision. REAL maps to real.
  • DATE maps to date. TIME maps to time.
  • UNIQUEIDENTIFIER maps to uuid.
  • VARBINARY and IMAGE map to bytea.
  • VARCHAR(MAX), NVARCHAR(MAX), TEXT, NTEXT map to text.
  • IDENTITY columns map to GENERATED ... AS IDENTITY. Reset the sequence to the migrated maximum before the first insert.

Mappings that change behavior

  • TINYINT has no one-byte equivalent. Use smallint and add a CHECK constraint if the 0 to 255 range matters.
  • BIT maps to boolean. Application code that writes 1 and 0 needs a cast or a driver setting. Find it before cutover, not after.
  • DATETIME rounds to about three milliseconds. DATETIME2 holds seven fractional digits. PostgreSQL timestamp holds six. The seventh digit is lost, which breaks equality comparisons against values captured from the source.
  • DATETIMEOFFSET maps to timestamptz, which stores the instant in UTC and does not keep the original offset. If the offset is business data, store it in a second column.
  • MONEY maps to numeric(19,4). Avoid the PostgreSQL money type; its formatting depends on locale settings.
  • NVARCHAR(n) maps to varchar(n). PostgreSQL counts characters in a UTF-8 database, so lengths carry over, but storage size per row changes.
  • ROWVERSION has no equivalent. Applications that use it for optimistic locking need a version column maintained by a trigger or by the application.
  • SQL_VARIANT, HIERARCHYID, GEOGRAPHY and GEOMETRY need a design decision: separate typed columns, the ltree extension, or PostGIS.

What breaks that is not a type

The larger risk sits next to the type, not in it.

  • Case sensitivity. Most SQL Server databases run a case-insensitive collation. PostgreSQL compares text case-sensitively by default. A login lookup that matched Smith and smith will stop matching. Options are the citext extension, a nondeterministic ICU collation, or explicit lower() indexes.
  • Trailing spaces. SQL Server ignores trailing spaces in most string comparisons. PostgreSQL does not for varchar and text.
  • NULLs in unique indexes. SQL Server allows one NULL in a unique index. PostgreSQL allows many unless the index is declared NULLS NOT DISTINCT (PostgreSQL 15 and later).
  • Identifier case. Unquoted identifiers fold to lower case. Mixed-case table names from SQL Server either get quoted forever or get renamed once.
  • Sort order. uuid values and text under a different collation sort differently. Reports and pagination that depend on order will change.

What to test before cutover

-- Run on both sides, per table, and compare.
SELECT count(*)                       AS row_count,
       count(DISTINCT customer_email) AS distinct_emails,
       min(created_at), max(created_at),
       sum(amount)                    AS amount_total
FROM   orders;
  • Row counts and aggregate totals per table, on a repeatable schedule.
  • Distinct counts on text keys, to expose case and trailing-space differences.
  • Minimum and maximum of every timestamp column, to expose precision loss.
  • A sample of rows compared column by column for the tables that carry money.

Record each mapping decision with its reason. The team that operates the database after cutover will need to know why a column is citext or why an offset lives in its own column.

Where this leads

Datrick runs SQL Server to PostgreSQL migrations and operates the database afterward, with named engineers and a written scope. The service page describes the delivery model: SQL Server to PostgreSQL migration. For a first read of risk and effort, see the migration risk assessment.

Related: SQL Server to PostgreSQL migration · migration risk assessment · Production Migration Notes · PostgreSQL DBA services