SQL reserved words – An empirical list | Hacker News Reader

SQL reserved words – An empirical list

modern-sql.com

177 pointsmariuz114 comments

SQL's most egregious reserved keyword is, in my opinion, "user". Almost every application will have a users table. I gravitate towards singular table names these days [1], and SQL sitting its fat behind on the word "user" means I always have to defer to "users" just for the users table. There is almost no other keyword that I regularly run into conflict with other than "user". I don't even use the "user" keyword in any SQL queries, what a terrible trade off.

[1] I used to prefer plural names, but now I favor singular names because no naming headaches with the plural intricacies of the English language.

Instead of picking a different name for your table, you can quote the table name in double quotes.

("user" is what got me in the habit of doing this unconditionally, to the point where SQL with bare table/column names looks weird to me now.)

I can't. I forgot which stackoverflow comment or blog post mentioned it, but unquoted lowercase identifiers are the most portable and resilient naming convention that work across all database dialects. You don't have to worry about whether your database preserves case, folds everything to uppercase (Oracle) or folds everything to lowercase (Postgres) if you only stick with unquoted lowercase identifiers, and your SQL queries pretty much look the same between all databases as long as you stick with ANSI SQL.

Surely quoted identifiers are the most portable? If you quote everything you get to skip the entire normalisation issue, as well as the keywords issue.

in MySQL backticks are used as quotes for tables/columns

You would think that right, but quoting an identifier is the start of misery. It means everytime you invoke the identifier, you have to quote it. If you don't quote it, you open yourself to bugs. It's a giant footgun. "But I'm quoting lowercase identifiers, so even if I forget the quotes it's fine". It's not. Only Postgres folds unquoted identifiers to lowercase. Oracle and SQL Server fold unquoted identifiers to uppercase. MySQL does the weird thing where it either folds to uppercase or preserves case sensitivity depending on whether you're running it on Windows or Unix (fun!). So not quoting your identifiers now means its behaviour is dependent on what database configuration you're using. It's not worth it. By using only lowercase unquoted identifiers, you can guarantee it behaves identically across every database, because even if they fold to uppercase or fold to lowercase or preserves case your lowercase unquoted identifiers get normalized accordingly to the database's rules and it all works without a hitch. Even if other people bring their weird database-isms like uppercasing identifiers and lowercasing keywords (like my company, sigh), it all works seamlessly. Say no to quoted identifiers, unless you want to saddle your developers with additional burden everytime they write an SQL query that touches the database.

Oh and yeah, every database brings its own opinion on what [quoting] `should` "look" 'like'.

> It means everytime you invoke the identifier, you have to quote it.

Well yes hence “use quoted identifiers for maximum compatibility”. That does not mean “use quoted identifiers except when you don’t want to”.

I don't know how to reply to that except "experience tells me it is miserable to mandate everyone to quote their identifiers when they touch your database". Do you do that?

Yes.

Use Human.

Don’t use human. Many systems have API users, and with subscription cycles, humans can wind up with multiple users.

i use user_accounts for homo sapiens living organisms and service_accounts for apis.

What should I use for the felis catus living organisms? They want root accounts, but those already belong to sequoia sempervirens living organisms.

I probably should have gone with accounts and had a species column.

Use human and robots. And UNION every query.

Just kidding. Then you'd eventually need extraterrestrials, sentient_ai, ghosts, and all kinds of extras.

SQL doesn't say you can't use than name. All that a reserved word means is that you need to quote it to disambiguate.

  CREATE TABLE "user" (
    id int PRIMARY KEY,
    name varchar(50) NOT NULL
  );

In fact, in this respect, SQL is much better than most languages with their `clazz` and `klass` and `func`.

The only other language like this I can think of is Rust. You can write r#keyword to quote a keyword to be used as an identifier.

This solution was devised as a means to add keywords to new editions of the language without breaking code written in the old edition that named this stuff the same as the keyword (and with full interoperability between code from new editions and old editions)

