Amazon SCT Action Code Index
The following table shows the icons we use to describe the automation levels of Amazon Schema Conversion Tool (Amazon SCT) and Amazon Database Migration Service (Amazon DMS).
Automation level icon | Description |
---|---|
|
Full automation. Amazon SCT performs fully automatic conversion, no manual conversion needed. |
|
High automation. Minor, simple manual conversions may be needed. |
|
Medium automation. Low-medium complexity manual conversions may be needed. |
|
Low automation. Medium-high complexity manual conversions may be needed. |
|
Very low automation. High risk or complex manual conversions may be needed. |
|
No automation. Not currently supported by Amazon SCT, manual conversion is required for this feature. |
The following sections list the Amazon Schema Conversion Tool Action codes for topics that are covered in this playbook.
Note
The links in the table point to the Microsoft SQL Server topic pages, which are immediately followed by the PostgreSQL pages for the same topics.
Creating Tables
Amazon SCT automatically converts the most commonly used constructs of the CREATE TABLE
statement as both SQL Server and Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) support the entry level American National Standards Institute (ANSI) compliance. These items include table names, containing security schema or database, column names, basic column data types, column and table constraints, column default values, primary, UNIQUE
, and foreign keys. Some changes may be required for computed columns and global temporary tables.
For more information, see Creating Tables.
Action code | Action message |
---|---|
7659 |
If you use recursion, make sure that table variables in your source database and temporary tables in your target database have the same scope. |
7665 |
PostgreSQL doesn’t support |
7678 |
Amazon SCT replaced computed columns with regular columns in the converted code. |
7679 |
Amazon SCT replaced computed columns with triggers in the converted code. |
7680 |
PostgreSQL doesn’t support global temporary tables. |
7812 |
Make sure that you remove the temporary table before the end of the function. |
7835 |
PostgreSQL doesn’t support |
Data Types
Data type syntax and rules are very similar between SQL Server and Aurora PostgreSQL and most are converted automatically by Amazon SCT. Note that date and time handling paradigms are different for SQL Server and Aurora PostgreSQL and require manual verification or conversion. Also note that due to differences in data type behavior between SQL Server and Aurora PostgreSQL, manual verification and strict testing are highly recommended.
For more information, see Data Types.
Action code | Action message |
---|---|
7657 |
PostgreSQL doesn’t support the |
7658 |
PostgreSQL doesn’t support the |
7662 |
PostgreSQL doesn’t support the |
7664 |
PostgreSQL doesn’t support the |
7690 |
PostgreSQL doesn’t support table types. |
7706 |
Amazon SCT can’t convert the declaration of a variable of the unsupported %s data type. |
7707 |
Amazon SCT can’t convert the usage of a variable of the unsupported %s data type. |
7708 |
Amazon SCT can’t convert the usage of the unsupported %s data type. |
7773 |
Amazon SCT can’t convert arithmetic operations with dates. |
7775 |
Converted code might lose accuracy compared to the source code. |
Collations
The collation paradigms of SQL Server and Aurora PostgreSQL are significantly different. Amazon SCT can’t migrate collations automatically to PostgreSQL.
For more information, see SQL Server Collations and PostgreSQL Encoding.
Action code | Action message |
---|---|
7646 |
Amazon SCT can’t convert collations. |
PIVOT and UNPIVOT
Aurora PostgreSQL version 10 doesn’t support PIVOT
and UNPIVOT
clauses. Amazon SCT can’t automatically convert PIVOT
and UNPIVOT
clauses.
For more information, see PIVOT and UNPIVOT.
Action code | Action message |
---|---|
7905 |
PostgreSQL doesn’t support |
7906 |
PostgreSQL doesn’t support |
TOP and FETCH
Aurora PostgreSQL supports the non-ANSI compliant but popular with other engines LIMIT… OFFSET
operator for paging results sets. Amazon SCT can’t automatically convert some options such as WITH TIES
. These options require manual conversion.
For more information, see SQL Server TOP and FETCH and PostgreSQL LIMIT and OFFSET.
Action code | Action message |
---|---|
7605 |
PostgreSQL doesn’t support the |
7796 |
PostgreSQL doesn’t support |
7798 |
PostgreSQL doesn’t support |
7799 |
PostgreSQL doesn’t support |
Cursors
PostgreSQL has PL/pgSQL cursors that enable you to iterate business logic on rows read from the database. They can encapsulate the query and read the query results a few rows at a time. All access to cursors in PL/pgSQL is performed through cursor variables, which are always of the refcursor
data type. There are specific options which aren’t supported for automatic conversion by Amazon SCT.
For more information, see Cursors.
Action code | Action message |
---|---|
7637 |
PostgreSQL doesn’t support global cursors. |
7639 |
PostgreSQL doesn’t support dynamic cursors. |
7700 |
Amazon SCT can’t convert the |
7701 |
Amazon SCT doesn’t convert the |
7702 |
Amazon SCT doesn’t convert the |
7704 |
PostgreSQL doesn’t support the |
7705 |
PostgreSQL doesn’t support the |
7803 |
PostgreSQL doesn’t support the |
Flow Control
Although the flow control syntax of SQL Server differs from Aurora PostgreSQL, Amazon SCT can convert most constructs automatically including loops, command blocks, and delays. Aurora PostgreSQL doesn’t support the GOTO
and WAITFOR TIME
commands, which require manual conversion.
For more information, see SQL Server Flow Control and PostgreSQL Control Structures.
Action code | Action message |
---|---|
7628 |
PostgreSQL doesn’t support |
7691 |
PostgreSQL doesn’t support the |
7801 |
Make sure that your table isn’t locked by an open cursor. |
7802 |
Make sure that you delete the table that you created within the procedure before the end of the procedure. |
7810 |
PostgreSQL doesn’t support |
7821 |
Amazon SCT can’t convert the |
7826 |
Amazon SCT can’t convert the default value of the |
7827 |
Amazon SCT can’t convert default values. |
Transaction Isolation
Aurora PostgreSQL supports the four transaction isolation levels specified in the SQL:92 standard: READ UNCOMMITTED
, READ COMMITTED
, REPEATABLE READ
, and SERIALIZABLE
, all of which are automatically converted by Amazon SCT. Also, Amazon SCT converts BEGIN / COMMIT
and ROLLBACK
commands that use slightly different syntax. Manual conversion is required for named, marked, and delayed durability transactions that aren’t supported by Aurora PostgreSQL.
For more information, see Transactions.
Action code | Action message |
---|---|
7807 |
Amazon SCT can’t convert the transaction management command. PostgreSQL doesn’t support explicit transaction management commands such as |
Stored Procedures
Aurora PostgreSQL stored procedures provide very similar functionality to SQL Server stored procedures. You can automatically convert them with Amazon SCT. Manual conversion is required for procedures that use RETURN
values and some less common EXECUTE
options such as the RECOMPILE
and RESULTS SETS
.
For more information, see Stored Procedures.
Action code | Action message |
---|---|
7640 |
PostgreSQL doesn’t support |
7641 |
PostgreSQL doesn’t support |
7642 |
PostgreSQL doesn’t support |
7643 |
PostgreSQL doesn’t support |
7672 |
PostgreSQL doesn’t support |
7695 |
PostgreSQL doesn’t support support the call of a procedure as a variable. |
7800 |
PostgreSQL doesn’t support result sets in the SQL Server style. |
7830 |
Amazon SCT can’t convert arithmetic operations with the |
7838 |
Amazon SCT can’t convert |
7839 |
Converted code might not work correctly because of parameter names. |
Triggers
Aurora PostgreSQL supports BEFORE
and AFTER
triggers for INSERT
, UPDATE
, and DELETE
. However, Aurora PostgreSQL triggers differ substantially from SQL Server’s triggers. You can migrate the most common use cases with minimal code changes.
For more information, see Triggers.
Action code | Action message |
---|---|
7809 |
PostgreSQL doesn’t support |
7832 |
Amazon SCT can’t convert |
7909 |
Amazon SCT can’t convert |
MERGE
Aurora PostgreSQL version 10 doesn’t support MERGE
statements. Amazon SCT can’t automatically convert these statements. Manual conversion is straightforward in most cases.
For more information, see MERGE.
Action code | Action message |
---|---|
7915 |
Converted code might produce different results compared to the source code. Make sure that the constraint includes the %s column. |
7916 |
Amazon SCT can’t emulate the |
Query Hints
You can use Amazon SCT to convert basic query hints such as index hints, except for data manipulation language (DML) statements. Note that specific optimizations used for SQL Server may be completely inapplicable to a new query optimizer. Amazon recommends to start migration testing with all hints removed. Then, selectively apply hints as a last resort if other means such as schema, index, and query optimizations have failed. Plan guides aren’t supported by Aurora PostgreSQL.
For more information, see SQL Server Query Hints and Plan Guides and PostgreSQL DB Query Planning.
Action code | Action message |
---|---|
7823 |
PostgreSQL doesn’t support table hints in DML statements. |
Full-Text Search
Migrating full-text indexes from SQL Server to Aurora PostgreSQL requires a full rewrite of the code that deals with both creating, managing, and querying full-text indexes. Amazon SCT can’t automatically convert these statements.
For more information, see Full-Text Search.
Action code | Action message |
---|---|
7688 |
PostgreSQL doesn’t support |
Indexes
Basic non-clustered indexes, which are the most commonly used type of indexes are automatically migrated by Amazon SCT. In addition, filtered indexes, indexes with included columns, and some SQL Server specific index options can’t be migrated automatically and require manual conversion.
For more information, see Indexes.
Action code | Action message |
---|---|
7675 |
PostgreSQL doesn’t support |
7681 |
PostgreSQL doesn’t support clustered indexes. |
7682 |
PostgreSQL doesn’t support the |
7781 |
PostgreSQL doesn’t support the |
7782 |
PostgreSQL doesn’t support the |
7783 |
PostgreSQL doesn’t support the |
7784 |
PostgreSQL doesn’t support the |
7785 |
PostgreSQL doesn’t support the |
7786 |
PostgreSQL doesn’t support the |
7787 |
PostgreSQL doesn’t support the |
7788 |
PostgreSQL doesn’t support the |
7789 |
PostgreSQL doesn’t support the |
7790 |
PostgreSQL doesn’t support the |
7791 |
PostgreSQL doesn’t support the |
Partitioning
Aurora PostgreSQL uses table inheritance, some of the physical aspects of partitioning in SQL Server don’t apply to Aurora PostgreSQL. For example, the concept of file groups and assigning partitions to file groups. Aurora PostgreSQL supports a much richer framework for table partitioning than SQL Server, with many additional options such as hash partitioning, and sub partitioning.
For more information, see SQL Server Partitioning and PostgreSQL Partitions or Table Inheritance.
Action code | Action message |
---|---|
7910 |
PostgreSQL doesn’t support |
7911 |
PostgreSQL doesn’t support foreign keys referencing partitioned tables. |
7912 |
PostgreSQL doesn’t support foreign key references from partitioned tables to other tables. |
7913 |
PostgreSQL doesn’t support |
7914 |
Converted code might produce different results compared to the source code. |
Starting from version 11, PostgreSQL supports NULL
columns for partitioning. In this case, you can ignore the action item with the 7910 code and use NULL
columns for partitioning in your target tables.
Backup
Migrating from a self-managed backup policy to a Platform as a Service (PaaS) environment such as Aurora PostgreSQL is a complete paradigm shift. You don’t need to worry about transaction logs, file groups, disks running out of space, and purging old backups. Amazon Relational Database Service (Amazon RDS) provides guaranteed continuous backup with point in time restore up to 35 days. Therefore, Amazon SCT doesn’t automatically convert backups.
For more information, see Backup and Restore.
Action code | Action message |
---|---|
7903 |
PostgreSQL doesn’t support functionality similar to SQL Server Backup. |
SQL Server Mail
Aurora PostgreSQL doesn’t provide native support for sending emails from the database.
For more information, see Database Mail.
Action code | Action message |
---|---|
7900 |
PostgreSQL doesn’t support functionality similar to SQL Server Database Mail. |
Graph
Amazon SCT doesn’t convert graph database capabilities.
For more information and potential workarounds, see SQL Server Graph and PostgreSQL Apache AGE Extension.
Action code | Action message |
---|---|
7931 |
Amazon SCT can’t convert SQL Graph tables. |
7932 |
Amazon SCT can’t convert DML constructs of SQL Graph databases. |
SQL Server Agent
Aurora PostgreSQL doesn’t provide functionality similar to SQL Server Agent as an external, cross-instance scheduler. However, Aurora PostgreSQL provides a native, in-database scheduler. It is limited to the cluster scope and can’t be used to manage multiple clusters. Therefore, Amazon SCT can’t automatically convert Agent jobs and alerts.
For more information, see SQL Server Agent and PostgreSQL Scheduled Lambda.
Action code | Action message |
---|---|
7902 |
PostgreSQL doesn’t support functionality similar to SQL Server Agent. |
Service Broker
Aurora PostgreSQL doesn’t provide a compatible solution to the SQL Server Service Broker. However, you can use DB Links and Amazon Lambda to achieve similar functionality.
For more information, see SQL Server Service Broker Essentials.
Action code | Action message |
---|---|
7901 |
PostgreSQL doesn’t support functionality similar to SQL Server Service Broker. |
XML
The XML options and features in Aurora PostgreSQL are similar or almost identical to SQL Server XPATH
and XQUERY
functions. PostgreSQL doesn’t support FOR
XML clause, the walkaround for that is using string_agg
instead. In some cases, it might be more efficient to use JSON instead of XML.
For more information, see JSON and XML.
Action code | Action message |
---|---|
7816 |
PostgreSQL doesn’t support methods for the XML data type. |
7817 |
PostgreSQL doesn’t support the |
7920 |
PostgreSQL doesn’t support |
7924 |
PostgreSQL doesn’t support XPath queries that return multiple elements. |
Constraints
Constraints feature is almost fully automated and compatible between SQL Server and Aurora PostgreSQL. The differences are: missing SET DEFAULT
and check constraint with sub-query.
For more information, see SQL Server Constraints and PostgreSQL Table Constraints.
Action code | Action message |
---|---|
7606 |
PostgreSQL doesn’t support foreign keys that reference partitioned tables. |
7675 |
PostgreSQL doesn’t support |
7825 |
Amazon SCT removed the default value of the |
7915 |
Converted code might produce different results compared to the source code. Make sure that the constraint includes the %s column. |
Linked Servers
Aurora PostgreSQL supports remote data access from the database. Connectivity between schemas is trivial, but connectivity to other instances require an extension installation.
For more information, see SQL Server Linked Servers and PostgreSQL DBLink and FDWrapper.
Action code | Action message |
---|---|
7645 |
PostgreSQL doesn’t support running pass-through commands on linked servers. |
Synonyms
Aurora PostgreSQL supports synonyms. If synonyms refer to tables, views, or functions, you can replace them with views or functions to wrap those. It becomes more challenging when synonyms refer to other objects.
For more information, see SQL Server Synonyms and PostgreSQL Views, Types, and Functions.
Action code | Action message |
---|---|
7792 |
PostgreSQL doesn’t support synonyms. |