# Migrating T-SQL features T-SQL This topic provides conceptual content comparing various features and functionalities between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can gain valuable insights into the differences and similarities between these two database systems, which is crucial for planning and executing a successful migration. The content covers a wide range of topics, including data types, cursors, stored procedures, error handling, full-text search, and more. By understanding these concepts, database administrators and developers can anticipate challenges, identify potential workarounds, and make informed decisions when transitioning their databases and applications from SQL Server to Aurora PostgreSQL. This knowledge enables smoother migrations and helps maintain data integrity and functionality in the new PostgreSQL environment. **Topics** + [ # Service Broker functionality for T-SQL ](chap-sql-server-aurora-pg.tsql.servicebroker.md) + [ # SQL Server cast and convert for T-SQL ](chap-sql-server-aurora-pg.tsql.castconvert.md) + [ # Common Language Runtime for T-SQL ](chap-sql-server-aurora-pg.tsql.clr.md) + [ # Collations for T-SQL ](chap-sql-server-aurora-pg.tsql.collations.md) + [ # Cursors for T-SQL ](chap-sql-server-aurora-pg.tsql.cursors.md) + [ # Date and time functions for T-SQL ](chap-sql-server-aurora-pg.tsql.datetime.md) + [ # String functions for T-SQL ](chap-sql-server-aurora-pg.tsql.stringfunctions.md) + [ # Databases and schemas for T-SQL ](chap-sql-server-aurora-pg.tsql.schemas.md) + [ # Dynamic SQL for T-SQL ](chap-sql-server-aurora-pg.tsql.dynamicsql.md) + [ # Transactions for T-SQL ](chap-sql-server-aurora-pg.tsql.transactions.md) + [ # Synonyms for T-SQL ](chap-sql-server-aurora-pg.tsql.synonyms.md) + [ # Delete and update from for T-SQL ](chap-sql-server-aurora-pg.tsql.delete.md) + [ # Stored procedures for T-SQL ](chap-sql-server-aurora-pg.tsql.storedprocedures.md) + [ # Error handling for T-SQL ](chap-sql-server-aurora-pg.tsql.errorhandling.md) + [ # Flow control for T-SQL ](chap-sql-server-aurora-pg.tsql.flowcontrol.md) + [ # Full-text search for T-SQL ](chap-sql-server-aurora-pg.tsql.fulltextsearch.md) + [ # SQL server graph features for T-SQL ](chap-sql-server-aurora-pg.tsql.graph.md) + [ # JSON and XML for T-SQL ](chap-sql-server-aurora-pg.tsql.json.md) + [ # Merge for T-SQL ](chap-sql-server-aurora-pg.tsql.merge.md) + [ # Pivot and unpivot for T-SQL ](chap-sql-server-aurora-pg.tsql.pivot.md) + [ # Triggers for T-SQL ](chap-sql-server-aurora-pg.tsql.triggers.md) + [ # Top fetch for T-SQL ](chap-sql-server-aurora-pg.tsql.topfetch.md) + [ # User-defined functions for T-SQL ](chap-sql-server-aurora-pg.tsql.udf.md) + [ # User-defined types for T-SQL ](chap-sql-server-aurora-pg.tsql.udt.md) + [ # Identity and sequences for T-SQL ](chap-sql-server-aurora-pg.tsql.sequences.md) # Service Broker functionality for T-SQL This topic provides reference information about migrating from Microsoft SQL Server 2019’s Service Broker functionality to Amazon Aurora PostgreSQL. You can understand the challenges and alternatives available when moving from SQL Server’s native messaging and queuing capabilities to Aurora PostgreSQL, which doesn’t offer a direct equivalent. The topic explores how you can achieve similar functionality using a combination of AWS services, including DB Links, AWS Lambda, and Amazon SQS. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[No compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-0.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Service Broker](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.servicebroker) | Use Amazon Lambda for similar functionality. | ## SQL Server Usage SQL Server Service Broker provides native support for messaging and queuing applications. Developers use Server Broker to create complex applications that use the database engine components to communicate between several SQL Server databases. Developers can use Service Broker to easily build distributed and more reliable applications. Benefits of using messaging queues: + Decouple dependencies between applications by communicating through messages. + Scale out your architecture by moving queues or message processors to separate servers as needed. + Maintain individual parts with a minimal impact to the end users. + Control when the messages are processed, for example, off-peak hours. + Process queued messages on multiple servers or processes or threads. The following sections describe the Service Broker commands. ### CREATE MESSAGE TYPE The following example creates a message with name and structure. ``` CREATE MESSAGE TYPE message_type_name [ AUTHORIZATION owner_name ] [ VALIDATION = { NONE | EMPTY | WELL_FORMED_XML | VALID_XML WITH SCHEMA COLLECTION schema_collection_name } ] [ ; ] ``` For more information, see [CREATE MESSAGE TYPE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-message-type-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. ### CREATE QUEUE The following example creates a queue to store messages. ``` CREATE QUEUE [ WITH [ STATUS = { ON | OFF } [ , ] ] [ RETENTION = { ON | OFF } [ , ] ] [ ACTIVATION ( [ STATUS = { ON | OFF } , ] PROCEDURE_NAME = , MAX_QUEUE_READERS = max_readers , EXECUTE AS { SELF | 'user_name' | OWNER } ) [ , ] ] [ POISON_MESSAGE_HANDLING ( [ STATUS = { ON | OFF } ] ) ] ] [ ON { filegroup | [ DEFAULT ] } ] [ ; ] ::= { [ database_name. [ schema_name ] . | schema_name. ] queue_name } ::= { [ database_name. [ schema_name ] . | schema_name. ] stored_procedure_name } ``` For more information, see [CREATE QUEUE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-queue-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. ### CREATE CONTRACT The following example specifies the role and what type of messages a service can handle. ``` CREATE CONTRACT contract_name [ AUTHORIZATION owner_name ] ( { { message_type_name | [ DEFAULT ] } SENT BY { INITIATOR | TARGET | ANY } } [ ,...n] ) [ ; ] ``` For more information, see [CREATE CONTRACT (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-contract-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. ### CREATE SERVICE The following example creates a named Service Broker for a specified task or set of tasks. ``` CREATE SERVICE service_name [ AUTHORIZATION owner_name ] ON QUEUE [ schema_name. ]queue_name [ ( contract_name | [DEFAULT][ ,...n ] ) ] [ ; ] ``` For more information, see [CREATE SERVICE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-service-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. ### BEGIN DIALOG CONVERSATION The following example starts the interaction between Service Brokers. ``` BEGIN DIALOG [ CONVERSATION ] @dialog_handle FROM SERVICE initiator_service_name TO SERVICE 'target_service_name' [ , { 'service_broker_guid' | 'CURRENT DATABASE' }] [ ON CONTRACT contract_name ] [ WITH [ { RELATED_CONVERSATION = related_conversation_handle | RELATED_CONVERSATION_GROUP = related_conversation_group_id } ] [ [ , ] LIFETIME = dialog_lifetime ] [ [ , ] ENCRYPTION = { ON | OFF } ] ] [ ; ] ``` For more information, see [BEGIN DIALOG CONVERSATION (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/begin-dialog-conversation-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. ### WAITFOR(RECEIVE TOP(1)) The following example specifies that a code block has to wait until one message is received. ``` [ WAITFOR ( ] RECEIVE [ TOP ( n ) ] [ ,...n ] FROM [ INTO table_variable ] [ WHERE { conversation_handle = conversation_handle | conversation_group_id = conversation_group_id } ] [ ) ] [ , TIMEOUT timeout ] [ ; ] ::= { * | { column_name | [ ] expression } [ [ AS ] column_alias ] | column_alias = expression } [ ,...n ] ::= { [ database_name . [ schema_name ] . | schema_name . ] queue_name } ``` For more information, see [RECEIVE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/receive-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. You can combine all of the preceding commands to achieve your architecture goals. For more information, see [Service Broker](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) doesn’t provide a compatible solution to the SQL Server Service Broker. However, you can use DB Links and AWS Lambda to achieve similar functionality. You can combine AWS Lambda with AWS SQS to reduce costs and remove some loads from the database into the AWS Lambda and Amazon Simple Queue Service (Amazon SQS). This will be much more efficient. For more information, see [Using Lambda with Amazon SQS](https://docs.aws.amazon.com/lambda/latest/dg/with-sqs.html). For example, you can create a table in each database and connect each database with a DB link to read the tables and process the data. For more information, see DB Links. You can also use AWS Lambda to query a table from the database, process the data, and insert it to another database (even another database type). This approach is the best option for moving workloads out of the database to a less expensive instance type. For even more decoupling and reducing workloads from the database, you can use Amazon SQS with Lambda. For more information, see [Database Mail](chap-sql-server-aurora-pg.management.databasemail.md). # SQL Server cast and convert for T-SQL This topic provides reference information about data type conversion and casting in Amazon Aurora PostgreSQL compared to Microsoft SQL Server. You can understand the similarities and differences between the CAST and CONVERT functions in both database systems. The topic explains how Aurora PostgreSQL supports the CAST function similarly to SQL Server, while also offering additional flexibility through custom casts and the CREATE CAST command. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png) | N/A | CONVERT is used only to convert between collations. CAST uses different syntax. | ## SQL Server Usage The `CAST` and `CONVERT` functions are commonly used to convert one data type to another. `CAST` and `CONVERT` behave mostly the same and share the same topic in MSDN. They have the following differences: + `CAST` is part of the ANSI-SQL specification, but `CONVERT` isn’t. + `CONVERT` accepts an optional style parameter used for formatting. For more information, see [Date and Time styles](https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles) in the *SQL Server documentation*. ### Conversion Matrix For a list of available conversion data types, see [Implicit conversions](https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#implicit-conversions) in the *SQL Server documentation*. ### Syntax ``` -- CAST Syntax: CAST ( expression AS data_type [ ( length ) ] ) -- CONVERT Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) ``` ### Examples The following example casts a `string` to `int` and `int` to `decimal`. ``` SELECT CAST('23' AS int) AS [int], CAST(23 AS decimal(10, 2)) AS [decimal]; ``` The following example converts `string` to `int` and `int` to `decimal`. ``` SELECT CONVERT(int, '23') AS [int], CONVERT(decimal(10, 2), 23) AS [decimal]; ``` For these two preceding examples, the result looks as shown following. ``` int decimal 23 23.00 ``` The following example converts a date with option style input `(109 - mon dd yyyy hh:mi:ss:mmmAM (or PM))`. ``` SELECT CONVERT(nvarchar(30), GETDATE(), 109); Jul 25 2018 5:20:10.8975085PM ``` For more information, see [CAST and CONVERT (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) provides the same CAST function as SQL Server for conversion between data types. It also provides a `CONVERSION` function, but it isn’t equivalent to SQL Server `CONVERT`. PostgreSQL `CONVERSION` is used to convert between character set encoding. `CREATE A CAST` defines a new cast on how to convert between two data types. Cast can be `EXPLICITLY` or `IMPLICIT`. The behavior is similar to SQL Server’s casting, but in PostgreSQL, you can also create your own casts to change the default behavior. For example, checking if a string is a valid credit card number by creating the `CAST` with the `WITHOUT FUNCTION` clause. `CREATE CONVERSION` is used to convert between encoding such as UTF8 and LATIN. If `CONVERT` is currently in use in SQL Server code, rewrite it to use `CAST` instead. **Note** Not all SQL Server data types are supported on Aurora PostgreSQL, besides changing the `CAST` or `CONVERT` commands, you might need to also change the source of the target data type. For more information, see [Data Types](chap-sql-server-aurora-pg.sql.datatypes.md). Another way to convert between data types in PostgreSQL will be to use the `::` characters. This option is useful and can make your PL/pgSQL code look cleaner and simpler, see the following examples. ### Syntax ``` CREATE CAST (source_type AS target_type) WITH FUNCTION function_name (argument_type [, ...]) [ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (source_type AS target_type) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (source_type AS target_type) WITH INOUT [ AS ASSIGNMENT | AS IMPLICIT ] ``` ### Examples The following example converts a numeric value to float. ``` SELECT 23 + 2.0; or SELECT CAST ( 23 AS numeric ) + 2.0; ``` The following example converts a date with format input ('mon dd yyyy hh:mi:ss:mmmAM (or PM)'). ``` SELECT TO_CHAR(NOW(),'Mon DD YYYY HH:MI:SS:MSAM'); Jul 25 2018 5:20:10.8975085PM ``` The following example uses the `::` characters. ``` SELECT '2.35'::DECIMAL + 4.5 AS results; results 6.85 ``` ## Summary | Option | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | Explicit `CAST` | `SELECT CAST('23.7' AS varchar) AS int` | `SELECT CAST('23.7' AS varchar) AS int` | | Explicit `CONVERT` | `SELECT CONVERT (VARCHAR, '23.7')` | Need to use `CAST`: | | `SELECT CAST('23.7' AS varchar) AS int` | Implicit casting | `SELECT 23 + 2.0 SELECT 23 + 2.0` | | Convert to a specific date format: `'mon dd yyyy hh:mi:ss:mmmAM'` | `SELECT CONVERT(nvarchar (30), GETDATE(), 109)` | `SELECT TO_CHAR(NOW(),'Mon DD YYYY HH:MI:SS:MSAM')` | For more information, see [CREATE CAST](https://www.postgresql.org/docs/13/sql-createcast.html), [Type Conversion](https://www.postgresql.org/docs/13/typeconv.html), and [CREATE CONVERSION](https://www.postgresql.org/docs/13/sql-createconversion.html) in the *PostgreSQL documentation*. # Common Language Runtime for T-SQL This topic provides reference information about migrating Microsoft SQL Server’s Common Language Runtime (CLR) objects to Amazon Aurora PostgreSQL. You can understand the differences in functionality between SQL Server’s CLR capabilities and alternatives. The topic explains that while Aurora PostgreSQL doesn’t support .NET code directly, it offers Perl as an alternative for creating similar database objects. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | N/A | Migrating CLR objects requires a full code rewrite. | ## SQL Server Usage SQL Server provides the capability of implementing .NET objects in the database using the Common Runtime Library (CLR). The CLR enables development of functionality that would be complicated using T-SQL. The CLR provides robust solutions for string manipulation, date manipulation, and calling external services such as Windows Communication Foundation (WCF) services and web services. You can create the following objects with the `EXTERNAL NAME` clause: + Procedures. For more information, see [CLR Stored Procedures](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/clr-stored-procedures?redirectedfrom=MSDN) in the *SQL Server documentation*. + Functions. For more information, see [Create CLR Functions](https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-clr-functions?view=sql-server-2017) in the *SQL Server documentation*. + Triggers. For more information, see [Create CLR Triggers](https://docs.microsoft.com/en-us/sql/relational-databases/triggers/create-clr-triggers?view=sql-server-2017) in the *SQL Server documentation*. + Types. For more information, see [CLR User-Defined Types](https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-types/clr-user-defined-types?view=sql-server-2017) in the *SQL Server documentation*. + User-defined aggregate functions. For more information, see [CLR User-Defined Aggregates](https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregates?view=sql-server-2017) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) doesn’t support .NET code. However, you can create Perl functions. In this case, convert all C\$1 code to PL/pgSQL or PL/Perl. To use PL/Perl language, install the Perl extension: ``` CREATE EXTENSION plperl; ``` After you install the Perl extension, you can create functions using Perl code. Specify `plperl` in the `LANGUAGE` clause. You can create the following objects with Perl: + Functions. + Void functions or procedures. + Triggers. + Event Triggers. + Values for session level. ### Examples The following example creates a function that returns the greater value of two integers. ``` CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ if ($_[0] > $_[1]) { return $_[0]; } return $_[1]; $$ LANGUAGE plperl; ``` For more information, see [PL/Perl — Perl Procedural Language](https://www.postgresql.org/docs/13/plperl.html) in the *PostgreSQL documentation*. # Collations for T-SQL This topic provides reference information about collations and character sets in Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL, highlighting their differences and similarities. You can gain insight into how these database systems handle string management, sorting rules, and character encoding. The topic explores the various levels at which collations can be defined in SQL Server, from server-level to expression-level, and contrasts this with PostgreSQL’s approach. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Collations](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.collations) | `UTF16`, `NCHAR`, and `NVARCHAR` data types aren’t supported. | ## SQL Server Usage SQL Server collations define the rules for string management and storage in terms of sorting, case sensitivity, accent sensitivity, and code page mapping. SQL Server supports both ASCII and UCS-2 UNICODE data. UCS-2 UNICODE data uses a dedicated set of UNICODE data types denoted by the prefix N: Nchar and Nvarchar. Their ASCII counterparts are `CHAR` and `VARCHAR`. Choosing a collation and a character set has significant implications on data storage, logical predicate evaluations, query results, and query performance. To view all collations supported by SQL Server, use the `fn_helpcollations` function: ``` SELECT * FROM sys.fn_helpcollations() ``` Collations define the actual bitwise binary representation of all string characters and the associated sorting rules. SQL Server supports multiple collations down to the column level. A table may have multiple string columns that use different collations. Collations for non-UNICODE character sets determine the code page number representing the string characters. UNICODE and non-UNICODE data types in SQL Server aren’t compatible. A predicate or data modification that introduces a type conflict is resolved using predefined collation precedence rules. For more information, see [Collation Precedence](https://docs.microsoft.com/en-us/sql/t-sql/statements/collation-precedence-transact-sql?view=sql-server-ver15). Collations define sorting and matching sensitivity for the following string characteristics: + Case + Accent + Kana + Width + Variation selector SQL Server uses a suffix naming convention that appends the option name to the collation name. For example, the collation Azeri\$1Cyrillic\$1100\$1CS\$1AS\$1KS\$1WS\$1SC, is an Azeri-Cyrillic-100 collation that is case-sensitive, accent-sensitive, kana type-sensitive, width-sensitive, and has supplementary characters. SQL Server supports three types of collation sets: + **Windows collations** use the rules defined for collations by the operating system locale where UNICODE and non-UNICODE data use the same comparison algorithms. + **Binary collations** use the binary bit-wise code for comparison. Therefore, the locale doesn’t affect sorting. + **SQL Server collations** provide backward compatibility with previous SQL Server versions. They aren’t compatible with the windows collation rules for non-UNICODE data. You can define collations at various levels: + **Server-level collations** determine the collations used for all system databases and is the default for future user databases. While the system databases collation can’t be changed, you can specify an alternative collation as part of the `CREATE DATABASE` statement. + **Database-level collations** inherit the server default unless the `CREATE DATABASE` statement explicitly sets a different collation. This collation is used as a default for all `CREATE TABLE` and `ALTER TABLE` statements. + **Column-level collations** can be specified as part of the `CREATE TABLE` or `ALTER TABLE` statements to override the default collation setting of your database. + **Expression-level collations** can be set for individual string expressions using the COLLATE function. For example, `SELECT * FROM MyTable ORDER BY StringColumn COLLATE Latin1_General_CS_AS`. SQL Server supports UCS-2 UNICODE only. SQL Server 2019 adds support for UTF-8 for import and export encoding, and as database-level or column-level collation for string data. Support includes PolyBase external tables, and Always Encrypted when not used with Enclaves. For more information, see [Collation and Unicode Support](https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15). ### Syntax ``` CREATE DATABASE [ ON ] COLLATE [ WITH ]; ``` ``` CREATE TABLE ( COLLATE [ ]... ); ``` ### Examples The following example creates a database with a default Bengali\$1100\$1CS\$1AI collation. ``` CREATE DATABASE MyBengaliDatabase ON ( NAME = MyBengaliDatabase_Datafile, FILENAME = 'C:\Program Files\Microsoft SQL Server-\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyBengaliDatabase.mdf', SIZE = 100) LOG ON ( NAME = MyBengaliDatabase_Logfile, FILENAME = 'C:\Program Files\Microsoft SQL Server-\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyBengaliDblog.ldf', SIZE = 25) COLLATE Bengali_100_CS_AI; ``` The following example creates a table with two different collations. ``` CREATE TABLE MyTable ( Col1 CHAR(10) COLLATE Hungarian_100_CI_AI_SC NOT NULL PRIMARY KEY, COL2 VARCHAR(100) COLLATE Sami_Sweden_Finland_100_CS_AS_KS NOT NULL ); ``` For more information, see [Collation and Unicode support](https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage PostgreSQL supports a variety of different character sets, also known as encoding, including support for both single-byte and multi-byte languages. The default character set is specified when initializing a PostgreSQL database cluster with `initdb`. Each individual database created on the PostgreSQL cluster supports individual character sets defined as part of database creation. **Note** For Amazon Relational Database Service (Amazon RDS), starting with PostgreSQL 13, the Windows version now supports obtaining version information for collations or ordering rules from the operating system. When you query the collation in PostgreSQL running on Windows, prior to version 13 there wasn’t any value to reflect the OS collation version. For example, for PostgreSQL version 11 running on Windows, the result is shown following: ``` CREATE COLLATION german (provider = libc, locale = 'de_DE'); CREATE COLLATION select oid,collname,collversion from pg_collation where collprovider='c' and collname='german'; oid collname collversion 16394 german (1 row) select pg_collation_actual_version (16394); pg_collation_actual_version (1 row) ``` For PostgreSQL version 13 running on Windows, the result is shown following: ``` CREATE COLLATION german (provider = libc, locale = 'de_DE'); CREATE COLLATION select oid,collname,collversion from pg_collation where collprovider='c' and collname='german'; oid collname collversion 32769 german 1539.5,1539.5 (1 row) select pg_collation_actual_version (32769); pg_collation_actual_version 1539.5,1539.5 (1 row) ``` Clients can use all supported character sets. However, some client-side only characters aren’t supported for use within the server. Unlike SQL Server, PostgreSQL doesn’t natively support an NVARHCHAR data type and doesn’t provide support for UTF-16. | Type | Function | Implementation level | | --- | --- | --- | | Encoding | Defines the basic rules on how alphanumeric characters are represented in binary format. For example, Unicode encoding. | Database | | Locale | A superset that includes `LC_COLLATE` and `LC_CTYPE` among others. For example, `LC_COLLATE` defines how strings are sorted and must be a subset supported by the database encoding. | Table-Column | ### Examples The following example creates a database named test01 which uses the Korean EUC\$1KR Encoding the and the `ko_KR` locale. ``` CREATE DATABASE test01 WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0; ``` The following example shows how to view the character sets configured for each database by querying the system catalog. ``` select datname, datcollate, datctype from pg_database; ``` ### Changing Character Sets or Encoding In-place modification of the database encoding isn’t recommended nor supported. Instead, export all data, create a new database with the new encoding, and import the data. Export the data using the `pg_dump` utility. ``` pg_dump mydb1 > mydb1_export.sql ``` Rename or delete a database. ``` ALTER DATABASE mydb1 TO mydb1_backup; ``` Create a new database using the modified encoding. ``` CREATE DATABASE mydb1_new_encoding WITH ENCODING 'UNICODE' TEMPLATE=template0; ``` Import data using the `pg_dump` file previously created. Verify that you set your client encoding to the encoding of your old database. ``` PGCLIENTENCODING=OLD_DB_ENCODING psql -f mydb1_export.sql mydb1_new_encoding ``` The `client_encoding` parameter overrides the use of `PGCLIENTENCODING`. ### Client-Server Character Set Conversions PostgreSQL supports conversion of character sets between servers and clients for specific character set combinations as described in the `pg_conversion` system catalog. PostgreSQL includes predefined conversions. For more information, see [Available Character Set Conversions](https://www.postgresql.org/docs/13/static/multibyte.html#MULTIBYTE-TRANSLATION-TABLE) in the *PostgreSQL documentation*. You can create a new conversion using the SQL command `CREATE CONVERSION`. ### Examples The following example creates a conversion from UTF8 to LATIN1 using the custom `myfunc1` function. ``` CREATE CONVERSION myconv FOR 'UTF8' TO 'LATIN1' FROM myfunc1; ``` The following example configures the PostgreSQL client character set. ``` Method 1 ======== psql \encoding SJIS Method 2 ======== SET CLIENT_ENCODING TO 'value'; ``` View the client character set and reset it back to the default value. ``` SHOW client_encoding; RESET client_encoding; ``` ### Table Level Collation PostgreSQL supports specifying the sort order and character classification behavior on a per-column level. ### Example Specify specific collations for individual table columns. ``` CREATE TABLE test1 (col1 text COLLATE "de_DE", col2 text COLLATE "es_ES"); ``` ## Summary | Feature | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | View database character set | `SELECT collation_name FROM sys.databases;` | `select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database;` | | Modify the database character set | `RECRATE` the database | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.collations.html) | | Character set granularity | Database | Database | | UTF8 | Supported | Supported | | UTF16 | Supported | Not Supported | | `NCHAR` or `NVARCHAR` data types | Supported | Not Supported | For more information, see [Character Set Support](https://www.postgresql.org/docs/13/multibyte.html) in the *PostgreSQL documentation*. # Cursors for T-SQL This topic provides reference information about cursor compatibility between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. It introduces the concept of cursors and their role in database operations, explaining how they allow developers to work with result sets sequentially. The topic compares cursor functionality in SQL Server and PostgreSQL, highlighting similarities and differences in syntax and usage. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | [Cursors](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.cursors) | Different cursor options. | ## SQL Server Usage A *set* is a fundamental concept of the relation data model from which SQL is derived. SQL is a declarative language that operates on whole sets, unlike most procedural languages that operate on individual data elements. A single invocations of an SQL statements can return a whole set or modify millions of rows. Many developers are accustomed to using procedural or imperative approaches to develop solutions that are difficult to implement using set-based querying techniques. Also, operating on row data sequentially may be a more appropriate approach in certain situations. Cursors provide an alternative mechanism for operating on result sets. Instead of receiving a table object containing rows of data, applications can use cursors to access the data sequentially, row-by-row. Cursors provide the following capabilities: + Positioning the cursor at specific rows of the result set using absolute or relative offsets. + Retrieving a row, or a block of rows, from the current cursor position. + Modifying data at the current cursor position. + Isolating data modifications by concurrent transactions that affect the cursor’s result. + T-SQL statements can use cursors in scripts, stored procedures, and triggers. ### Syntax ``` DECLARE CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR
SET = ,... FROM
WHERE ; ``` ``` DELETE FROM
FROM
WHERE ; ``` ### Examples The following example deletes customers with no orders. ``` CREATE TABLE Customers ( Customer VARCHAR(20) PRIMARY KEY ); ``` ``` INSERT INTO Customers VALUES ('John'), ('Jim'), ('Jack') ``` ``` CREATE TABLE Orders ( OrderID INT NOT NULL PRIMARY KEY, Customer VARCHAR(20) NOT NULL, OrderDate DATE NOT NULL ); ``` ``` INSERT INTO Orders (OrderID, Customer, OrderDate) VALUES (1, 'Jim', '20180401'), (2, 'Jack', '20180402'); ``` ``` DELETE FROM Customers FROM Customers AS C LEFT OUTER JOIN Orders AS O ON O.Customer = C.Customer WHERE O.OrderID IS NULL; ``` ``` SELECT * FROM Customers; ``` ``` Customer Jim Jack ``` The following example updates multiple columns in Orders based on the values in OrderCorrections. ``` CREATE TABLE OrderCorrections ( OrderID INT NOT NULL PRIMARY KEY, Customer VARCHAR(20) NOT NULL, OrderDate DATE NOT NULL ); ``` ``` INSERT INTO OrderCorrections VALUES (1, 'Jack', '20180324'); ``` ``` UPDATE O SET Customer = OC.Customer, OrderDate = OC.OrderDate FROM Orders AS O INNER JOIN OrderCorrections AS OC ON O.OrderID = OC.OrderID; ``` ``` SELECT * FROM Orders; ``` ``` Customer OrderDate Jack 2018-03-24 Jack 2018-04-02 ``` For more information, see [UPDATE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15), [DELETE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver15), and [FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Aurora PostgreSQL doesn’t support the `DELETE..FROM` syntax, but it support the `UPDATE FROM` syntax. ### Syntax ``` [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] ``` ### Migration Considerations You can rewrite the `DELETE` statements as subqueries. Place the subqueries in the WHERE clause. This workaround is simple and, in most cases, easier to read and understand. ### Examples The following example deletes customers with no orders. ``` CREATE TABLE Customers ( Customer VARCHAR(20) PRIMARY KEY ); INSERT INTO Customers VALUES ('John'), ('Jim'), ('Jack') CREATE TABLE Orders ( OrderID INT NOT NULL PRIMARY KEY, Customer VARCHAR(20) NOT NULL, OrderDate DATE NOT NULL ); INSERT INTO Orders (OrderID, Customer, OrderDate) VALUES (1, 'Jim', '20180401'), (2, 'Jack', '20180402'); DELETE FROM Customers WHERE Customer NOT IN ( SELECT Customer FROM Orders ); SELECT * FROM Customers; Customer Jim Jack ``` The following example updates multiple columns in Orders based on the values in OrderCorrections ``` CREATE TABLE OrderCorrections ( OrderID INT NOT NULL PRIMARY KEY, Customer VARCHAR(20) NOT NULL, OrderDate DATE NOT NULL ); INSERT INTO OrderCorrections VALUES (1, 'Jack', '20180324'); UPDATE orders SET Customer = OC.Customer, OrderDate = OC.OrderDate FROM Orders AS O INNER JOIN OrderCorrections AS OC ON O.OrderID = OC.OrderID; SELECT * FROM Orders; Customer OrderDate Jack 2018-03-24 Jack 2018-04-02 ``` ## Summary The following table identifies similarities, differences, and key migration considerations. | Feature | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | Join as part of `DELETE` | `DELETE FROM …​ FROM` | Not available. Rewrite to use WHERE clause with a sub-query. | | Join as part of `UPDATE` | `UPDATE …​ FROM` | `UPDATE …​ FROM` | For more information, see [DELETE](https://www.postgresql.org/docs/13/sql-delete.html) and [UPDATE](https://www.postgresql.org/docs/13/sql-update.html) in the *PostgreSQL documentation*. # Stored procedures for T-SQL This topic provides reference information about the compatibility and differences between stored procedures in Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can use this guide to understand the key distinctions in syntax, security contexts, parameter handling, and supported features when migrating stored procedures from SQL Server to Aurora PostgreSQL. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png) | [Stored Procedures](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.storedprocedures) | Syntax and option differences. | ## SQL Server Usage Stored procedures are encapsulated, persisted code modules that you can run using the `EXECUTE` T-SQL statement. They may have multiple input (`IN`) and output (`OUT`) parameters. Table-valued user-defined types can be used as input parameters. `IN` is the default direction for parameters, but `OUT` must be explicitly specified. You can specify parameters as both `IN` and `OUT`. SQL Server allows you to run stored procedures in any security context using the `EXECUTE AS` option. You can explicitly recompile them for every run using the `RECOMPILE` option. You can encrypt them in the database using the `ENCRYPTION` option to prevent unauthorized access to the source code. SQL Server provides a unique feature that allows you to use a stored procedure as an input to an INSERT statement. When using this feature, only the first row in the data set returned by the stored procedure is evaluated. ### Syntax ``` CREATE [ OR ALTER ] { PROC | PROCEDURE } [ [ WITH [ ENCRYPTION ]|[ RECOMPILE ]|[ EXECUTE AS ...]] AS { [ BEGIN ] [ END ] }[;] ``` ### Examples **Create and run a stored procedure** The following example creates a simple parameterized stored procedure to validate the basic format of an email. ``` CREATE PROCEDURE ValidateEmail @Email VARCHAR(128), @IsValid BIT = 0 OUT AS BEGIN IF @Email LIKE N'%@%' SET @IsValid = 1 ELSE SET @IsValid = 0 RETURN @IsValid END; ``` The following example runs this stored procedure. ``` DECLARE @IsValid BIT EXECUTE [ValidateEmail] @Email = 'X@y.com', @IsValid = @IsValid OUT; SELECT @IsValid; -- Returns 1 ``` ``` EXECUTE [ValidateEmail] @Email = 'Xy.com', @IsValid = @IsValid OUT; SELECT @IsValid; -- Returns 0 ``` The following example creates a stored procedure that uses `RETURN` to pass an error value to the application. ``` CREATE PROCEDURE ProcessImportBatch @BatchID INT AS BEGIN BEGIN TRY EXECUTE Step1 @BatchID EXECUTE Step2 @BatchID EXECUTE Step3 @BatchID END TRY BEGIN CATCH IF ERROR_NUMBER() = 235 RETURN -1 -- indicate special condition ELSE THROW -- handle error normally END CATCH END ``` **Using a table-valued input parameter** The following example creates and populates an OrderItems table. ``` CREATE TABLE OrderItems( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) ); ``` ``` INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200), (3, 'M6 Washer', 100); ``` The following example creates a table-valued type for the `OrderItem` table-valued parameter. ``` CREATE TYPE OrderItems AS TABLE ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) ); ``` The following example creates a procedure to process order items. ``` CREATE PROCEDURE InsertOrderItems @OrderItems AS OrderItems READONLY AS BEGIN INSERT INTO OrderItems(OrderID, Item, Quantity) SELECT OrderID, Item, Quantity FROM @OrderItems END; ``` The following example populates the table-valued variable and passes the data set to the stored procedure. ``` DECLARE @OrderItems AS OrderItems; INSERT INTO @OrderItems ([OrderID], [Item], [Quantity]) VALUES (1, 'M8 Bolt', 100), (1, 'M8 Nut', 100), (1, M8 Washer, 200); EXECUTE [InsertOrderItems] @OrderItems = @OrderItems; (3 rows affected) Item Quantity 1 M8 Bolt 100 2 M8 Nut 100 3 M8 Washer 200 ``` ### INSERT…​ EXEC Syntax ``` INSERT INTO EXECUTE ; ``` For more information, see [CREATE PROCEDURE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage PostgreSQL version 10 provides support for both stored procedures and stored functions using the `CREATE FUNCTION` statement. To emphasize, only the `CREATE FUNCTION` is supported by the procedural statements used by PostgreSQL version 10. The `CREATE PROCEDURE` statement isn’t supported. PL/pgSQL is the main database programming language used for migrating from SQL Server T-SQL code. PostgreSQL supports these additional programming languages, also available in Amazon Aurora PostgreSQL: + PL/pgSQL + PL/Tcl + PL/Perl Use the `show.rds.extensions` command to view all available Amazon Aurora extensions. ### PostgreSQL Create Function Privileges To create a function, make sure that a user has the `USAGE` privilege on the language. When you create a function, you can specify a language parameter as shown in the following examples. ### Examples The following example creates a new FUNC\$1ALG function. ``` CREATE OR REPLACE FUNCTION FUNC_ALG(P_NUM NUMERIC) RETURNS NUMERIC AS $$ BEGIN RETURN P_NUM * 2; END; $$ LANGUAGE PLPGSQL; ``` The `CREATE OR REPLACE` statement creates a new function or replaces an existing function with these limitations: + You can’t change the function name or argument types. + The statement doesn’t allow changing the existing function return type. + The user must own the function to replace it. + The `P_NUM` INPUT parameter is implemented similar to SQL Server T-SQL INPUT parameter. + The double dollar signs alleviate the need to use single-quoted string escape elements. With the double dollar sign, there is no need to use escape characters in the code when using single quotation marks. The double dollar sign appears after the keyword `AS` and after the function keyword `END`. + Use the `LANGUAGE PLPGSQL` parameter to specify the language for the created function. The following example creates a function with PostgreSQL PL/pgSQL. ``` CREATE OR REPLACE FUNCTION EMP_SAL_RAISE (IN P_EMP_ID DOUBLE PRECISION, IN SAL_RAISE DOUBLE PRECISION) RETURNS VOID AS $$ DECLARE V_EMP_CURRENT_SAL DOUBLE PRECISION; BEGIN SELECT SALARY INTO STRICT V_EMP_CURRENT_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID = P_EMP_ID; UPDATE EMPLOYEES SET SALARY = V_EMP_CURRENT_SAL + SAL_RAISE WHERE EMPLOYEE_ID = P_EMP_ID; RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'NEW SALARY FOR EMPLOYEE ID: ', P_EMP_ID, ' IS ', (V_EMP_CURRENT_SAL + SAL_RAISE)); EXCEPTION WHEN OTHERS THEN RAISE USING ERRCODE := '20001', MESSAGE := CONCAT_WS('', 'AN ERROR WAS ENCOUNTERED -', SQLSTATE, ' -ERROR-', SQLERRM); END; $$ LANGUAGE PLPGSQL; select emp_sal_raise(200, 1000); ``` In the preceding example, you can replace the `RAISE` command with `RETURN` to inform the application that an error occurred. The following example creates a function with PostgreSQL PL/pgSQL. ``` CREATE OR REPLACE FUNCTION EMP_PERIOD_OF_SERVICE_YEAR (IN P_EMP_ID DOUBLE PRECISION) RETURNS DOUBLE PRECISION AS $$ DECLARE V_PERIOD_OF_SERVICE_YEARS DOUBLE PRECISION; BEGIN SELECT EXTRACT (YEAR FROM NOW()) - EXTRACT (YEAR FROM (HIRE_DATE)) INTO STRICT V_PERIOD_OF_SERVICE_YEARS FROM EMPLOYEES WHERE EMPLOYEE_ID = P_EMP_ID; RETURN V_PERIOD_OF_SERVICE_YEARS; END; $$ LANGUAGE PLPGSQL; SELECT EMPLOYEE_ID,FIRST_NAME, EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS PERIOD_OF_SERVICE_YEAR FROM EMPLOYEES; ``` There is a new behavior in PostgreSQL version 10 for a set-returning function, used by `LATERAL FROM` clause. **PostgreSQL version 9.6 and lower** ``` CREATE TABLE emps (id int, manager int); INSERT INTO tab VALUES (23, 24), (52, 23), (21, 65); SELECT x, generate_series(1,5) AS g FROM tab; id g 23 1 23 2 23 3 23 4 23 5 52 1 52 2 52 3 52 4 52 5 21 1 21 2 21 3 21 4 21 5 ``` **PostgreSQL version 10 and higher** ``` SELECT id, g FROM emps, LATERAL generate_series(1,5) AS g; id g 23 1 23 2 23 3 23 4 23 5 52 1 52 2 52 3 52 4 52 5 21 1 21 2 21 3 21 4 21 5 ``` In the preceding example, you can put the set-return function on the outside of the nested loop join because it has no actual lateral dependency on `emps` table. ## Summary The following table summarizes the differences between stored procedures in SQL Server and PostgreSQL. | Feature | SQL Server | Aurora PostgreSQL | Workaround | | --- | --- | --- | --- | | General CREATE syntax differences | 
CREATE PROC|PROCEDURE

@Parameter1 , ...n
AS
| 
CREATE [ OR REPLACE] FUNCTION
 (Parameter1 , ...n)
AS $$
| Rewrite stored procedure creation scripts to use `FUNCTION` instead of `PROC` or `PROCEDURE`. Rewrite stored procedure creation scripts to omit the `AS $$` pattern. Rewrite stored procedure parameters to not use the `@` symbol in parameter names. Add parentheses around the parameter declaration. | | Security context | 
{ EXEC | EXECUTE } AS
{ CALLER | SELF | OWNER
| 'user_name' }
| 
SECURITY INVOKER | SECURITY DEFINER
| For stored procedures that use an explicit user name, rewrite the code from `EXECUTE AS user` to `SECURITY DEFINER` and recreate the functions with this user. For stored procedures that use the `CALLER` option, rewrite the code to include `SECURITY INVOKER`. For stored procedures that use the `SELF` option, rewrite the code to `SECURITY DEFINER`. | | Encryption | Use the `WITH ENCRYPTION` option. | Not supported in Aurora PostgreSQL. | | | Parameter direction | `IN` and `OUT\|OUTPUT`, by default `OUT` can be used as `IN` as well. | `IN`, `OUT`, `INOUT`, or `VARIADIC` | Although the functionality of these parameters is the same for SQL Server and PostgreSQL, rewrite the code for syntax compliance. Use `OUT` instead of `OUTPUT`. Use `INOUT` instead of `OUT` for bidirectional parameters. | | Recompile | Use the `WITH RECOMPILE` option. | Not supported in Aurora PostgreSQL. | | | Table-valued parameters | Use declared table type user-defined parameters. | Use declared table type user-defined parameters. | | | Additional restrictions | Use `BULK INSERT` to load data from text file. | Not supported in Aurora PostgreSQL. | | For more information, see [CREATE FUNCTION](https://www.postgresql.org/docs/13/sql-createfunction.html), [PL/pgSQL — SQL Procedural Language](https://www.postgresql.org/docs/13/plpgsql.html), [Procedural Languages](https://www.postgresql.org/docs/13/xplang.html), and [Query Language (SQL) Functions](https://www.postgresql.org/docs/13/xfunc-sql.html) in the *PostgreSQL documentation*. # Error handling for T-SQL This topic provides reference information about error handling in SQL Server and Amazon Aurora PostgreSQL, focusing on the differences and similarities between the two systems. You can use this knowledge to understand how error handling mechanisms in SQL Server translate to Aurora PostgreSQL when migrating your database. The topic compares specific error handling features, such as TRY…​CATCH blocks and THROW statements, with their PostgreSQL equivalents. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | N/A | Different paradigm and syntax will require rewrite of error handling code. | ## SQL Server Usage SQL Server error handling capabilities have significantly improved throughout the years. However, previous features are retained for backward compatibility. Before SQL Server 2008, only very basic error handling features were available. `RAISERROR` was the primary statement used for error handling. Starting from SQL Server 2008, the extensive .NET-like error handling capabilities were added. They included `TRY…​CATCH` blocks, `THROW` statements, the `FORMATMESSAGE` function, and a set of system functions that return metadata for the current error condition. ### TRY…​CATCH Blocks `TRY…​CATCH` blocks implement error handling similar to Microsoft Visual C\$1 and Microsoft Visual C\$1\$1. `TRY …​ END TRY` statement blocks can contain T-SQL statements. If an error is raised by any of the statements within the `TRY …​ END TRY` block, the run stops and is moved to the nearest set of statements that are bounded by a `CATCH …​ END CATCH` block. ``` BEGIN TRY END TRY BEGIN CATCH END CATCH ``` ### THROW The `THROW` statement raises an exception and transfers run of the `TRY …​ END TRY` block of statements to the associated `CATCH …​ END CATCH` block of statements. Throw accepts either constant literals or variables for all parameters. ``` THROW [Error Number>, , < Error State>] [;] ``` ### Examples The following example uses `TRY…​CATCH` error blocks to handle key violations. ``` CREATE TABLE ErrorTest (Col1 INT NOT NULL PRIMARY KEY); ``` ``` BEGIN TRY BEGIN TRANSACTION INSERT INTO ErrorTest(Col1) VALUES(1); INSERT INTO ErrorTest(Col1) VALUES(2); INSERT INTO ErrorTest(Col1) VALUES(1); COMMIT TRANSACTION; END TRY BEGIN CATCH THROW; -- Throw with no parameters = RETHROW END CATCH; ``` ``` (1 row affected) (1 row affected) (0 rows affected) Msg 2627, Level 14, State 1, Line 7 Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE54D8676973'. Can't insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1). ``` **Note** Contrary to what many SQL developers believe, the values 1 and 2 are indeed inserted into `ErrorTestTable` in the preceding example. This behavior is in accordance with ANSI specifications stating that a constraint violation should not roll back an entire transaction. The following example uses `THROW` with variables. ``` BEGIN TRY BEGIN TRANSACTION INSERT INTO ErrorTest(Col1) VALUES(1); INSERT INTO ErrorTest(Col1) VALUES(2); INSERT INTO ErrorTest(Col1) VALUES(1); COMMIT TRANSACTION; END TRY BEGIN CATCH DECLARE @CustomMessage VARCHAR(1000), @CustomError INT, @CustomState INT; SET @CustomMessage = 'My Custom Text ' + ERROR_MESSAGE(); SET @CustomError = 54321; SET @CustomState = 1; THROW @CustomError, @CustomMessage, @CustomState; END CATCH; ``` ``` (0 rows affected) Msg 54321, Level 16, State 1, Line 19 My Custom Text Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE545CBDBB9A'. Can't insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1). ``` ### RAISERROR The `RAISERROR` statement is used to explicitly raise an error message, similar to `THROW`. It causes an error state for the run session and forwards run to either the calling scope or, if the error occurred within a `TRY …​ END TRY` block, to the associated `CATCH …​ END CATCH` block. `RAISERROR` can reference a user-defined message stored in the `sys.messages` system table or can be used with dynamic message text. The key differences between `THROW` and `RAISERROR` are: + Message IDs passed to `RAISERROR` must exist in the sys.messages system table. The error number parameter passed to THROW doesn’t. + `RAISERROR` message text may contain `printf` formatting styles. The message text of `THROW` may not. + `RAISERROR` uses the severity parameter for the error returned. For `THROW`, severity is always 16. ``` RAISERROR (|, , [WITH option [
] USING ON [WHEN MATCHED [AND ] THEN UPDATE SET | DELETE] [WHEN NOT MATCHED [BY TARGET] [AND ] THEN INSERT [()] VALUES () | DEFAULT VALUES] [WHEN NOT MATCHED BY SOURCE [AND ] THEN UPDATE SET | DELETE] OUTPUT [] ``` ### Examples The following example performs a simple one-way synchronization of two tables. ``` CREATE TABLE SourceTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 VARCHAR(20) NOT NULL ); ``` ``` CREATE TABLE TargetTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 VARCHAR(20) NOT NULL ); ``` ``` INSERT INTO SourceTable (Col1, Col2) VALUES (2, 'Source2'), (3, 'Source3'), (4, 'Source4'); ``` ``` INSERT INTO TargetTable (Col1, Col2) VALUES (1, 'Target1'), (2, 'Target2'), (3, 'Target3'); ``` ``` MERGE INTO TargetTable AS TGT USING SourceTable AS SRC ON TGT.Col1 = SRC.Col1 WHEN MATCHED THEN UPDATE SET TGT.Col2 = SRC.Col2 WHEN NOT MATCHED THEN INSERT (Col1, Col2) VALUES (SRC.Col1, SRC.Col2); ``` ``` SELECT * FROM TargetTable; ``` For the preceding examples, the result looks as shown following. ``` Col1 Col2 1 Target1 2 Source2 3 Source3 4 Source4 ``` Perform a conditional two-way synchronization using `NULL` for no change and `DELETE` from the target when the data isn’t found in the source. ``` TRUNCATE TABLE SourceTable; INSERT INTO SourceTable (Col1, Col2) VALUES (3, NULL), (4, 'NewSource4'), (5,'Source5'); ``` ``` MERGE INTO TargetTable AS TGT USING SourceTable AS SRC ON TGT.Col1 = SRC.Col1 WHEN MATCHED AND SRC.Col2 IS NOT NULL THEN UPDATE SET TGT.Col2 = SRC.Col2 WHEN NOT MATCHED THEN INSERT (Col1, Col2) VALUES (SRC.Col1, SRC.Col2) WHEN NOT MATCHED BY SOURCE THEN DELETE; ``` ``` SELECT * FROM TargetTable; ``` For the preceding examples, the result looks as shown following. ``` Col1 Col2 3 Source3 4 NewSource4 5 Source5 ``` For more information, see [MERGE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Currently, PostgreSQL version 10 doesn’t support the use of the `MERGE` command. As an alternative, consider using the `INSERT…​ ON CONFLICT` clause, which can handle cases where insert clauses might cause a conflict, and then redirect the operation as an update. ### Examples The following example uses the `ON ONFLICT` clause. ``` CREATE TABLE EMP_BONUS ( EMPLOYEE_ID NUMERIC, BONUS_YEAR VARCHAR(4), SALARY NUMERIC, BONUS NUMERIC, PRIMARY KEY (EMPLOYEE_ID, BONUS_YEAR)); INSERT INTO EMP_BONUS (EMPLOYEE_ID, BONUS_YEAR, SALARY) SELECT EMPLOYEE_ID, EXTRACT(YEAR FROM NOW()), SALARY FROM EMPLOYEES WHERE SALARY < 10000 ON CONFLICT (EMPLOYEE_ID, BONUS_YEAR) DO UPDATE SET BONUS = EMP_BONUS.SALARY * 0.5; SELECT * FROM EMP_BONUS; employee_id bonus_year salary bonus 103 2017 9000.00 4500.000 104 2017 6000.00 3000.000 105 2017 4800.00 2400.000 106 2017 4800.00 2400.000 107 2017 4200.00 2100.000 109 2017 9000.00 4500.000 110 2017 8200.00 4100.000 111 2017 7700.00 3850.000 112 2017 7800.00 3900.000 113 2017 6900.00 3450.000 115 2017 3100.00 1550.000 116 2017 2900.00 1450.000 117 2017 2800.00 1400.000 118 2017 2600.00 1300.000 ``` Running the same operation multiple times using the `ON CONFLICT` clause doesn’t generate an error because the existing records are redirected to the update clause. For more information, see [INSERT](https://www.postgresql.org/docs/13/sql-insert.html) and [Unsupported Features](https://www.postgresql.org/docs/13/unsupported-features-sql-standard.htm) in the *PostgreSQL documentation*. # Pivot and unpivot for T-SQL This topic provides reference information about feature compatibility between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL, specifically regarding the PIVOT and UNPIVOT operators. You can understand the differences in functionality and learn how to adapt your SQL queries when migrating from SQL Server to Aurora PostgreSQL. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [PIVOT and UNPIVOT](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.pivot) | Straightforward rewrite to use traditional SQL syntax. | ## SQL Server Usage `PIVOT` and `UNPIVOT` are relational operations used to transform a set by rotating rows into columns and columns into rows. ### PIVOT The `PIVOT` operator consists of several clauses and implied expressions. The *anchor column* isn’t pivoted and results in a single row for each unique value, similar to `GROUP BY`. The pivoted columns are derived from the `PIVOT` clause and are the row values transformed into columns. The values for these columns are derived from the source column defined in the `PIVOT` clause. #### PIVOT Syntax ``` SELECT , [Pivoted Column 1] AS , [Pivoted column 2] AS ...n FROM ()[;] ``` #### Examples The following example creates a table-valued function to aggregate employee orders. ``` CREATE TABLE Orders ( OrderID INT NOT NULL PRIMARY KEY, EmployeeID INT NOT NULL, OrderDate DATETIME NOT NULL ); ``` ``` INSERT INTO Orders (OrderID, EmployeeID, OrderDate) VALUES (1, 1, '20180101 13:00:05'), (2, 1, '20180201 11:33:12'), (3, 2, '20180112 10:22:35'); ``` ``` CREATE FUNCTION dbo.EmployeeMonthlyOrders (@EmployeeID INT) RETURNS TABLE AS RETURN ( SELECT EmployeeID, YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, COUNT(*) AS NumOrders FROM Orders AS O WHERE EmployeeID = @EmployeeID GROUP BY EmployeeID, YEAR(OrderDate), MONTH(OrderDate) ); ``` ``` SELECT * FROM dbo.EmployeeMonthlyOrders (1) EmployeeID OrderYear OrderMonth NumOrders 1 2018 1 1 1 2018 2 1 ``` ### Multi-Statement User-Defined Table-Valued Functions Multi-statement table-valued UDFs, such as In-line UDFs, are also similar to views or CTEs with the added benefit of parameters. You can use multi-statement table-valued UDFs in `FROM` clauses as sub queries. Also, you can join multi-statement table-valued UDFs to other source table rows using the `APPLY` and `OUTER APPLY` operators. The difference between multi-statement UDFs and the inline UDFs is that multi-statement UDFs aren’t restricted to a single `SELECT` statement. They can consist of multiple statements including logic implemented with flow control, complex data processing, security checks, and so on. The downside of using multi-statement UDFs is that there are far less optimizations possible and performance may suffer. #### Syntax ``` CREATE FUNCTION ([{ [AS] [= ] [READONLY]} [,...n]]) RETURNS <@Return Variable> TABLE
[AS] BEGIN RETURN END[;] ``` For more information, see [CREATE FUNCTION (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage For more information, see [Stored Procedures](chap-sql-server-aurora-pg.tsql.storedprocedures.md). ### Syntax ``` CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...]] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, ...] ) ] { LANGUAGE lang_name | TRANSFORM { FOR TYPE type_name } [, ... ] | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | PARALLEL { UNSAFE | RESTRICTED | SAFE } | COST execution_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' } ... [ WITH ( attribute [, ...] ) ] ``` # User-defined types for T-SQL This topic provides reference information about user-defined types in SQL Server and PostgreSQL, which is valuable for database administrators and developers migrating from Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL. You can gain insight into how both database systems implement custom data types, including their similarities and differences. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png) | ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png) | N/A | Syntax and option differences. | ## SQL Server Usage SQL Server user-defined types provide a mechanism for encapsulating custom data types and for adding NULL constraints. SQL Server also supports table-valued user-defined types, which you can use to pass a set of values to a stored procedure. User-defined types can also be associated to CLR code assemblies. Beginning with SQL Server 2014, memory optimized types support memory optimized tables and code. **Note** If your code uses custom rules bound to data types, Microsoft recommends discontinuing the use of this deprecated feature. All user-defined types are based on an existing system data types. They allow developers to reuse the definition, making the code and schema more readable. ### Syntax The simplified syntax for the `CREATE TYPE` statement is shown following. ``` CREATE TYPE { FROM [ NULL | NOT NULL ] | AS TABLE (
)} ``` ### User-Defined Types Examples The following example creates a `ZipCode` scalar user-defined type. ``` CREATE TYPE ZipCode FROM CHAR(5) NOT NULL ``` The following example uses this `ZipCode` type in a table. ``` CREATE TABLE UserLocations (UserID INT NOT NULL PRIMARY KEY, ZipCode ZipCode); INSERT INTO [UserLocations] ([UserID],[ZipCode]) VALUES (1, '94324'); INSERT INTO [UserLocations] ([UserID],[ZipCode]) VALUES (2, NULL); ``` The code in the preceding example displays the following error message indicating that NULL values for `ZipCode` aren’t allowed. ``` Msg 515, Level 16, State 2, Line 78 Can't insert the value NULL into column 'ZipCode', table 'tempdb.dbo.UserLocations'; column does not allow nulls. INSERT fails. The statement has been terminated. ``` ### Table-Valued Types Examples The following example demonstrates how to create and use a table-valued types to pass a set of values to a stored procedure. Create the `OrderItems` table. ``` CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) ); ``` Create a table-valued type for the `OrderItems` table. ``` CREATE TYPE OrderItems AS TABLE ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) ); ``` Create the InsertOrderItems procedure. Note that the entire set of rows from the table-valued parameter is handled with one statement. ``` CREATE PROCEDURE InsertOrderItems @OrderItems AS OrderItems READONLY AS BEGIN INSERT INTO OrderItems(OrderID, Item, Quantity) SELECT OrderID, Item, Quantity FROM @OrderItems; END ``` Instantiate the OrderItems type, insert the values, and pass it to a stored procedure. ``` DECLARE @OrderItems AS OrderItems; INSERT INTO @OrderItems ([OrderID], [Item], [Quantity]) VALUES (1, 'M8 Bolt', 100), (1, 'M8 Nut', 100), (1, M8 Washer, 200); EXECUTE [InsertOrderItems] @OrderItems = @OrderItems; (3 rows affected) ``` Select all rows from the OrderItems table. ``` SELECT * FROM OrderItems; OrderID Item Quantity 1 M8 Bolt 100 1 M8 Nut 100 1 M8 Washer 200 ``` For more information, see [CREATE TYPE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Similar to SQL Server, PostgreSQL enables the creation of user-defined types using the `CREATE TYPE` statement. A user-defined type is owned by the user who creates it. If a schema name is specified, the type is created under that schema. PostgreSQL supports the creation of several different user-defined types: \$1 Composite types store a single named attribute attached to a data type or multiple attributes as an attribute collection. In PostgreSQL, you can also use the CREATE TYPE statement standalone with an association to a table. \$1 Enumerated types (enum) store a static ordered set of values. For example, product categories. \$1 ``` CREATE TYPE PRODUCT_CATEGORT AS ENUM ('Hardware', 'Software', 'Document'); ``` + Range Types store a range of values, for example, a range of timestamps used to represent the ranges of time of when a course is scheduled. ``` CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi); ``` For more information, see [Range Types](https://www.postgresql.org/docs/13/rangetypes.html) in the *PostgreSQL documentation*. + Base types are the system core types (abstract types) and are implemented in a low-level language such as C. + Array types support definition of columns as multidimensional arrays. You can create an array column with a built-in type or a user-defined base type, enum type, or composite. ``` CREATE TABLE COURSE_SCHEDULE ( COURSE_ID NUMERIC PRIMARY KEY, COURSE_NAME VARCHAR(60), COURSE_SCHEDULES text[]); ``` For more information, see [Arrays](https://www.postgresql.org/docs/13/arrays.html) in the *PostgreSQL documentation*. ### Syntax ``` CREATE TYPE name AS RANGE ( SUBTYPE = subtype [ , SUBTYPE_OPCLASS = subtype_operator_class ] [ , COLLATION = collation ] [ , CANONICAL = canonical_function ] [ , SUBTYPE_DIFF = subtype_diff_function ] ) CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function [ , RECEIVE = receive_function ] [ , SEND = send_function ] [ , TYPMOD_IN = type_modifier_input_function ] [ , TYPMOD_OUT = type_modifier_output_function ] [ , ANALYZE = analyze_function ] [ , INTERNALLENGTH = { internallength | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] [ , LIKE = like_type ] [ , CATEGORY = category ] [ , PREFERRED = preferred ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] [ , COLLATABLE = collatable ] ) ``` ### Examples The following example creates a user-defined type for storing an employee phone numbers. ``` CREATE TYPE EMP_PHONE_NUM AS ( PHONE_NUM VARCHAR(11)); CREATE TABLE EMPLOYEES ( EMP_ID NUMERIC PRIMARY KEY, EMP_PHONE EMP_PHONE_NUM NOT NULL); INSERT INTO EMPLOYEES VALUES(1, ROW('111-222-333')); SELECT a.EMP_ID, (a.EMP_PHONE).PHONE_NUM FROM EMPLOYEES a; emp_id phone_num 1 111-222-333 (1 row) ``` The following example creates a PostgreSQL Object Type as a collection of Attributes for the employees table. ``` CREATE OR REPLACE TYPE EMP_ADDRESS AS OBJECT ( STATE VARCHAR(2), CITY VARCHAR(20), STREET VARCHAR(20), ZIP_CODE NUMERIC); CREATE TABLE EMPLOYEES ( EMP_ID NUMERIC PRIMARY KEY, EMP_NAME VARCHAR(10) NOT NULL, EMP_ADDRESS EMP_ADDRESS NOT NULL); INSERT INTO EMPLOYEES VALUES(1, 'John Smith', ('AL', 'Gulf Shores', '3033 Joyce Street', '36542')); SELECT a.EMP_NAME, (a.EMP_ADDRESS).STATE, (a.EMP_ADDRESS).CITY, (a.EMP_ADDRESS).STREET, (a.EMP_ADDRESS).ZIP_CODE FROM EMPLOYEES a; emp_name state city street zip_code John Smith AL Gulf Shores 3033 Joyce Street 36542 ``` For more information, see [CREATE TYPE](https://www.postgresql.org/docs/13/sql-createtype.html) and [Composite Types](https://www.postgresql.org/docs/13/rowtypes.htm) in the *PostgreSQL documentation*. # Identity and sequences for T-SQL This topic provides reference information comparing automatic enumeration features between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. It focuses on how these databases handle sequence generation and identity columns, which are commonly used for creating surrogate keys in relational database systems. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | N/A | Less options with `SERIAL`. Reseeding needs to be rewritten. | ## SQL Server Usage Automatic enumeration functions and columns are common with relational database management systems and are often used for generating surrogate keys. SQL Server provides several features that support automatic generation of monotonously increasing value generators. + `IDENTITY` property of a table column. + `SEQUENCE` objects framework. + Numeric functions such as `IDENTITY` and `NEWSEQUENTIALID`. ### Identity The `IDENTITY` property is probably the most widely used means of generating surrogate primary keys in SQL Server applications. Each table may have a single numeric column assigned as an `IDENTITY`, using the `CREATE TABLE` or `ALTER TABLE` DDL statements. You can explicitly specify a starting value and increment. **Note** The identity property doesn’t enforce uniqueness of column values, indexing, or any other property. Additional constraints such as primary or unique keys, explicit index specifications, or other properties must be specified in addition to the `IDENTITY` property. The `IDENTITY` value is generated as part of the transaction that inserts table rows. Applications can obtain `IDENTITY` values using the `@@IDENTITY`, `SCOPE_IDENTITY`, and `IDENT_CURRENT` functions. You can manage `IDENTITY` columns using the `DBCC CHECKIDENT` command, which provides functionality for reseeding and altering properties. #### Syntax ``` IDENTITY [(, )] ``` #### Examples The following example creates a table with an `IDENTITY` column. ``` CREATE TABLE MyTABLE ( Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY(1,1), Col2 VARCHAR(20) NOT NULL ); ``` The following example inserts a row and retrieve the generated `IDENTITY` value. ``` DECLARE @LastIdent INT; INSERT INTO MyTable(Col2) VALUES('SomeString'); SET @LastIdent = SCOPE_IDENTITY() ``` The following example creates a table with a non-key `IDENTITY` column and an increment of 10. ``` CREATE TABLE MyTABLE ( Col1 VARCHAR(20) NOT NULL PRIMARY KEY, Col2 INT NOT NULL IDENTITY(1,10), ); ``` The following example creates a table with a compound primary key including an `IDENTITY` column. ``` CREATE TABLE MyTABLE ( Col1 VARCHAR(20) NOT NULL, Col2 INT NOT NULL IDENTITY(1,10), PRIMARY KEY (Col1, Col2) ); ``` ### SEQUENCE Sequences are objects that are independent of a particular table or column and are defined using the `CREATE SEQUENCE` DDL statement. You can manage sequences using the `ALTER SEQUENCE` statement. Multiple tables and multiple columns from the same table may use the values from one or more `SEQUENCE` objects. You can retrieve a value from a `SEQUENCE` object using the `NEXT VALUE FOR` function. For example, a `SEQUENCE` value can be used as a default value for a surrogate key column. `SEQUENCE` objects provide several advantages over `IDENTITY` columns: + You can use `SEQUENCE` objects to obtain a value before the actual `INSERT` takes place. + You can share value series among columns and tables. + Easier management, restart, and modification of sequence properties. + Allows assignment of value ranges using `sp_sequence_get_range` and not just per-row values. #### Syntax ``` CREATE SEQUENCE [AS ] START WITH INCREMENT BY ; ``` ``` ALTER SEQUENCE RESTART [WITH ] INCREMENT BY ; ``` #### Examples The following example creates sequence and uses it for a primary key default. ``` CREATE SEQUENCE MySequence AS INT START WITH 1 INCREMENT BY 1; CREATE TABLE MyTable ( Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT (NEXT VALUE FOR MySequence), Col2 VARCHAR(20) NULL ); ``` ``` INSERT MyTable (Col1, Col2) VALUES (DEFAULT, 'cde'), (DEFAULT, 'xyz'); ``` ``` SELECT * FROM MyTable; ``` ``` Col1 Col2 1 cde 2 xyz ``` ### Identity SQL Server provides two sequential generation functions: `IDENTITY` and `NEWSEQUENTIALID`. **Note** The IDENTITY function should not be confused with the IDENTITY property of a column. You can use the IDENTITY function only in a `SELECT …​ INTO` statement to insert `IDENTITY` column values into a new table. The `NEWSEQUNTIALID` function generates a hexadecimal GUID, which is an integer. While the `NEWID` function generates a random GUID, the `NEWSEQUENTIALID` function guarantees that every GUID created is greater (in numeric value) than any other GUID previously generated by the same function on the same server since the operating system restart. You can use `NEWSEQUENTIALID` only with `DEFAULT` constraints associated with columns having a `UNIQUEIDENTIFIER` data type. #### Syntax ``` IDENTITY ( [, , ]) [AS ] ``` ``` NEWSEQUENTIALID() ``` #### Examples The following example uses the `IDENTITY` function as surrogate key for a new table based on an existing table. ``` CREATE TABLE MySourceTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 VARCHAR(10) NOT NULL, Col3 VARCHAR(10) NOT NULL ); ``` ``` INSERT INTO MySourceTable VALUES (12, 'String12', 'String12'), (25, 'String25', 'String25'), (95, 'String95', 'String95'); ``` ``` SELECT IDENTITY(INT, 100, 1) AS SurrogateKey, Col1, Col2, Col3 INTO MyNewTable FROM MySourceTable ORDER BY Col1 DESC; ``` ``` SELECT * FROM MyNewTable; ``` ``` SurrogateKey Col1 Col2 Col3 100 95 String95 String95 101 25 String25 String25 102 12 String12 String12 ``` The following example uses `NEWSEQUENTIALID` as a surrogate key for a new table. ``` CREATE TABLE MyTable ( Col1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWSEQUENTIALID() ); ``` ``` INSERT INTO MyTable DEFAULT VALUES; ``` ``` SELECT * FROM MyTable; ``` ``` Col1 9CC01320-C5AA-E811-8440-305B3A017068 ``` For more information, see [Sequence Numbers](https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-ver15) and [CREATE TABLE (Transact-SQL) IDENTITY (Property)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage The PostgreSQL `CREATE SEQUENCE` command is mostly compatible with the SQL Server `CREATE SEQUENCE` command. Sequences in PostgreSQL serve the same purpose as in SQL Server; they generate numeric identifiers automatically. A sequence object is owned by the user that created it. ### Sequence Parameters + `TEMPORARY` or `TEMP` — PostgreSQL can create a temporary sequence within a session. Once the session ends, the sequence is automatically dropped. + `IF NOT EXISTS` — Creates a sequence. If a sequence with an identical name already exists, it is replaced. + `INCREMENT BY` — An optional parameter with a default value of 1. Positive values generate sequence values in ascending order. Negative values generate sequence values in descending sequence. + `START WITH` — An optional parameter having a default of 1. It uses the MINVALUE for ascending sequences and the MAXVALUE for descending sequences. + `MAXVALUE` \$1 `NO MAXVALUE` — Defaults are between 263 for ascending sequences and -1 for descending sequences. + `MINVALUE` \$1 `NO MINVALUE` — Defaults are between 1 for ascending sequences and -263 for descending sequences. + `CYCLE` \$1 `NO CYCLE` — If the sequence value reaches `MAXVALUE` or `MINVALUE`, the `CYCLE` parameter instructs the sequence to return to the initial value (`MINVALUE` or `MAXVALUE`). The default is `NO CYCLE`. + `CACHE` — In PostgreSQL, the `NOCACHE` isn’t supported. By default, when the `CACHE` parameter isn’t specified, no sequence values are pre-cached into memory (equivalent to the SQL Server `NOCACHE` parameter). The minimum value is 1. + `OWNED BY` \$1 `OWNBY NON` — Specifies that the sequence object is to be associated with a specific column in a table. When dropping this type of sequence, an error is returned due to the sequence/table association. + `AS data_type` — This option is available in PostgreSQL version 10 and higher. To easily determine the minimum and maximum values and also improve storage management, you can select the data type for the sequence. The available data types are smallint, integer, and bigint. The default data type is bigint. ### Syntax ``` CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] ``` Most SQL Server `CREATE SEQUENCE` parameters are compatible with PostgreSQL. ### Examples The following example creates a sequence. ``` CREATE SEQUENCE SEQ_1 START WITH 100 INCREMENT BY 1 MAXVALUE 99999999999 CACHE 20 NO CYCLE; ``` The following example drops a sequence. ``` DROP SEQUENCE SEQ_1; ``` View sequences created in the current schema and sequence specifications. ``` SELECT * FROM INFORMATION_SCHEMA.SEQUENCES; OR \ds ``` The following example uses a PostgreSQL sequence as part of a `CREATE TABLE` and an `INSERT` statement. ``` CREATE TABLE SEQ_TST (COL1 NUMERIC DEFAULT NEXTVAL('SEQ_1') PRIMARY KEY, COL2 VARCHAR(30)); INSERT INTO SEQ_TST (COL2) VALUES('A'); SELECT * FROM SEQ_TST; col1 col2 100 A ``` Use the OWNED BY parameter to associate the sequence with a table. ``` CREATE SEQUENCE SEQ_1 START WITH 100 INCREMENT BY 1 OWNED BY SEQ_TST.COL1; ``` Query the current value of a sequence. ``` SELECT CURRVAL('SEQ_1); ``` Manually increment a sequence value according to the `INCREMENT BY` value. ``` SELECT NEXTVAL('SEQ_1'); OR SELECT SETVAL('SEQ_1', 200); ``` Alter an existing sequence. ``` ALTER SEQUENCE SEQ_1 MAXVALUE 1000000; ``` ### IDENTITY Usage Starting from PostgreSQL 10, there is a new option called identity columns which is similar to the `SERIAL` data type but more SQL standard compliant. The identity columns are slightly more compatible compared to SQL Server identity columns. To create a table with identity columns, use the following statement: ``` CREATE TABLE emps ( emp_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, emp_name VARCHAR(35) NOT NULL); INSERT INTO emps (emp_name) VALUES ('Robert'); INSERT INTO emps (emp_id, emp_name) VALUES (DEFAULT, 'Brian'); SELECT * FROM emps; col1 col2 1 Robert 2 Brian ``` In PostgreSQL, for `SERIAL` and `IDENTITY`, you can insert any value, so long as it won’t violate the primary key constraint. If the value violates the primary key constraint and you use the identity column sequence value again, the following error might be raised: ``` SQL Error [23505]: ERROR: duplicate key value violates unique constraint "emps_iden_pkey" Detail: Key (emp_id)=(2) already exists. ``` ### SERIAL Usage In PostgreSQL, you can create a sequence similar to the `IDENTITY` property supported by identity columns. When you create a new table, the sequence is created through the `SERIAL` pseudo-type. Other types from the same family are `SMALLSERIAL` and `BIGSERIAL`. By assigning a `SERIAL` type to a column during table creation, PostgreSQL creates a sequence using the default configuration and adds a NOT NULL constraint to the column. The newly created sequence behaves like a regular sequence (incremented by 1) and no composite `SERIAL` option. The following example uses `SERIAL` sequence. ``` CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10)); INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A'); SELECT * FROM SERIAL_SEQ_TST; col1 col2 1 A \ds Schema Name Type Owner public serial_seq_tst_col1_seq sequence pg_tst_db ``` The following example uses the PostgreSQL `SERIAL` pseudo-type with a sequence that is created implicitly. ``` CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10)); \ds Schema Name Type Owner public serial_seq_tst_col1_seq sequence pg_tst_db ALTER SEQUENCE SERIAL_SEQ_TST_COL1_SEQ RESTART WITH 100 INCREMENT BY 10; INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A'); INSERT INTO SERIAL_SEQ_TST(COL1, COL2) VALUES(DEFAULT, 'B'); SELECT * FROM SERIAL_SEQ_TST; col1 col2 100 A 110 B ``` Use the `ALTER SEQUENCE` command to change the default sequence configuration in a `SERIAL` column. Create a table with a `SERIAL` column that uses increments of 10: ``` CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10)); ALTER SEQUENCE serial_seq_tst_col1_seq INCREMENT BY 10; ``` **Note** The auto generated sequence’s name should be created with the following format: `TABLENAME_COLUMNNAME_seq`. Create a table with a compound primary key including a `SERIAL` column: ``` CREATE TABLE SERIAL_SEQ_TST (COL1 SERIAL, COL2 VARCHAR(10), PRIMARY key (COL1,COL2)); ``` ## Summary The following table identifies similarities, differences, and key migration considerations. | Feature | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | Independent `SEQUENCE` object | `CREATE SEQUENCE` | `CREATE SEQUENCE` | | Automatic enumerator column property | `IDENTITY` | `SERIAL` or `IDENTITY` | | Reseed sequence value | `DBCC CHECKIDENT` | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.sequences.html) | | Column restrictions | Numeric | Numeric | | Controlling seed and interval values | `CREATE/ALTER SEQUENCE` | `CREATE/ALTER SEQUENCE` | | Sequence setting initialization | Maintained through service restarts | `ALTER SEQUENCE` | | Explicit values to column | Not allowed by default, `SET IDENTITY_INSERT ON` required | Allowed | For more information, see [CREATE SEQUENCE](https://www.postgresql.org/docs/13/sql-createsequence.html), [Sequence Manipulation Functions](https://www.postgresql.org/docs/13/functions-sequence.html), [Numeric Types](https://www.postgresql.org/docs/13/datatype-numeric.html), and [CREATE TABLE](https://www.postgresql.org/docs/13/sql-createtable.html) in the *PostgreSQL documentation*.