Creating or querying a table named user fails in PostgreSQL
ERROR: syntax error at or near "user"Short answer
USER is a reserved key word in PostgreSQL, so it cannot be used as an unquoted table or column name. Wrap it in double quotes everywhere, or rename the table to users. Once you quote a name with capital letters, you must quote it exactly the same way in every later query.
Fix it fast
1Find out which cause you have
Run the smallest failing statement by itself in psql. If it fails there, the name is the problem and not your application.
CREATE TABLE user (id integer); CREATE TABLE "user" (id integer);2Match what you see, then jump to its fix
What does the error look like?
ERROR: syntax error at or near "user"ERROR: relation "User" does not exist
Cause 1 of 2
USER is reserved and was written without quotes
1Check
The key words appendix marks USER as reserved, and says reserved words are not allowed as column or table names. Check every place the name appears, including ORM-generated SQL.
grep -rn -i -E "(from|join|into|table|update) +user\b" --include=*.sql --include=*.py --include=*.js .2Fix
Quote the name in every statement, or rename the table to users so no quoting is needed. The appendix recommends quoting when a listed key word gives parser errors.
CREATE TABLE "user" (id integer PRIMARY KEY, email text);
SELECT * FROM "user";
-- or avoid the problem
CREATE TABLE users (id integer PRIMARY KEY, email text);3Verify
The table exists and can be queried.
SELECT count(*) FROM "user";Confirmed by postgresql.org
Cause 2 of 2
The table was created with quotes and capitals, then queried without them
1Check
Quoting makes a name case-sensitive, while unquoted names are folded to lower case. A table created as "User" is not found by User, USER or user.
SELECT table_name FROM information_schema.tables WHERE table_name ILIKE 'user%';2Fix
Use the stored spelling with its quotes, or recreate the table with a lower-case name and never quote it.
SELECT * FROM "User";
-- or recreate it in lower case and never quote it
CREATE TABLE users (id integer PRIMARY KEY, email text);3Verify
The query finds the table.
SELECT count(*) FROM users;Confirmed by postgresql.org
How do I stop it coming back?
- Name tables with plural nouns such as users, accounts and orders, which avoids most reserved words.
- Check a new name against the key words table before you build code around it.
- Quote a name always or never. Mixing both is what causes the case errors.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix