I have JSON data containing mixed-case keys in a column of type SUPER. Example JSON:
{
"MyKey1": "value 1",
"MyKey2": "value 2"
}
Long story short, to query fields I have to set enable_case_sensitive_identifier to true and quote the identifiers.
set enable_case_sensitive_identifier to true;
select column."MyKey1", column."MyKey2" from table;
Without setting enable_case_sensitive_identifier to true, redshift downcases the identifiers and returns null for "mykey1" and "mykey2".
But now I need to make a view where I split the JSON into individual columns, with a query such as the above. And it's back to downcasing and returning null when I query the view.
Is there any way to set a parameter in a view? Or some other way to force case-sensitive identifiers?
AWS OFFICIALUpdated 3 months ago
"ALTER USER admin SET enable_case_sensitive_identifier = TRUE;" you can use this code. This will switch on case sensitive for SUPER type for admin user. Or you can add it on cluster level for all users. But this "SET enable_case_sensitive_identifier = TRUE; " don't work for session. Be awere if you will create table with <"ColumnName"> it will be apply on table as <ColumnName> butn if you will create table with <ColumnName> it will apply as <columnname>
"SET enable_case_sensitive_identifier = TRUE" works if you reboot your cluster.