| Keys and identity |
IDENTITY |
PostgreSQL generates keys from a sequence the table owns, not from a property on the column. |
Created as GENERATED ... AS IDENTITY, or SERIAL on older targets, and the sequence is set past the highest loaded value so the next insert does not hit a duplicate key. |
UNIQUEIDENTIFIER |
Nothing to reconcile here - both engines have a native GUID type. |
Becomes UUID, stored as 16 bytes and comparable as a GUID rather than as text. |
| Numbers |
BIT |
SQL Server's BIT is a one-bit integer column; PostgreSQL has a real boolean. |
Becomes BOOLEAN, so the column reads as true/false instead of 1/0. |
MONEY, SMALLMONEY |
PostgreSQL's own money type depends on the server's locale setting, which makes it the wrong place to land an amount that has to stay exact. |
Both go to fixed-point NUMERIC - MONEY as NUMERIC(19,4) and SMALLMONEY as NUMERIC(10,4) - keeping the four decimal places and the full range of each. |
| Date and time |
DATETIME2, TIME |
Both engines store fractional seconds, but SQL Server goes to 100 ns and PostgreSQL stops at microseconds. |
The declared scale comes across, capped at the six digits PostgreSQL supports: DATETIME2(7) becomes TIMESTAMP(6) and TIME(7) becomes TIME(6). |
DATETIMEOFFSET |
The value carries a UTC offset with it, and most targets have nowhere to put one. |
Becomes TIMESTAMPTZ, which keeps the instant rather than the wall-clock reading. This is the one target where the zone does not have to be dropped or moved into a second column. |
| Text and XML |
NVARCHAR, NCHAR |
SQL Server keeps Unicode in the N types and everything else in the database's code page, where anything outside it was already lost before the migration started. |
Land on VARCHAR and CHAR in a UTF8 database, so Cyrillic, CJK and emoji arrive intact. PostgreSQL has one text encoding per database, so there is no second, narrower type to fall into. |
NVARCHAR(max), VARCHAR(max) |
The max forms hold up to 2 GB, and PostgreSQL does not declare a length for unbounded text. |
Created as TEXT, which has no length limit and is stored out of line automatically when a value is large. |
XML |
Nothing to reconcile - PostgreSQL has an xml type of its own. |
Stays XML, transcoded into the database encoding, so xpath() and the rest of PostgreSQL's XML functions work on it. |
| Binary |
VARBINARY, ROWVERSION |
SQL Server's TIMESTAMP / ROWVERSION is a row-change counter, not a date - a name that has misled more than one migration. |
Both become BYTEA. The row-version column arrives as the bytes it was; it stops advancing, because the counter belonged to the source server. |
| Structure |
| Identifier case |
PostgreSQL folds unquoted names to lower case, so OrderHeader becomes orderheader unless every reference to it is quoted forever after. |
Mixed-case names are normalised to lower case by default, or the quoting is preserved as it was - you pick the policy in the wizard rather than discovering it at application boot. |
| Index names |
SQL Server index names only have to be unique within their table; PostgreSQL requires them to be unique across the whole schema. |
Colliding names are disambiguated on the way in, so two tables that each had an IX_Name both keep their index. |
Windows 10 or later