Posted On: Jun 24, 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.
Case Insensitivity support is now available in all commercial regions. Please refer to the AWS Region Table for Amazon Redshift availability.