naming a table "order" is giving error
(1) By anonymous on 2022-04-27 11:41:52 [link] [source]
I am using DB Browser for SQLite so i cant say if its a bug in Browser or SQLite but the problem is: Naming a table 'order' doesn't give any errors while creating table but when query 'select * from order' is executed, it gives syntax error(all happening in DB Browser for SQLite). And when its renamed to 'orders', it shows perfect results. I understand that 'order' is a keyword/token used by SQL for ordering selected data but following the syntax 'select * from order', it should have been working.
(2) By Richard Hipp (drh) on 2022-04-27 11:46:32 in reply to 1 [link] [source]
Put double-quotes around the table name:
SELECT * FROM "order";
(3) By Harald Hanche-Olsen (hanche) on 2022-04-27 12:28:39 in reply to 1 [source]
As Richard said.
SQLite, as well as many other SQL engines (at least PostgreSQL, which I know best), accept the use of keywords as identifiers without quoting in some places, wherever that usage does not cause confusion. But unfortunately, that is not a very precise condition. Life would perhaps have fewer surprises if the practice was never allowed, but that is not where we are today.
You might argue that, since following the keyword FROM immediately by the keyword ORDER, the parser might decide that ORDER in that position must be an identifier. But consider how confusing the following might be, at least to a human reader:
select foo from order order by bar;
Personally, I am glad that is not allowed.
(4) By Gunter Hick (gunter_hick) on 2022-04-27 13:43:42 in reply to 3 [link] [source]
How about adding an alias "order" to produce select order.foo from order order order by order.bar;
(5) By Ryan Smith (cuz) on 2022-04-27 17:20:02 in reply to 1 [link] [source]
Others have already mentioned all needed to understand the situation, I'll just add this simple rule-of-thumb which covers it:
Database objects (such as tables, views, etc.) may all be named anything whatsoever, even nothing, as long as each object name (aka identifier) is unique.
However, the parser needs to be able to differentiate between keywords and identifiers, so if you do use a keyword as an identifier, you must place it inside double-quotes - though on occasion if the parser is pretty sure the next word can only be an identifier, it may still regard it so and accept it without quotes.
Behind the scenes it's also possible DB Browser (or whichever DB-admin tool you use) may quote identifiers for you.
Either way, you should never use keywords as identifiers unquoted, in fact my advice is to never use keywords as identifiers, period - that way you will also never need to quote identifiers, which makes for much much better code legibility and easier in-program string compilation.