Menu
AWS Schema Conversion Tool
User Guide (Version 1.0)

Microsoft SQL Server to PostgreSQL Conversion Issue Reference

Use the following sections to get information on the types of issues you might encounter during a Microsoft SQL Server to PostgreSQL conversion. Choose the link in the Issue column to get more detailed resolution information about the issue if it is available.

Arithmetic Operators

Item Issue Resolution

Arithmetic operators

Issue 7775: Check the data type conversion for possible loss of accuracy

Cast values as the intended type.

Arithmetic operators and date types

Issue 7773: Unable to perform an automated migration of arithmetic operations with dates

Cast values as the intended type.

Arithmetic operators and mixed types

Issue 7774: Unable to perform an automated migration of the arithmetic operations with mixed types of operands

Cast values as the intended type.

Built-In Services

Item Issue Resolution

Database Mail

Issue 7900: PostgreSQL does not have functionality similar to SQL Server Database Mail

Try using the Amazon Simple Notification Service (Amazon SNS).

Service Broker

Issue 7901: PostgreSQL does not have functionality similar to SQL Server Service Broker

Try using the Amazon Simple Queue Service (Amazon SQS).

SQL Server Agent

Issue 7902: PostgreSQL does not have functionality similar to SQL Server Agent

Try using the AWS Lambda service with Scheduled Events.

SQL Server Backup

Issue 7903: PostgreSQL does not have functionality similar to SQL Server Backup

Try using the Amazon Glacier storage service.

Built-In SQL Functions

Item Issue Resolution

SOUNDEX

Issue 7811: PostgreSQL doesn't support the SOUNDEX function

Create a user-defined function.

CONTROL FLOW

Item Issue Resolution
Cursors

Issue 7801: The table can be locked open cursor

Review your transformed code and modify it if necessary.

DECLARE / DEFAULT VALUE

Issue 7826: Check the default value for a DateTime variable

Check the default value for a DateTime variable.

GOTO

Issue 7628: PostgreSQL doesn't support the GOTO option. Automatic conversion can't be performed.

Revise your code to eliminate GOTO operators, using BEGIN...END blocks in combination with EXIT, REPEAT, UNTIL, and WHILE operators.

Procedures

Issue 7802: A table that is created within the procedure, must be deleted before the end of the procedure

Review your transformed code and modify it if necessary.

Result sets

Issue 7800: PostgreSQL doesn't support result sets in the style of MSSQL

Review your transformed code and modify it if necessary.

WAITFOR DELAY

Issue 7821: Automatic conversion operator WAITFOR with a variable is not supported

Perform a manual conversion.

WAITFOR TIME

Issue 7691: PostgreSQL doesn't support WAITFOR TIME feature

Perform a manual conversion.

CREATE

Item Issue Resolution

Issue 7696: Unable convert object due to %s not created

Review the %s object.

Encrypted objects

Issue 7813: Encrypted objects

Decrypt the object before conversion.

CURSORS

Item Issue Resolution

CURSOR with option GLOBAL

Issue 7637: PostgreSQL doesn't support GLOBAL CURSORS. Requires manual conversion.

Change the global cursor to a local cursor, or revise your code so it doesn't require global cursors.

CURSOR with options STATIC or KEYSET or DYNAMIC or FAST_FORWARD

Issue 7639: PostgreSQL doesn't support DYNAMIC cursors

Revise your code to eliminate dynamic cursors.

FAST_FORWARD option

Issue 7701: Setting this option corresponds to the typical behavior of cursors in PostgreSQL, so this option is skipped

Use cursors without this option.

FOR UPDATE

Issue 7803: PostgreSQL doesn't support the option FOR UPDATE, so this option is skipped

Review your transformed code and modify it if necessary.

KEYSET option

Issue 7700: The membership and order of rows never changes for cursors in PostgreSQL, so this option is skipped

Use cursors without this option.

OPTIMISTIC option

Issue 7704: PostgreSQL doesn't support the option OPTIMISTIC, so this option is skipped

Use cursors without this option.

READ_ONLY option

Issue 7702: All PostgreSQL cursors are read-only, so this option is skipped

Use cursors without this option.

TYPE_WARNING option

Issue 7705: PostgreSQL doesn't support the option TYPE_WARNING, so this option is skipped

Use cursors without this option.

DATA TYPES

Item Issue Resolution

BYTEA

Issue 7818: PostgreSQL doesn't support arithmetic operations with binary data types

