PostgreSQL: 'syntax error at or near "user"' with a reserved word (42601)

ERROR: syntax error at or near "user" (SQLSTATE 42601) on a CREATE TABLE means you used a reserved word, here user, as a table or column name. Rename it (app_users, order_no) or wrap it in double quotes everywhere you use it. Renaming is the fix you will not regret.

Quoting works, but every query must then write "user" with the quotes, and a query that forgets them does not always fail: it can silently return something else.

The error

CREATE TABLE user (id integer PRIMARY KEY, name text NOT NULL);
psql:/tmp/q4.sql:2: ERROR:  syntax error at or near "user"
LINE 1: CREATE TABLE user (id integer PRIMARY KEY, name text NOT NUL...
                     ^

The same with a column called order:

CREATE TABLE purchases (id integer PRIMARY KEY, order integer NOT NULL);
psql:/tmp/q4.sql:4: ERROR:  syntax error at or near "order"
LINE 1: CREATE TABLE purchases (id integer PRIMARY KEY, order intege...
                                                        ^

Why it happens

Some SQL keywords are reserved: the parser reads them as part of the grammar, so an unquoted user where a table name should be is a syntax error. USER is a built-in function that returns the current user, and ORDER starts ORDER BY. PostgreSQL lists its keywords in pg_get_keywords(), and on this server it reported:

SELECT word, catdesc FROM pg_get_keywords() WHERE word IN ('user', 'order', 'name', 'type', 'value') ORDER BY word;
 word  |  catdesc
-------+------------
 name  | unreserved
 order | reserved
 type  | unreserved
 user  | reserved
 value | unreserved
(5 rows)

So name, type and value are fine as column names (CREATE TABLE kw_test (name text, type text, value text) succeeded), while user and order are not. The caret in the error points at the word the parser could not accept, which is the name you need to change.

The fix

Rename, which keeps every later query simple:

CREATE TABLE app_users (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE
CREATE TABLE purchases (id integer PRIMARY KEY, order_no integer NOT NULL);
CREATE TABLE

Or quote the name, and keep quoting it:

CREATE TABLE "user" (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE
INSERT INTO "user" VALUES (1, 'Maria Garcia');
INSERT 0 1
SELECT id, name FROM "user";
 id |     name
----+--------------
  1 | Maria Garcia
(1 row)

Here is the trap. With the table "user" in place, the unquoted query SELECT * FROM user; did not fail; it returned one row with the column user and the value postgres, the name of the connected role, not the table's data. That is why renaming is the better fix. From .NET, Npgsql reports the syntax error as a PostgresException with SqlState 42601.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2. The two CREATE TABLE statements failed as shown, the quoted and the renamed versions succeeded, and the unquoted SELECT * FROM user returned the role name. Statements ran through psql -a -f inside the container; a verbose run printed ERROR: 42601.

Frequently asked

Can I name a PostgreSQL table user?

Only if you write it in double quotes, "user", in every statement. Without quotes user is a reserved word and CREATE TABLE user fails with a syntax error; renaming to app_users avoids the problem.

Why does SELECT * FROM user return postgres instead of my table?

Unquoted, user is the built-in that returns the current role name, so the query returns one row with your login name. Write SELECT * FROM "user" or rename the table.

How do I check whether a word is reserved in PostgreSQL?

Query SELECT word, catdesc FROM pg_get_keywords() WHERE word = 'yourword'. A catdesc that starts with reserved means it cannot be used as a table or column name without double quotes.

More decoded errors in the Fixes category. For naming rules that avoid this and the case-folding traps, see naming conventions in Database Design for Developers.