3 ms·

SQLite has specific naming advice: "For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object... "SQLi

by chasil 1y ago

SQLite has specific naming advice: "For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object... "SQLite adds new keywords from time to time when it takes on new features. So to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to." https://sqlite.org/lang_keywords.html https://sqlite.org/lang_keywords.html

hvenev 1y ago
English also changes, so the only way to be safe is to quote all identifiers.
- 01HNNWZ0MV43FF 1y ago
  I'll just stop upgrading SQLite if they ever add "rizz" as a keyword
phoyd 1y ago
I want to drop here the not very well known fact, that the SQL Standard grammar distinguishes between "SQL language identifier" and "regular identifier". According to the rules, a SQL language identifier can not end with an underscore (copied from ISO/IEC 9075-2:1999 "5.4 Names and identifiers": <SQL language identifier> ::=<SQL language identifier start> [ { <underscore> | <SQL language identifier part> }... ] <SQL language identifier start> ::= <simple Latin letter> <SQL language identifier part> ::= <simple Latin letter> | <digit> So, using names with trailing underscore should always be safe.
- majewsky 1y ago
  It_ might_ be_ safe_, but_ it_ is_ definitely_ annoying_.
isoprophlex 1y ago
French table and column names, here I come! La vache query!
SQLite 1y ago
I think (I hope!) we are probably done adding keywords to SQLite. Furthermore, all of the more recently added keywords (ex: WITHIN, RETURNING, MATERIALIZED) make use of special capabilities in SQLite's parser that allows keywords to be used as identifiers as long as the identifier usage does not occur in a context where the keyword is allowed. So, for example, you can used MATERIALIZED as a keyword in a common-table expression ("WITH xyzzy(a,b) AS MATERIALIZED (...)") but MATERIALIZED can also be used as a column or table name. Hence, the following SQL actually works in SQLite: WITH xyz(MATERIALIZED) AS MATERIALIZED( VALUES(1),(2),(3) ) SELECT * FROM xyz;

by chasil 1y ago

hvenev 1y ago

English also changes, so the only way to be safe is to quote all identifiers.

01HNNWZ0MV43FF 1y ago
I'll just stop upgrading SQLite if they ever add "rizz" as a keyword

phoyd 1y ago

I want to drop here the not very well known fact, that the SQL Standard grammar distinguishes between "SQL language identifier" and "regular identifier". According to the rules, a SQL language identifier can not end with an underscore (copied from ISO/IEC 9075-2:1999 "5.4 Names and identifiers": <SQL language identifier> ::=<SQL language identifier start> [ { <underscore> | <SQL language identifier part> }... ] <SQL language identifier start> ::= <simple Latin letter> <SQL language identifier part> ::= <simple Latin letter> | <digit> So, using names with trailing underscore should always be safe.

majewsky 1y ago
It_ might_ be_ safe_, but_ it_ is_ definitely_ annoying_.

isoprophlex 1y ago

French table and column names, here I come! La vache query!

SQLite 1y ago

I think (I hope!) we are probably done adding keywords to SQLite. Furthermore, all of the more recently added keywords (ex: WITHIN, RETURNING, MATERIALIZED) make use of special capabilities in SQLite's parser that allows keywords to be used as identifiers as long as the identifier usage does not occur in a context where the keyword is allowed. So, for example, you can used MATERIALIZED as a keyword in a common-table expression ("WITH xyzzy(a,b) AS MATERIALIZED (...)") but MATERIALIZED can also be used as a column or table name. Hence, the following SQL actually works in SQLite: WITH xyz(MATERIALIZED) AS MATERIALIZED( VALUES(1),(2),(3) ) SELECT * FROM xyz;