So if some function in some Rust 2015 library was named async (a new keyword introduced in Rust 2018), you can call it like r#async() in newer versions

In C# you can prefix with @ to achieve the same thing.

In Scala it's backticks.

   val `val` = 1

In kotlin you can use backticks to "unkeywordify" a name

In Julia you can also use var”any string” to use any string as a variable name

I'm sure you know this, but this is actually a special case of a much more general rule; in Julia, prefixed string literals are implemented are passed on to macros: https://docs.julialang.org/en/v1/manual/metaprogramming/#met...

Such macros can be defined by libraries or the end user to provide special behavior.

To join the other commenters, in Nim you use backticks to allow using Nim keywords - this is important for e.g. nice deserialization where some JSON object has a field like `type` which is a keyword in Nim.

https://nim-lang.org/docs/json.html#overview-unmarshalling

in php all variables are prefixed so you don't have to worry about keyword collisions at all

Iv'e inherited a database with both a USER and an ORDER table, both are central to the app and involed in pretty much every query. I feel your pain.

What word would you pick instead of "USER" for users in the context of DBMS ?

I just name it users and accept the minor inconsistency that every table is singular except for the users table (it has the lowest cognitive overhead IMO)

No, I mean the word USER in a statement like below

  CREATE USER user_name   
    [
      { FOR | FROM } LOGIN login_name   
    ]  
  [ WITH <limited_options_list> [ ,... ] ]   
  [ ; ]

It's a shame that the classic style separation of lexing and parsing means to support the command CREATE USER FOR LOGIN WITH means all of those are now magic keywords, instead of only behaving so in the context of a CREATE command.

As for how to name the "user" table, I've used "account". User is a human not a piece of data, Account is the data I have about their association with my service.