Perform a manual conversion.

geography

Issue 7662: PostgreSQL doesn't support this type. A manual conversion is required.

To store data of this type in PostgreSQL, use a PostgreSQL-compatible type or use a composite type.

geometry

Issue 7664: PostgreSQL doesn't support this type. A manual conversion is required.

To store data of this type in PostgreSQL, use a PostgreSQL-compatible type or use a composite type.

hierarchyid

Issue 7657: PostgreSQL doesn't support this type. A manual conversion is required.

To store data of this type in PostgreSQL, use a PostgreSQL-compatible type or use a composite type.

rowversion

Issue 7706: PostgreSQL doesn't support this type

Perform a manual conversion.

sql_variant

Issue 7658: PostgreSQL doesn't support this type. A manual conversion is required.

To store data of this type in PostgreSQL, use a PostgreSQL-compatible type or use a composite type.

table

Issue 7659: The scope table-variables and temporary tables is different. You must apply manual conversion, if you are using recursion.

Perform a manual conversion.

UDT

Issue 7690: PostgreSQL doesn't support table types

Perform a manual conversion.

XML

Issue 7816: PostgreSQL doesn't support any methods for data type XML

Perform a manual conversion.

XML

Issue 7817: PostgreSQL doesn't support option [for xml path] in the SQL queries

Perform a manual conversion.

DDL

Item Issue Resolution

CREATE INDEX

Issue 7675: PostgreSQL doesn't support sorting options (ASC | DESC) for constraints

Use indexes without this option.

CREATE INDEX

Issue 7681: PostgreSQL doesn't support clustered indexes

Use nonclustered indexes.

CREATE INDEX

Issue 7682: PostgreSQL doesn't support the INCLUDE option in indexes

Use index without this option.

CREATE INDEX

Issue 7781: PostgreSQL doesn't support the PAD_INDEX option in indexes

Use index without this option.

CREATE INDEX

Issue 7782: PostgreSQL doesn't support the SORT_IN_TEMPDB option in indexes

Use index without this option.

CREATE INDEX

Issue 7783: PostgreSQL doesn't support the IGNORE_DUP_KEY option in indexes

Use index without this option.

CREATE INDEX

Issue 7784: PostgreSQL doesn't support the STATISTICS_NORECOMPUTE option in indexes

Use index without this option.

CREATE INDEX

Issue 7785: PostgreSQL doesn't support the STATISTICS_INCREMENTAL option in indexes

Use index without this option.

CREATE INDEX

Issue 7786: PostgreSQL doesn't support the DROP_EXISTING option in indexes

Use index without this option.

CREATE INDEX

Issue 7787: PostgreSQL doesn't support the ONLINE option in indexes

Use index without this option.

CREATE INDEX

Issue 7788: PostgreSQL doesn't support the ALLOW_ROW_LOCKS option in indexes

Use index without this option.

CREATE INDEX

Issue 7789: PostgreSQL doesn't support the ALLOW_PAGE_LOCKS option in indexes

Use index without this option.

CREATE INDEX

Issue 7790: PostgreSQL doesn't support the MAXDOP option in indexes

Use index without this option.

CREATE INDEX

Issue 7791: PostgreSQL doesn't support the DATA_COMPRESSION option in indexes

Use index without this option.

CREATE TABLE

Issue 7679: PostgreSQL doesn't support computed columns

Try using a trigger.

CREATE TABLE

Issue 7680: PostgreSQL doesn't support global temporary tables

Use local temporary or regular tables.

CREATE TABLE

Issue 7812: Temporary table must be removed before the end of the function

Review your transformed code and modify it if necessary.

CREATE TABLE

Issue 7825: The default value for a DateTime column removed

Review generated code and modify it if necessary.

Inline Function

Issue 7776: Automatic migration of inline functions not supported

Perform a manual conversion.

DELETE

DML, FROM

Item Issue Resolution

Table Hints

Issue 7823: PostgreSQL doesn't support table hints in DML statements

Use PostgreSQL methods of performance tuning.

EXECUTE

Item Issue Resolution

Issue 7695: PostgreSQL doesn't support the execution of a procedure as a variable

Perform a manual conversion using dynamic SQL.

EXECUTE a character string

Issue 7672: Automatic conversion of this command is not supported

Perform a manual conversion.

Execute a pass-through command against a linked server

