Redshift table column name auto convert to lowercase issue

Question

I am facing an issue while fetching the data via query from a redshift table. For example:

table name:   test_users
column names: user_id, userName, userLastName

Now while creating the test_users table it converts the capital letter of the userName column to username and similar with userLastName which will be converted to userlastname.

I have found the way to convert the all columns to capital or in lowercase, but not in the way to get it as it is.

Answer 1

To preserve case:

• SET enable_case_sensitive_identifier TO true;

  https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html

To force returned uppercase fields (for anyone else curious):

• SET describe_field_name_in_uppercase TO on;

  https://docs.aws.amazon.com/redshift/latest/dg/r_describe_field_name_in_uppercase.html

Answer 2

Unfortunately, AWS Redshift does not support case-sensitive identifiers at the time of writing (Feb 2020). And, while Redshift is based on PostgreSQL, AWS has heavily modified it to the point where many assumptions that would be correct for PostgreSQL 8 are not correct for Redshift.

The documentation at https://docs.aws.amazon.com/redshift/latest/dg/r_names.html explicitly states that it downcases identifiers. The relevant paragraph is below, with the critical sentence bolded:

Names identify database objects, including tables and columns, as well as users and passwords. The terms name and identifier can be used interchangeably. There are two types of identifiers, standard identifiers and quoted or delimited identifiers. Identifiers must consist of only UTF-8 printable characters. ASCII letters in standard and delimited identifiers are case-insensitive and are folded to lowercase in the database. In query results, column names are returned as lowercase by default. To return column names in uppercase, set the describe_field_name_in_uppercase configuration parameter to true.