> Note that you can still use these words as identifiers by putting them under double quotes (").

Of surrounding by square brackets in SQL Server, like [so] instead of like "so", which despite being non-standard is more commonly used in the MS SQL world because its behaviour is much more consistent than quotes in that environment.

Quoted (rather than bracketed) identifiers are supported but it depends upon a setting which may vary per DB or procedure. It is common to see the option ON these days a some features¹ depend upon it, and many tools like MS's SSMS default it to ON, but this can not at all be relied upon. See https://learn.microsoft.com/en-us/sql/t-sql/statements/set-q...

[1] From the documentation: “SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, then CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns, or tables with indexed views.”

Thank you for the regularly scheduled reminder as to why nobody should ever use MS SQL for any reason whatsoever.

Until any other RDBMS has the combination of now-basic features like SQL:2011, declarative schema design with terraform-like plan/apply migrations (ssdt, azure data studio, DACPAC, etc.), easily configurable hosted cloud databases, actual good MERGE syntax, and more, I'll keep using it. Attempting to use Maria was a nightmare.

I'd like Postgres more (and really want to) if they would at least implement more basic stuff like SQL:2011 and/or there was a good hosted deploy story - RDS/Aurora is fairly complex.

Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?

You can compare the database schema in source control against what's been deployed, and automatically generate deployment scripts.

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpa...

The APIs were historically part of Visual Studio, and only split out into dacfx recently.

Someone braver than me could consider automated deployment from github actions etc.

The hard part is that a lot of database schema changes cause data truncation (e.g. reducing the size of a column or re-ordering columns).

You still need someone to review the generated code if it throws an error for potential data loss.

>You still need someone to review the generated code if it throws an error for potential data loss.

Yeah, exactly what I meant by the terraform-like plan/apply stages. You generate the change script and save it as your "plan", and then have an apply step that takes a backup and runs the script.

I wonder why you are so focused on SQL:2011? Since then there was SQL:2016 and now there is SQL:2023.

Also, which features in particular are you missing in PostgreSQL? Merge was added with PostgreSQL 15 a year ago: https://modern-sql.com/caniuse/merge

Temporal tables, most likely. Although even SQL Server only supports system time versioning, not full bitemporal tables (as per the spec).

The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.

> there is an unsolved elephant in the room: It doesn't cover schema changes.

100% agreed. It's remarkable how Datomic also arrived on the scene in the same era (2012) but actually managed to solve a lot of these hard issues of immutable versioning + schema evolution via a clean EAV-based information model and an emphasis on accrete-only schema changes.

I'm a big fan of your work by the way :)

2016/2023 got pretty widespread support across databases for the functionality I care about, both before and after adoption - namely the JSON stuff. MSSQL also already had pretty good graph support AFAIK, although I haven't used it that much.

I find the temporal table stuff really useful and they drastically simplify a number of requirements, so it's annoying that the only non-proprietary DB that supports it is maria.

Why won't SQL Server implement the ANSI standard for Persistent Stored Modules (PSM)?

https://en.wikipedia.org/wiki/SQL/PSM

This is so pervasive in both free and commercial databases, that it is a gaping hole in SQL Server where obvious functionality should be.

I have thousands of lines of this stuff that will never see the light of day in a conversion because of Sybase Transact-SQL.

Why is Microsoft addicted to this very much not standard language, that they did not even originate?

> actual good MERGE syntax

While not the shit-show of bugs it was upon introduction in SQL Server 2008, there are still reasons to be careful with MERGE in SQL Server: it can still deadlock with itself in some circumstances, has issues with filtered indexes, can cause problems with CDC (wrong operation(s) get logged), …

(I actually like SQL Server, but the implementation of MERGE found there-in is certainly not one of the things I like about it!)

Having slightly different syntax seems like a poor reason to not use MS SQL.

It is one of the better ones out there. I shy away from it because of cost. If it was similar in cost to the other ones then I would probably use it a lot more. Been using it from when it was watcom (for about 50 to 250 bucks a copy). Then MS took over and the cost went crazy not oracle crazy but crazy enough.

MSSQL is one of the best rdbms you can buy, the absolute best for some use cases. Its only downside is that it’s expensive. T-SQL idiosyncrasies are inconsequential.

Would not some variants of Sybase be better, as the architecture is the same, and it has supported far more platforms which allow it to scale in ways that Microsoft SQL Server cannot?

I’d been running couple TB mixed olap + oltp on 2016 a few years ago and a single cluster handled what otherwise today would be 3 or 4 different data stores today. Performance of properly tuned column stores was literally mind blowing. Haven’t even needed memory optimized tables.

Can’t comment on Sybase. Didn’t need any other platforms.

Sybase and SQL Server came from the same code until release 4.8, so it would be familiar to you.

That was 30 years ago, though. Things have probably diverged quite a bit.

There were significant under-the-hood rewrites for SQL Server 2005 at least, and no doubt many smaller changes throughout the versions even where not adding new features, though there is still plenty that remains the same or at least very similar. I can name one bug from way-back-then which will never be fixed because maybe something somewhere “relies” on the odd behaviour!

I really gotta appreciate that someone's willing to say this out loud, rather than just pretending than MSSQL must be horrible because it comes from MS. If MSSQL was OSS Postgres wouldn't stand a chance.

This is ridiculous, Postgres is hands down a superior database at any price point.

Sorry, no. Not even close. Postgres is very good at what it can do and very cheap, but it just doesn’t do very much compared to SQL server or others. I’ve ran both mssql and Postgres in prod in the same project in perhaps not big but still nontrivial scale (low single digit terabytes) and SQL server made some things possible which Postgres simply didn’t have an answer for - column stores being the notable single killer feature if I had to name only one.

I'm currently running SQL Server and Postgres in the same project, and I would love to dump SQL Server. Its geospatial "capabilities" are so laughably bad, they should just stop advertising that they exist at all, and the unpredictable locking behavior alone should disqualify it from being used in any modern software project. And sure, it has column stores, which postgres has in extensions, but even if I needed column stores, SQL Server would still be last on my list, because there are so many better options out there.

select [select] from [from] where [where] = [equals]

This is the way.

INSERT INTO [FROM] ([SELECT], [WHERE], [EQUALS]) VALUES( 'WHY YES', 'IT DOES', 'WORK')

This is how you get maintenance developers to take out hits on you.

Bonus points if the tables contain highly abstracted data, so that there is no obvious name you could rename the tables and their columns either.

Here is my try :

  WITH [WITH] AS (
    SELECT [AS], [IN], [ON]
    FROM [UNION]
    WHERE [AND] = [OR]
  ),
  [OUTER] AS (
    SELECT [LEFT], [RIGHT], [FULL]
    FROM [CROSS]
    WHERE [INNER] = [OUTER]
  ),
  [GROUP] AS (
    SELECT COUNT(*) AS [HAVING]
    FROM [ORDER]
    GROUP BY [GROUP]
    HAVING COUNT(*) > 1
  )
    SELECT 
    [WITH].[AS], 
    [OUTER].[LEFT], 
    [GROUP].[HAVING]
    FROM 
    [WITH]
  JOIN 
    [OUTER]
  ON 
    [WITH].[IN] = [OUTER].[RIGHT]
  LEFT JOIN 
    [GROUP]
  ON 
    [WITH].[ON] = [GROUP].[HAVING]
  WHERE 
    [WITH].[AS] = [OUTER].[LEFT]
  AND 
    ([GROUP].[HAVING] IS NULL OR [GROUP].[HAVING] > 1)
  ORDER BY 
    [WITH].[AS] ASC;

I shouldn't even try. I'm clearly way out of my league here. Have an updoot.

That’s actually pretty clean in terms of being self documenting.

Not strictly related by I really enjoy using SQL as a general-purpose programming language for coding challenges. Here's my n queens solution in SQL, for example: https://gist.github.com/seisvelas/952185983a625cd16e1ed4d901.... I made that because I ran out of challenges on CodeWars.

implementing algorithms in SQL really made my intuition for SQL skyrocket, to the point where no tasks in my day to day data engineering role were very challenging - at least not in terms of dealing with crazy joins and subqueries and such.

I miss SQL now days (as an appsec dev). Currently, I'm learning Hoon, which is too intuitive to really replace SQL as a language for doing toy problems while having to think wierd.

Wow, that n queens implementation is super impressive! I firmly fall into the first category of SQL knowledge, but I definitely know enough to be impressed.

My pet silliness in sql from a couple years ago: https://github.com/chunky/sqlraytracer

You remind me I need to finish my hunt the wumpus implementation. (sqlite has an easy way to get user input, mid query...)

Maybe allowing bare words as both identifiers and keywords that could occur in the same position was never the right solution. SQL's roots start young in programming language design so no shame on anybody for not knowing. But e.g. if you're developing an ORM today that generates SQL strings and that SQL dialect might change in a DB upgrade later you should just be always quoting user identifiers.

Is there any programming language which doesn't have this property? All of the mainstream ones certainly use bare words as both keywords and identifiers, and there can sometimes be ambiguity. Most don't even have a solution like quoted identifiers (though quite a few do).

Forth and Lisp? :)

