troubleshooting

Why are quotes necessary when querying a column in a Kafka table with schema support?

André Falk encountered an issue where querying a column without quotes failed, but querying with quotes succeeded in a Kafka table with schema support. The discussion led to the explanation that the column names are case-sensitive due to the schema definition.

An

André Falk

Asked on Dec 20, 2023

  • In PostgreSQL, identifiers are considered case-insensitive unless quoted.
  • When creating a table with a schema in CAPS, such as { "FOO": string, "BAR": int }, the column names become case-sensitive.
  • Querying a column without quotes may result in the column name being cast to lowercase, causing the query to fail.
  • Using quotes around the column name, like SELECT "foo" FROM foobar;, ensures that the query references the correct case-sensitive column name.
  • This behavior is standard in PostgreSQL and helps prevent ambiguity in column names when querying.
Dec 20, 2023Edited by