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,SMALLINTmap tointeger,bigint,smallint.DECIMAL(p,s)andNUMERIC(p,s)map tonumeric(p,s).FLOATmaps todouble precision.REALmaps toreal.DATEmaps todate.TIMEmaps totime.UNIQUEIDENTIFIERmaps touuid.VARBINARYandIMAGEmap tobytea.VARCHAR(MAX),NVARCHAR(MAX),TEXT,NTEXTmap totext.IDENTITYcolumns map toGENERATED ... AS IDENTITY. Reset the sequence to the migrated maximum before the first insert.
Mappings that change behavior
TINYINThas no one-byte equivalent. Usesmallintand add aCHECKconstraint if the 0 to 255 range matters.BITmaps toboolean. Application code that writes1and0needs a cast or a driver setting. Find it before cutover, not after.DATETIMErounds to about three milliseconds.DATETIME2holds seven fractional digits. PostgreSQLtimestampholds six. The seventh digit is lost, which breaks equality comparisons against values captured from the source.DATETIMEOFFSETmaps totimestamptz, 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.MONEYmaps tonumeric(19,4). Avoid the PostgreSQLmoneytype; its formatting depends on locale settings.NVARCHAR(n)maps tovarchar(n). PostgreSQL counts characters in a UTF-8 database, so lengths carry over, but storage size per row changes.ROWVERSIONhas no equivalent. Applications that use it for optimistic locking need a version column maintained by a trigger or by the application.SQL_VARIANT,HIERARCHYID,GEOGRAPHYandGEOMETRYneed a design decision: separate typed columns, theltreeextension, 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
Smithandsmithwill stop matching. Options are thecitextextension, a nondeterministic ICU collation, or explicitlower()indexes. - Trailing spaces. SQL Server ignores trailing spaces in most string comparisons. PostgreSQL does not for
varcharandtext. - 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.
uuidvalues 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
