Skip to main content

Identifier case

identifierCase sets the case of unquoted names: tables, columns, aliases, schemas.

Default: preserve, and we recommend leaving it there. A keyword is a word of the language, but a name is an object that already exists in your database, spelled one way. Re-casing names rewrites the query rather than its layout:

select 42 as TotalAmount   -- duckdb returns a column called TotalAmount
select 42 as totalamount   -- lower-cased, it returns one called totalamount
  • It renames result columns on every engine that keeps the spelling you wrote: DuckDB, SQLite, Spark, Databricks, Trino and SQL Server. A dashboard reading the old name breaks.
  • It reaches words that only look like names. A JSON key and a path into cloud storage are data, and data is compared exactly.
  • Whether a name is compared exactly can depend on the database rather than on the SQL, which no formatter can see.

Other formatters agree: sql-formatter marks its identifierCase experimental and not recommended, and pgFormatter leaves identifiers unchanged unless asked.

Set lower or upper where the engine folds every unquoted name anyway, so that the case in the file is cosmetic. PostgreSQL folds to lower, Oracle and Snowflake to upper; there the setting makes MyCol and mycol look like the one name they are. Quoted names are never re-cased on any dialect.

PostgreSQL, default and lower​

SELECT MyCol FROM MyTable t
SELECT MyCol FROM MyTable t
SELECT MyCol FROM MyTable t
SELECT mycol FROM mytable t

Where it is still unsafe, even where dfmt allows it. Each of these needs something the formatter cannot see in the text.

  • A JSON field read with a dot. DuckDB's j.UserName is a key lookup if j holds JSON and a struct field if j holds a struct: the same syntax, told apart only by the column's type. Keys are compared exactly and struct fields are not, so on DuckDB a set identifierCase can lower a key and turn the value into NULL. Write the key quoted, or leave the setting at preserve.
  • A Snowflake stage path. copy into t from @MyStage/Data/Year=2024/ is a path into cloud storage, where Data/ and data/ are different prefixes. dfmt reads the path's words as names, so a set identifierCase re-cases them. Its spacing is safe: the path is one lexical unit and stays whole.
  • A case-sensitive collation. SQL Server compares names by the database's collation, and a database created with a _CS_ collation compares them exactly. Nothing in the file says which database the SQL will run against.

Where it is refused. When you do set it, dfmt applies it only where the engine compares every kind of name without regard to case, and only to the letters the engine folds: PostgreSQL folds A to Z and nothing else, so Ä stays Ä. It is ignored, with a note, on the engines that compare some kind of name exactly as written: BigQuery (dataset and table names), MySQL, MariaDB, SingleStore, Doris, StarRocks and PlanetScale (table names), ClickHouse (everything); and on an engine whose rules nobody has measured: Athena, Fabric, Synapse and Dune. TiDB speaks MySQL's protocol and shares its grammar, and yet T finds t there at every role, measured on the engine, so it is allowed where its family is refused. Each dialect states its own answer rather than inheriting a relative's.

A path into semi-structured data is not a name​

In Snowflake, v:UserName reads the key UserName out of a VARIANT, and keys are compared exactly. v:username returns NULL. The column v is a name; the keys after it are not.

select V:UserName, V:Address.City from t
select v:UserName, v:Address.City from t