Migrating from SQL Server to PostgreSQL with AWS Schema Conversion Tool

To limit the number of comments with action items in the converted code.

For Add comments in the converted code for the action items of selected severity and higher, choose the severity of action items. AWS SCT adds comments in the converted code for action items of the selected severity and higher.

For example, to minimize the number of comments in your converted code, choose Errors only. To include comments for all action items in your converted code, choose All messages.

To allow to use indexes with the same name in different tables in SQL Server.

In PostgreSQL, all index names that you use in the schema, must be unique. To make sure that AWS SCT generates unique names for all your indexes, select Generate unique names for indexes.

To convert SQL Server procedures to PostgreSQL functions.

PostgreSQL version 10 and earlier doesn't support procedures. For customers who aren't familiar with using procedures in PostgreSQL, AWS SCT can convert procedures to functions. To do so, select Convert procedures to functions.

To emulate the output of EXEC in a table.

Your source SQL Server database can store the output of EXEC in a table. AWS SCT creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select Create additional routines for handling open datasets.

To define the template to use for the schema names in the converted code. For Schema name generation template, choose one of the following options:

To keep the letter case of your source object names.

To avoid conversion of object names to lower case, select Avoid casting to lower case for case sensitive operations. This option applies only when you turn on case sensitivity option in your target database.

To keep the parameter names from your source database.

To add double quotation marks to the names of parameters in the converted code, select Keep original parameter names.

SQL Server allows you to partition a table using a column without a NOT NULL constraint. In that case, all NULL values go to the leftmost partition. PostgreSQL doesn’t support NULL values for RANGE partitioning.

SQL Server allows you to create primary and unique keys for partitioned tables. For PostgreSQL, you create primary or unique keys for each partition directly. Thus, PRIMARY or UNIQUE KEY constraint must be removed from their parent table when migrating to PostgreSQL. The resulting key names take the format <original_key_name>_<partition_number>.

SQL Server allows you to create foreign key constraint from and to partitioned tables. PostgreSQL doesn’t support foreign keys referencing partitioned tables. Also, PostgreSQL doesn’t support foreign key references from a partitioned table to another table.

SQL Server allows you to create indexes for partitioned tables. For PostgreSQL, an index should be created for each partition directly. Thus, indexes must be removed from their parent tables when migrating to PostgreSQL. The resulting index names take the format <original_index_name>_<partition_number>.

PostgreSQL doesn’t support partitioned indexes.

In PostgreSQL, all object’s names in a schema must be unique, including indexes. Index names must be unique in the schema of the base table. In SQL Server, an index name can be the same for different tables.

To ensure the uniqueness of index names, AWS SCT gives you the option to generate unique index names if your index names are not unique. To do this, choose the option Generate unique index names in the project properties. By default, this option is enabled. If this option is enabled, unique index names are created using the format IX_table_name_index_name. If this option is disabled, index names aren’t changed.

A GOTO statement and a label can be used to change the order that statements are run in. Any Transact-SQL statements that follow a GOTO statement are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can also be nested.

PostgreSQL doesn’t use GOTO statements. When AWS SCT converts code that contains a GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement. You can find examples of how AWS SCT converts GOTO statements in the table following.

BEGIN
   ....
   statement1;
   ....
   GOTO label1;
   statement2;
   ....
   label1:
   Statement3;
   ....
END

BEGIN
 label1:
 BEGIN
   ....
   statement1;
   ....
   EXIT label1;
   statement2;
   ....
 END;
   Statement3;
   ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   statement2;
   ....
   GOTO label1;
   statement3;
   ....
   statement4;
   ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   LOOP
    statement2;
    ....
    CONTINUE label1;
    EXIT label1;
   END LOOP; 
    statement3;
    ....
    statement4;
    ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   statement2;
   ....
   statement3;
   ....
   statement4;
   ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   BEGIN
    statement2;
    ....    
    statement3;
    ....
    statement4;
    ....    
   END; 
END

PostgreSQL doesn't support a MERGE statement. AWS SCT emulates the behavior of a MERGE statement in the following ways:

AWS SCT can add database triggers to the object tree when Amazon RDS is the target.

AWS SCT can add server-level triggers to the object tree when Amazon RDS is the target.

SQL Server automatically creates and manages deleted and inserted tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. AWS SCT can convert the usage of these tables inside DML trigger statements.

AWS SCT can add linked servers to the object tree when Amazon RDS is the target.

When migrating from Microsoft SQL Server to PostgreSQL, the built-in SUSER_SNAME function is converted as follows:

Converting table-valued functions is supported. Table-valued functions return a table and can take the place of a table in a query.

PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression on all valid text and character data types. It returns zeros if the pattern is not found. When converting from SQL Server to Amazon RDS for PostgreSQL, AWS SCT replaces application code that uses PATINDEX with aws_sqlserver_ext.patindex(<pattern character>, <expression character varying>) .

In SQL Server, a user-defined table type is a type that represents the definition of a table structure. You use a user-defined table type to declare table-value parameters for stored procedures or functions. You can also use a user-defined table type to declare table variables that you want to use in a batch or in the body of a stored procedure or function. AWS SCT emulated this type in PostgreSQL by creating a temporary table.