Column Names Like rank and key Are Reserved Words on Some SQL Engines

A leaderboard table with a column called rank works on SQLite, PostgreSQL and MariaDB. On MySQL 8 the CREATE TABLE fails, because rank became a reserved word when window functions arrived. BareSQL Portable knows 83 names like these.

Every engine keeps a list of words that can’t be names without quotes, and the lists differ. PostgreSQL reserves user, only and array; MySQL and MariaDB reserve key, index, range, rows and many more, and MySQL 8 added rank, groups, row and other words of window functions and grouping. SQLite reserves the fewest. The table lists words people often give to tables and columns, each tried as a column name on all four engines. As a column label after AS, PostgreSQL takes any word, so there only MySQL, MariaDB and SQLite matter.

CREATE TABLE t (id INTEGER, rank INTEGER), run on four engines: SQLite 3.45.1 no rows; PostgreSQL 16.15 no rows; MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'rank INTEGER)' at line 1; MariaDB 10.11.14 no rows.

What Each Engine Answered

The example

CREATE TABLE t (id INTEGER, rank INTEGER);

gave these answers:

Engine	Answer
SQLite 3.45.1	no rows
PostgreSQL 16.15	no rows
MySQL 8.0.46	error: `You have an error in your SQL syntax ... near 'rank INTEGER)' at line 1`
MariaDB 10.11.14	no rows

Every Form BareSQL Checks

BareSQL Portable checks 83 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB
`USER`	yes	no	yes	yes
`KEY`	yes	yes	no	no
`KEYS`	yes	yes	no	no
`RANK`	yes	yes	no	yes
`GROUPS`	yes	yes	no	yes
`ROW`	yes	yes	no	yes
`ROWS`	yes	yes	no	no
`RANGE`	yes	yes	no	no
`FUNCTION`	yes	yes	no	yes
`SYSTEM`	yes	yes	no	yes
`SCHEMA`	yes	yes	no	yes
`DATABASE`	yes	yes	no	yes
`CHANGE`	yes	yes	no	no
`CONDITION`	yes	yes	no	no
`DESCRIBE`	yes	yes	no	no
`DIV`	yes	yes	no	no
`MOD`	yes	yes	no	no
`DUAL`	yes	yes	no	no
`INTERVAL`	yes	yes	no	no
`READ`	yes	yes	no	no
`WRITE`	yes	yes	no	no
`RELEASE`	yes	yes	no	no
`RETURN`	yes	yes	no	no
`USAGE`	yes	yes	no	no
`SIGNAL`	yes	yes	no	no
`OPTION`	yes	yes	no	yes
`LOCK`	yes	yes	no	no
`MATCH`	yes	yes	no	no
`RENAME`	yes	yes	no	no
`REPLACE`	yes	yes	no	no
`SQL`	yes	yes	no	no
`OFFSET`	yes	no	yes	no
`WINDOW`	yes	no	no	yes
`LATERAL`	yes	no	no	yes
`RETURNING`	no	no	yes	no
`VALUES`	no	yes	no	no
`INDEX`	no	yes	no	no
`SET`	no	yes	no	no
`END`	yes	no	yes	yes
`ONLY`	yes	no	yes	yes
`ARRAY`	yes	no	yes	yes
`CAST`	yes	no	yes	yes
`FULL`	yes	no	yes	yes
`LEFT`	yes	no	no	no
`RIGHT`	yes	no	no	no
`DESC`	yes	no	no	no
`ASC`	yes	no	no	no
`COLUMN`	yes	no	no	no
`BINARY`	yes	no	no	no
`ANALYZE`	yes	no	no	no
`EMPTY`	yes	yes	no	yes
`VIRTUAL`	yes	yes	no	yes
`STORED`	yes	yes	no	yes
`GENERATED`	yes	yes	no	yes
`LEAD`	yes	yes	no	yes
`LAG`	yes	yes	no	yes
`OF`	yes	yes	no	yes
`OVER`	yes	yes	no	no
`PARTITION`	yes	yes	no	no
`PRECISION`	yes	yes	no	no
`REAL`	yes	yes	no	no
`FLOAT`	yes	yes	no	no
`DOUBLE`	yes	yes	no	no
`TRIGGER`	yes	yes	no	no
`CURSOR`	yes	yes	no	no
`EXIT`	yes	yes	no	no
`LOOP`	yes	yes	no	no
`REPEAT`	yes	yes	no	no
`DO`	yes	no	yes	yes
`CUBE`	yes	yes	no	yes
`GROUPING`	yes	yes	no	yes
`RECURSIVE`	yes	yes	no	no
`SEPARATOR`	yes	yes	no	no
`LINES`	yes	yes	no	no
`LOAD`	yes	yes	no	no
`SHOW`	yes	yes	no	no
`EXPLAIN`	yes	yes	no	no
`BEFORE`	yes	yes	no	no
`EACH`	yes	yes	no	no
`CASCADE`	yes	yes	no	no
`RESTRICT`	yes	yes	no	no
`IGNORE`	yes	yes	no	no
`OUT`	yes	yes	no	no

What to Write Instead

This form answers the same on every engine:

CREATE TABLE t (id INTEGER, rank_no INTEGER);

Engine	Answer
SQLite 3.45.1	no rows
PostgreSQL 16.15	no rows
MySQL 8.0.46	no rows
MariaDB 10.11.14	no rows

Pick another name, such as user_name, rank_no or sort_key. Quoting the name is no portable way out: double quotes name a column on SQLite and PostgreSQL and make a string on MySQL and MariaDB (see Double quotes), and backticks work on SQLite, MySQL and MariaDB only (see backticks).

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:29: error: The name rank works on SQLite, PostgreSQL and MariaDB; MySQL refuses it. [reserved-word]
    rank
    Pick another name, such as user_name, rank_no or sort_key. Quoting the name is no portable way out: double quotes name a column on SQLite and PostgreSQL and make a string on MySQL and MariaDB (see double-quotes), and backticks work on SQLite, MySQL and MariaDB only (see backticks).

1 problem: 1 error.

baresql rules reserved-word prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.