Backticks Around Names Work on MySQL and SQLite and Fail on PostgreSQL
MySQL examples often quote every name in backticks, as in SELECT `name` FROM `people`. Copied into a PostgreSQL project, the query fails.
`name` is MySQL’s way of quoting a name, and SQLite and MariaDB accept it too. PostgreSQL reads the backtick as a syntax error.
What Each Engine Answered
On a table made with
CREATE TABLE people (name VARCHAR(20));
INSERT INTO people VALUES ('Ann');
the example
SELECT `name` FROM people;
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 'Ann' |
| PostgreSQL 16.15 | error: syntax error at or near "FROM" |
| MySQL 8.0.46 | 'Ann' |
| MariaDB 10.11.14 | 'Ann' |
What to Write Instead
This form answers the same on every engine:
SELECT name FROM people;
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 'Ann' |
| PostgreSQL 16.15 | 'Ann' |
| MySQL 8.0.46 | 'Ann' |
| MariaDB 10.11.14 | 'Ann' |
Use plain lower-case names that are not keywords; they need no quotes on any engine.
What BareSQL Portable Reports
Saved as example.sql, the example gets this from baresql lint --all:
$ baresql lint --all example.sql
example.sql:3:8: error: Backticks quote names on SQLite, MySQL and MariaDB; PostgreSQL refuses them. [backticks]
`name`
Use plain lower-case names that are not keywords; they need no quotes on any engine.
1 problem: 1 error.
baresql rules backticks prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.