I'll admit that I'm not an expert here but to my eye it's a combination of bare words and cases where the position might be amgiuous, combined with the range of dialects where your reserved words could even change in the future. `select distinct` could be `select [distinct]` or just `select distinct`. Compare to say C where `sometype somebareword` isn't ambiguous because `sometype` is always a language keyword. But wait you say, with typedefs sometype could well be a `typedef stuct somebareword! Yep, it's a problem here too and I've been lying all along.

So that's some rambling but I think you're right, it's just a problem in SQL because of the range of dialects and the fact that they can change out from under you.

> Forth and Lisp? :)

Not having keywords at all is cheating :)

Or, for Common Lisp at least, perhaps we can say that (,), #, ;, ", `,', :, commas, whitespace, and numbers are actually the keywords, since most other ASCII chars are valid identifiers but these aren't (so you can have a variable named = or ?, but you can't have a variable named # or 1. And in that case, CL also mixes up "keywords" and identifiers just like everyone else :) .

> Compare to say C where `sometype somebareword` isn't ambiguous because `sometype` is always a language keyword. But wait you say, with typedefs sometype could well be a `typedef stuct somebareword! Yep, it's a problem here too and I've been lying all along.

Even without typedefs, you have a problem more closely related to "select x" VS "select distinct x" - you can have "long x;" or "long double x;".

Either way, yes, I think we agree - there's a difference in quantity and stability of keywords with SQL, but not much else.

I don't know how many are missing but I'm guessing several since I didn't see one: "TYPE" https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywor...

MySQL's SQL is the only language I use where I regularly trip on reserved keywords, super annoying. Not sure off the top of my head if there's some better design to fix that short of namespacing every reserved keyword. In some cases you can do `` to get around it but better to avoid them altogether.

"TYPE" is a non-reserved keyword (hence the lack of an (R) in your link), it is also non-reserved in standard SQL. So it's only a keyword in some contexts, and it's generally available as an identifier.

From the wording of TFA, it probably only lists reserved keywords.

    create table type (
      type integer
    );

    insert into type (type) values (1), (2);

    select type from type;

works perfectly fine in all of sqlite, postgres, mysql, and sql server (at least according to onecompiler.com).

I see. I don’t make a distinction between reserved and keyword I guess as a matter of habit. It seems like asking for trouble.

To use the type example you provided, I’ve seen that example break syntax highlighting and ORMs where you need special quoting to make it work.

Having to quote escape a “timestamp” field at least once a week, I was _extremely_ surprised to see it excluded from this list.

Fair point, looks suspicious on first sight. I've checked my tests briefly: It's accepted as table and column name. As mentioned in footnote [0] I haven't checked different contextes yet.

Can you tell me: what kind of identifier is it (view name, function name) and which SQL context it causes problems (select list, create/drop statement, ...) and which system has problems with it. Thx.

It's fun when these change between versions too. At work we recently upgraded a bunch of servers from MySQL 5.7 to 8.0 (yeah I know, basically last second) and missed a stored procedure that was using "rows" unquoted. Fine in 5.7; reserved as of 8.0.2.

I also find their documentation on this sort of thing to be far too sprawled to be easily accessible, makes it quite hard to prepare for this sort of thing if you're not particularly expert when it comes to database administration.

I'm surprised sqlfluff hasn't been mentioned yet. Perhaps not a comprehensive list, but it's worked for everything I've thrown at it. There's an ANSI keyword list [0], and then dialect-specific lists for everything from DB2 [1] to Snowflake [2].

[0]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/... [1]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/... [2]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/...

Oracle actually provides a database table listing all keywords, along with information about their respective type of reservedness: https://docs.oracle.com/en/database/oracle/oracle-database/1...

ClickHouse has no reserved keywords, e.g.

    SELECT SELECT FROM FROM

is a valid query.

I'm confused how their parser could support that. Isn't `SELECT SELECT FROM FROM foo` ambigous between "get the column 'SELECT' from the table 'FROM' aliased as 'foo'" and "get the column 'SELECT' aliased as 'FROM' from the table 'foo'"?

Unquoted alias without the AS keyword cannot be named FROM.

You can make it even funnier.

  create table from (select UInt8, where UInt8) engine = MergeTree() order by tuple();
  insert into from values (1, 1), (2, 1), (3, 0), (4, 0), (5, 1);
  select select from from where where;

select select from from where where is a really lovely query.

What's the most useful "dialect" of SQL one should learn? I've only learned SQLite but there are just so many that it's heard to know which one is most in demand.

Honestly, you picked the right one. It's restricted, so what you learn will be widely applicable.

Do yourself a favor and pick up some books about SQL by Joe Celko's SQL Puzzles and Answers. If you follow it, you will learn how to accomplish various queries while having restrictions on dialects or whatever. It's a real mind-expander. I found myself doing in SQLite things I hadn't thought possible for that set of keywords.

SQLite is a really good one because - at least for SELECT features - it feels to me like a very clean subset that works across most other databases.

I'd recommend digging into PostgreSQL as well, since it's "larger" than SQLite and will expose you to a bunch more concepts.

If you're familiar with both SQLite and PostgreSQL you should find other dialects very easy to pick up when you need them.

This might reveal my technical mediocrity, but my approach:

1. Write the SQL that I think should mostly work

2. Try to run it

3. Fix errors with the help of docs, Stack Overflow, and now generative AI

4. Subconsciously learn whatever dialect this is to improve my performance on Step 1

This might work in the short term, but I don't recommend it as a general technique.

This sort of an approach often causes there to be hidden bugs, which do not generate errors now, but will cause some problem down the line.

It also has a tendency to lead to cargo cult programming (https://en.wikipedia.org/wiki/Cargo_cult_programming), which is less immediately problematic, but is still not great, especially for whoever needs to maintain that code.

Any one in detail, but be aware of the sort of differences found in the others.

Which one should be your core one depends on what projects you wish to work on. My DayJob is an MS shop to SQL Server's TSQL is my area of expertise, but I know more-or-less what isn't supported or is handled differently in other common places (core postgres, mysql/mariadb, sqlite). In some places you may end up being more completely fluent in multiple rather than just one. The key is understanding the concepts (set based operations rather than thinking procedurally, recursive queries, window functions, how query planners commonly work so you can optimise for them) rather than specific syntax which is always easy to lookup.

The short answer is to write ANSI 92 SQL and understand the variances between different RDBMS based on which ones you encounter.

The longer answer is that some RBDMS adhere closer to the standard than others. Generally the more open source and more long lived a platform is, the closer to the standard it can be. However, even an RDBMS like MSSqlServer isn't that far off from it, and while it may have things it supports that are outside of that standard, it will still support ANSI SQL (i.e. `ISNULL` vs `COALESCE`)

If you're looking for learning SQL that you can likely use in a wide number of places, I'd steer away from Oracle and DB2. Both are fairly proprietary, in my experience, and feel like writing in a different language that looks like SQL, but has a different set of rules and constraints.

SQLite isn't a bad one to know at all. It's pretty close to PostgreSQL and PostgreSQL prides itself as caring about the SQL standard.

I do think that, as general learning experience, working with PostgreSQL is a good starting place because of the good degree of SQL standard compliance. Get those basics down and the less standards compliant vendors become more accessible.

After that it depends what kinda of companies you'd want to work for. Enterprises deal much in MSSQL and Oracle. Start-uppy kinds of companies you're looking at PostgreSQL or MySQL... Or something not RDBMS at all. There are many generalizations that can be made but these are a few hand-wavy examples I would make.

I don't work a ton in SQL, but my experience with SQLite tends to carry me thru anything that requires some SQL without having to commit to fully learning another dialect. I just pick up what I need when they differ. Some sort of IDE works great if it understands the dialects and will red-squiggle anything that's off. (DataGrip works great for me)

The one of the database you use. They all have their idiosyncrasies you’ll have to learn once you actually use them.

One thing to keep in mind: the type system is extremely unusual even in simple cases compared to what I'd expect in a SQL-92 system.

It's clever, giving SQLite a lot of power in limited code, important in its conventional application in embedded use cases where fixed costs like code object size and starting database heap size are pretty constrained, and databases tend to be small...but, nevertheless, it's in its own world, there.

https://www.sqlite.org/datatype3.html

The one that you are currently using is the best one to learn. They're all different and they all work slightly differently. You need to learn generalities, not specifics, until you're working with a specific RDBMS.

SQLite is fine. The biggest issue to be aware of with SQLite is that SQLite's type affinity system is completely different from how other SQL RDBMSs function. The norm is for columns to have much more rigid data typing.

SQLite also ignores size restrictions on text columns; trying to get CHAR(2) to prevent the insertion of ILLINOIS instead of IL requires a trigger. Most other databases do not behave like this.

Other databases all had wonky left join syntax before the SQL92 standard - every attempt should be made to avoid archaic syntax if possible. SQLite itself lacked right join until recently.

Procedural SQL comes in two common varieties - ANSI SQL/PSM (strongly influenced by Oracle), and Transact-SQL (that is only found on Sybase and Microsoft SQL Server). Choose your investment here carefully.

> SQLite also ignores size restrictions on text columns; [...] requires a trigger.

I'd go for CHECK constraints first: https://www.sqlite.org/lang_createtable.html#ckconst

> Procedural SQL [...] Choose your investment here carefully.

I think that the demand for procedual code has dropped drastically in the past decades as the "normal" SQL can solve so many more things with window functions, recursion, and so forth. So I'd say: Yes, choose your investment wisely and stay away from procedural SQL as long as possible.

There is still a case to embed business logic at the database layer, not the application layer, as databases tend not to change as much.

I have thousands of lines of PL/SQL that originated in the days of PowerBuilder that are now serviced by .NET; a decade from now could be totally different.

SQLite appears to use a (very small) subset of PSM for triggers.

Hey, SQL Server CASE should be as reserved. https://learn.microsoft.com/en-us/sql/t-sql/language-element...

Do they have tests for this? was it done manually?

Author of the website here.

All charts on modern-sql.com are backed by tests. I also keep them up-to-date by running them for new releases when they appear.

we xan always rely on you . thank you Markus

This is very nice.

Quoting the SQLite docs:

  The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object.

https://www.sqlite.org/lang_keywords.html

For increased complexity, SQL actually has two kinds of keywords, reserved and non-reserved, non-reserved is what some grammars call contextual or lexical keywords: they are only keywords in specific contexts, but are generally usable as unquoted identifiers.

And obviously this is only for maximally portable SQL, postgres has a comparative table, which demonstrates that many of SQL's reserved keywords are non-reserved in postgres-sql: https://www.postgresql.org/docs/current/sql-keywords-appendi...

This is exactly why I prefer to use the plural forms for table names, despite the debate.

Curse you - it's an apple collection not a collection of apples!

I loathe plurality in table names because it's hard for ESL developers due to the plethora of irregular pluralities (like the word pluralities) in english. If object/entity names and table names are both singular you can do a lot of stuff on autopilot.

Electronic Sports League?

Anywho, I agree, you see:

"It puts the lotion in the basket"

See? Not a plural in sight!

Sorry ESL here is "English as a Second Language" - or non-native english speakers. Our company has a diverse set of employees and only about 60% of us have English as our native language.

Then enable a spell checker in the editor / ide in which you write your SQL. Not being a native speaker should never be used as an argument against proper naming and wording of code and or documentation. I am not a native English speaker as well.

As pointed out by other replies to your comment. Abbreviations can lead to similar problems even with native speakers.

The most strange word in this short list is "vacuum", what SQL ideas might be expressed with this word?

https://www.sqlite.org/lang_vacuum.html

And https://www.postgresql.org/docs/current/sql-vacuum.html

That "don't use english words" is somehow the recommendation instead of "always quote identifiers" feels a bit ridiculous.

I think it is a joke.

That's like saying "bash specifies a large number of character patterns which may not be used as arguments."

No. It just requires they be quoted.

---

The SQL standard forbids certain "regular identifiers," however it places no such restriction of "delimited identifiers." I.e. use quotes.

Darn. I really wanted to name my Db2 table "END-EXEC".

Reply on news.ycombinator.com