View a markdown version of this page

Data type rules - AWS Database Migration Service

Data type rules

The following rules apply when you convert a Microsoft SQL Server database to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL.

Context matters

Some data type rules depend on where the type is used, such as a table column, a variable inside a procedure, or a routine parameter. The same source type can convert differently in each context.

Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL share one rule set, so the rules on this page apply to both targets.

Table columns, procedure and function parameters

The following rules apply to table columns and to procedure and function parameters.

Source data typeApplies whenTarget data typeNotes
BIGINTAlwaysBIGINTNone
BINARYAlwaysBYTEANone
BITAlwaysNUMERIC(1,0)None
CHAR(n)Length is specifiedCHAR(n)None
CHARNo length specifiedCHAR(1)None
DATEAlwaysDATENone
DATETIMEAlwaysTIMESTAMP WITHOUT TIME ZONENone
DATETIME2(p)Precision ≤ 6TIMESTAMP(p) WITHOUT TIME ZONENone
DATETIME2All other casesTIMESTAMP(6) WITHOUT TIME ZONENone
DATETIMEOFFSET(p)Precision ≤ 6TIMESTAMP(p) WITH TIME ZONENone
DATETIMEOFFSETAll other casesTIMESTAMP(6) WITH TIME ZONENone
DECIMALIdentity column, precision 1–5SMALLINTNone
DECIMALIdentity column, precision 6–13INTEGERNone
DECIMALIdentity column, precision 14–19BIGINTNone
DECIMALIdentity column, precision 20–38BIGINTAI 7936: PostgreSQL doesn't support identity columns of the DECIMAL or NUMERIC data type with precision greater than 19
DECIMAL(p,s)Precision and scale are specifiedNUMERIC(p,s)None
DECIMAL(p)Precision is specifiedNUMERIC(p,0)None
DECIMALAll other casesNUMERIC(18,0)None
DOUBLE PRECISIONAlwaysDOUBLE PRECISIONNone
FLOATAlwaysDOUBLE PRECISIONNone
GEOGRAPHYAlwaysGEOGRAPHYNone
GEOMETRYAlwaysGEOMETRYNone
HIERARCHYIDAlwaysVARCHAR(8000)AI 7657: PostgreSQL doesn't support the hierarchyid data type
IMAGEAlwaysBYTEANone
INTAlwaysINTEGERNone
MONEYAlwaysNUMERIC(19,4)None
NCHAR(n)Length between 1 and 4000CHAR(n)None
NCHARNo length specifiedCHAR(1)None
NTEXTAlwaysTEXTNone
NUMERICIdentity column, precision 1–5SMALLINTNone
NUMERICIdentity column, precision 6–13INTEGERNone
NUMERICIdentity column, precision 14–19BIGINTNone
NUMERICIdentity column, precision 20–38BIGINTAI 7936: PostgreSQL doesn't support identity columns of the DECIMAL or NUMERIC data type with precision greater than 19
NUMERIC(p,s)Precision and scale are specifiedNUMERIC(p,s)None
NUMERIC(p)Precision is specifiedNUMERIC(p,0)None
NUMERICAll other casesNUMERIC(18,0)None
NVARCHARLength is MAXTEXTNone
NVARCHAR(n)Length ≥ 1VARCHAR(n)None
NVARCHARNo length specifiedVARCHAR(1)None
REALAlwaysDOUBLE PRECISIONNone
ROWVERSIONAlwaysBIGINTNone
SMALLDATETIMEAlwaysTIMESTAMP WITHOUT TIME ZONENone
SMALLINTAlwaysSMALLINTNone
SMALLMONEYAlwaysNUMERIC(10,4)None
SQL_VARIANTAlwaysVARCHAR(8000)AI 7658: PostgreSQL doesn't support the sql_variant data type
SYSNAMEAlwaysVARCHAR(128)None
TEXTAlwaysTEXTNone
TIME(p)Precision ≤ 6TIME(p) WITHOUT TIME ZONENone
TIMEAll other casesTIME(6) WITHOUT TIME ZONENone
TIMESTAMPAlwaysBIGINTNone
TINYINTAlwaysSMALLINTNone
UNIQUEIDENTIFIERAlwaysUUIDNone
VARBINARYAlwaysBYTEANone
VARCHARLength is MAXTEXTNone
VARCHAR(n)Length between 1 and 8000VARCHAR(n)None
VARCHARNo length specifiedVARCHAR(1)None
XMLAlwaysXMLNone
any unrecognized typeAlwaysVARCHAR(8000)None

CAST and CONVERT

The following rules apply to the target data type in a CAST or CONVERT expression. When a character type has no length, SQL Server uses a length of 30 in these expressions, and DMS Schema Conversion keeps that length. All other data types convert as described in Table columns, procedure and function parameters.

Source data typeApplies whenTarget data typeNotes
CHARNo length specifiedCHAR(30)None
NCHARNo length specifiedCHAR(30)None
NVARCHARNo length specifiedVARCHAR(30)None
VARCHARNo length specifiedVARCHAR(30)None