Data types for ANSI SQL

This topic provides reference information about data type compatibility between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can use this information to understand how various SQL Server data types map to their PostgreSQL equivalents during migration

SQL Server Usage

In SQL Server, each table column, variable, expression, and parameter has an associated data type. SQL Server provides a rich set of built-in data types as summarized in the following table.

You can create custom user defined data types using T-SQL, and the .NET Framework. Custom data types are based on the built-in system data types and are used to simplify development. For more information, see User-Defined Types.

TEXT, NTEXT, and IMAGE Deprecated Data Types

The TEXT, NTEXT, and IMAGE data types have been deprecated as of SQL Server 2008R2. For more information, see Deprecated Database Engine Features in SQL Server 2008 R2 in the SQL Server documentation.

These data types are legacy types for storing BLOB and CLOB data. The TEXT data type was used to store ASCII text CLOBS, the NTEXT data type to store UNICODE CLOBS, and IMAGE was used as a generic data type for storing all BLOB data. In SQL Server 2005, Microsoft introduced the new and improved VARCHAR (MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types as the new BLOB and CLOB standard. These new types support a wider range of functions and operations. They also provide enhanced performance over the legacy types.

If your code uses TEXT, NTEXT or IMAGE data types, AWS SCT automatically converts them to the appropriate Aurora PostgreSQL BYTEA data type. Also, AWS SCT converts TEXT and NTEXT data types to LONGTEXT and IMAGE to LONGBLOB. Make sure you use the proper collations. For more information, see the SQL Server Collations and PostgreSQL Encoding.

Examples

Define table columns.

CREATE TABLE MyTable
(
Col1 AS INTEGER NOT NULL PRIMARY KEY,
Col2 AS NVARCHAR(100) NOT NULL
);

Define variable types.

DECLARE @MyXMLType AS XML,
  @MyTemporalType AS DATETIME2

DECLARE @MyTableType
AS TABLE
(
Col1 AS BINARY(16) NOT NULL PRIMARY KEY,
Col2 AS XML NULL
);

For more information, see Data types (Transact-SQL) in the SQL Server documentation.

PostgreSQL Usage

PostgreSQL provides multiple data types equivalent to certain SQL Server data types. The following tables include the full list of PostgreSQL data types.

Character data types

Numeric data types

Temporal data types

Binary data types

LOB data types

Spatial data types

Other data types

PostgreSQL Character Column Semantics

PostgreSQL only supports CHAR for column size semantics. If you define a field as VARCHAR (10), PostgreSQL can store 10 characters regardless of how many bytes it takes to store each non-English character. VARCHAR(n) stores strings up to n characters, not bytes, in length.

Migration of SQL Server Data Types to PostgreSQL Data Types

You can use AWS Schema Conversion Tool (AWS SCT) for automatic migration and conversion of SQL Server tables and data types.

Examples

To demonstrate AWS SCT capability for migrating SQL Server tables to their PostgreSQL equivalents, a table containing columns representing the majority of SQL Server data types was created and converted using AWS SCT.

Source SQL Server compatible DDL for creating the DATATYPES table

CREATE TABLE "DataTypes"(
  "BINARY_FLOAT" REAL,
  "BINARY_DOUBLE" FLOAT,
  "BLOB" VARBINARY(4000),
  "CHAR" CHAR(10),
  "CHARACTER" CHAR(10),
  "CLOB" VARCHAR(4000),
  "DATE" DATE,
  "DECIMAL" NUMERIC(3,2),
  "DOUBLE_PRECISION" FLOAT(52),
  "FLOAT" FLOAT(3),
  "INTEGER" INTEGER,
  "LONG" TEXT,
  "NCHAR" NCHAR(10),
  "NUMBER" NUMERIC(9,9),
  "NUMBER1" NUMERIC(9,0),
  "NUMERIC" NUMERIC(9,9),
  "RAW" BINARY(10),
  "REAL" FLOAT(52),
  "SMALLINT" SMALLINT,
  "TIMESTAMP" TIMESTAMP,
  "TIMESTAMP_WITH_TIME_ZONE" DATETIMEOFFSET(5),
  "VARCHAR" VARCHAR(10),
  "VARCHAR2" VARCHAR(10),
  "XMLTYPE" XML
);

Target PostgreSQL compatible DDL for creating the DATATYPES table migrated from SQL Server with AWS SCT.

CREATE TABLE IF NOT EXISTS datatypes(
  binary_float real DEFAULT NULL,
  binary_double double precision DEFAULT NULL,
  blob bytea DEFAULT NULL,
  char character(10) DEFAULT NULL,
  character character(10) DEFAULT NULL,
  clob text DEFAULT NULL,
  date TIMESTAMP(0) without time zone DEFAULT NULL,
  decimal numeric(3,2) DEFAULT NULL,
  dec numeric(3,2) DEFAULT NULL,
  double_precision double precision DEFAULT NULL,
  float double precision DEFAULT NULL,
  integer numeric(38,0) DEFAULT NULL,
  long text DEFAULT NULL,
  nchar character(10) DEFAULT NULL,
  number numeric(9,9) DEFAULT NULL,
  number1 numeric(9,0) DEFAULT NULL,
  numeric numeric(9,9) DEFAULT NULL,
  raw bytea DEFAULT NULL,
  real double precision DEFAULT NULL,
  smallint numeric(38,0) DEFAULT NULL,
  timestamp TIMESTAMP(5) without time zone DEFAULT NULL,
  timestamp_with_time_zone TIMESTAMP(5) with time zone DEFAULT NULL,
  varchar character varying(10) DEFAULT NULL,
  varchar2 character varying(10) DEFAULT NULL,
  xmltype xml DEFAULT NULL
)
WITH (
  OIDS=FALSE
);

Summary

AWS SCT converts all incompatible data types.

SQL Server CREATE TABLE command:

CREATE TABLE scttest(
SMALLDATETIMEcol SMALLDATETIME,
datetimecol DATETIME,
datetime2col DATETIME2,
datetimeoffsetcol DATETIMEOFFSET,
binarycol BINARY,
varbinarycol VARBINARY,
ntextcol NTEXT,
imagecol IMAGE,
nvarcharmaxcol NVARCHAR(MAX),
varbinarymaxcol VARBINARY(MAX),
uniqueidentifiercol UNIQUEIDENTIFIER,
hierarchyiDcol HIERARCHYID,
sql_variantcol SQL_VARIANT,
rowversioncol ROWVERSION);

The equivalent command that was created by AWS SCT:

CREATE TABLE scttest(
smalldatetimecol TIMESTAMP WITHOUT TIME ZONE,
datetimecol TIMESTAMP WITHOUT TIME ZONE,
datetime2col TIMESTAMP(6) WITHOUT TIME ZONE,
datetimeoffsetcol TIMESTAMP(6) WITH TIME ZONE,
binarycol BYTEA,
varbinarycol BYTEA,
ntextcol TEXT,
imagecol BYTEA,
nvarcharmaxcol TEXT,
varbinarymaxcol BYTEA,
uniqueidentifiercol UUID,
hierarchyidcol VARCHAR(8000),
sql_variantcol VARCHAR(8000),
rowversioncol VARCHAR(8000) NOT NULL);

For more information, see System Columns and Data Types in the PostgreSQL documentation, and Schema Conversion Tool Documentation.