Issue 7645: PostgreSQL doesn't support executing a pass-through command on a linked server

Use other methods to execute statements on a remote server.

EXECUTE with execute options [RECOMPILE]

Issue 7640: The EXECUTE with RECOMPILE option is ignored

Use EXECUTE command without this option.

EXECUTE with execute options [RESULT SETS (<result_sets_definition>)]

Issue 7643: The EXECUTE with RESULT SETS <result set definition> option is ignored

Use EXECUTE command without this option.

EXECUTE with execute options [RESULT SETS NONE]

Issue 7642: The EXECUTE with RESULT SETS NONE option is ignored

Use EXECUTE command without this option.

EXECUTE with execute options [RESULT SETS UNDEFINED]

Issue 7641: The EXECUTE with RESULT SETS UNDEFINED option is ignored

Use EXECUTE command without this option.

INSERT

Item Issue Resolution

EXECUTE

Issue 7819: PostgreSQL doesn't support INSERT...EXECUTE statements

Perform a manual conversion.

TOP

Issue 7799: PostgreSQL doesn't support TOP option in the operator INSERT

Perform a manual conversion.

Operators

Item Issue Resolution

Bitwise Operators

Issue 7804: PostgreSQL doesn't support operator [Bitwise exclusive OR]

Perform a manual conversion.

Comparison Operators

Issue 7805: PostgreSQL doesn't support operator [Not less than]

Perform a manual conversion.

Comparison Operators

Issue 7806: PostgreSQL doesn't support operator [Not greater than]

Perform a manual conversion.

Parser Error

Item Issue Resolution

Parser Error

Issue 7663: Unable to resolve the object

Verify if object <object name> is present in the database. If it isn't, check the object name or add the object. If the object is present, transform the code manually.

SELECT

Item Issue Resolution

GROUP BY CUBE

Issue 7653: PostgreSQL doesn't support the option GROUP BY ROLLUP. Automatic conversion can't be performed.

Try creating a stored procedure to replace the query.

GROUP BY CUBE

Issue 7654: PostgreSQL doesn't support the option GROUP BY CUBE. Automatic conversion can't be performed.

Try creating a stored procedure to replace the query.

Group By Grouping Sets

Issue 7655: PostgreSQL doesn't support the option GROUP BY GROUPING SETS. Automatic conversion can't be performed.

Try creating a stored procedure to replace the query.

ORDER BY Specifying a collation

Issue 7646: PostgreSQL doesn't support the COLLATE option in the ORDER BY clause. Automatic conversion can't be performed.

You must use the COLLATION settings that were assigned when the database was created.

search condition

Issue 7687: PostgreSQL doesn't support the CONTAINS predicate

Perform a manual conversion.

search condition

Issue 7688: PostgreSQL doesn't support the FREETEXT predicate

Perform a manual conversion.

search condition

Issue 7795: PostgreSQL is case sensitive. Check the string comparison.

Check the string comparison.

TOP WITH TIES

Issue 7605: PostgreSQL doesn't support the WITH TIES option

Perform a manual conversion.

SEQUENCE

Item Issue Resolution

Specific data type

Issue 7793: PostgreSQL doesn't support changing data type in sequences

Review your transformed code and modify it if necessary.

SYNONYMS

Item Issue Resolution

CREATE

Issue 7792: PostgreSQL doesn't support synonyms

Replace a synonym with original database object. If original object is table or view you can try to create a view in one database that relies on a table or view in another database.

SYS objects

Item Issue Resolution

SYS objects

Issue 7904: Unable to convert system object <object name>

Perform a manual conversion.

TRANSACTION

Item Issue Resolution

Transaction

Issue 7807: Transactions in functions PostgreSQL doesn't support

Perform a manual conversion.

TRIGGERS

Item Issue Resolution

Triggers

Issue 7809: In PostgreSQL there are no analogues of the tables 'inserted' and 'deleted'

Perform a manual conversion.

Unknown

Item Issue Resolution

Issue 7627: This syntactic element conversion is not supported yet

Perform a manual conversion.

Unknown Clause

Issue 7674: Automatic conversion of <clause name> clause of <statement name> statement is not supported

Perform a manual conversion.

Unparsed SQL

Issue 7808: Unparsed SQL

Perform a manual conversion.

UPDATE

User Types

Item Issue Resolution

User-Defined Data Types

Issue 7794: PostgreSQL doesn't support user-defined data types

Perform a manual conversion.