Skip to content
Daniël van Eeden edited this page Mar 13, 2024 · 14 revisions

Normal identifiers

Most dialects support [a-zA-Z_] as first character and [a-zA-Z0-9_] as rest of the characters. The differences from this are listed below:

  • BigQuery: single dashes (-) can be used, but not at the beginning or end.
  • DB2: first char can be an uppercase letter (a lowercase letter gets converted to uppercase). My testing in dbfiddle shows that @, #, or $ characters can be used anywhere inside an identifier.
  • DB2i: like DB2. The IBM i docs state that only the first char can be @, #, or $, but #550 suggests that these can also appear after first character.
  • Hive: (no differences)
  • MariaDB: no first-letter restrictions. The characters [a-zA-Z0-9_$] and unicode letters are allowed everywhere. Can begin with digit, but can't only contain digits.
  • MySQL: same as MariaDB.
  • N1QL: (no differences)
  • PL/SQL: can't start with _. Allows $, # in rest of the identifier.
  • PostgreSQL: additionally $ after first char. Also unicode letters are allowed.
  • Redshift: also unicode letters are allowed. # is allowed as the first char of temporary table names.
  • SingleStoreDB: Same as MariaDB.5
  • Snowflake: additionally $ after first char allowed.
  • Spark: Seems like the usual syntax is allowed. But the docs are confusing.
  • SQLite: (no differences)
  • TiDB: Same as MySQL and MariaDB.
  • Transact-SQL: @ and # are allowed as first chars plus $ in the rest. Also unicode letters are allowed. Though the beginning @ signifies a local variable or parameter and # a temporary table or procedure.
  • Trino: (no differences)4

Delimited identifiers

SQL standard specifies double-quotes ".." for delimited identifiers. There is a considerable variation in implementations:

  • `..` BigQuery
  • ".." DB2 (repeated " used for escaping)
  • ".." DB2i It seems that escaping is not supported. Also a single quote ' can be configured for quoting.
  • `..` Hive (repeated ` used for escaping)
  • `..`, (".."1, [..]2) MariaDB (repeated ` used for escaping)
  • `..`, (".."1) MySQL (repeated ` used for escaping)
  • `..` SingleStoreDB5
  • `..` N1QL
  • ".." PL/SQL (escaping of quotes is not supported)
  • "..", U&".." PostgreSQL (repeated " used for escaping)
  • ".." Redshift (repeated " used for escaping)
  • ".." Snowflake (repeated " used for escaping)
  • `..` Spark (repeated ` used for escaping)
  • "..", `..`, [..] SQLite (repeated " or ` used for escaping)
  • `..`, (".."1) TiDB (repeated ` used for escaping)
  • ".."3, [..] Transact-SQL (repeated " or ] used for escaping)
  • ".."4 Trino (repeated " used for escaping)

Notes:

  1. when ANSI_QUOTES mode enabled
  2. when MSSQL mode enabled
  3. unless QUOTED_IDENTIFIER option has been set OFF
  4. Trino grammar lists `..`-quoted identifiers and identifiers starting with number, only to print an error message saying: these aren't supported.
  5. Tested SingleStoreDB syntax manually. Haven't found any documentation for these low-level details.
Clone this wiki locally