Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Creating or querying a table named user fails in PostgreSQL

ErrorERROR: 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.

  • Applies to PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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);
  2. 2Match what you see, then jump to its fix

    1. 1syntax error at or near "user" on CREATE TABLE, INSERT, SELECT or JOINUSER is reserved and was written without quotesFix
    2. 2relation "User" does not exist after a successful CREATE TABLE "User"The table was created with quotes and capitals, then queried without themFix

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.