Posted On: Aug 13, 2021
Amazon Redshift, a fully-managed cloud data warehouse, now supports case-insensitive collation with column and expression level overrides. Starting today, you can use the COLLATE clause within a CREATE DATABASE statement to specify the default collation for all CHAR and VARCHAR columns in the database as case-sensitive or case-insensitive. You can also specify a COLLATE clause in a CREATE TABLE statement to specify collation for columns in that table. In addition, the COLLATE() function allows you to override the collation of a string column or an expression.
Collation is a set of rules that defines how database engines compare and sort the character type data in SQL. With native support for case-insensitivity collation, when you specify the CASE_INSENSITIVE clause on a database or a table, all CHAR or VARCHAR columns use case-insensitive collation. Data inserted or ingested in case-insensitive columns will keep its original case, but all comparison-based string operations including sorting and grouping will be case-insensitive. Pattern matching operations such as LIKE predicates, similar to, and regular expression functions will also be case-insensitive. Distribution keys and Sort keys are supported for any VARCHAR or CHAR columns in case-insensitive tables, and range-restricted scans are also supported on case-insensitive columns.
To get started and learn more about native case-insensitive collation support and column/expression level overrides, visit our documentation for CREATE DATABASE, CREATE TABLE, and the COLLATE function.
Case Insensitivity support is now available in the Amazon Web Services China (Beijing) region, operated by Sinnet and the Amazon Web Services China (Ningxia) region, operated by NWCD. Please refer to the AWS Region Table for Amazon Redshift availability.