The reserved name error occurs when using PostgreSQL reserved keywords as column or table names without proper quoting. PostgreSQL distinguishes between reserved and non-reserved keywords, and reserved words cannot be used as identifiers unless wrapped in double quotes.
In PostgreSQL, certain SQL keywords are classified as "reserved" because they have special meaning in the SQL grammar. These reserved keywords—such as SELECT, FROM, WHERE, ORDER, OFFSET, USER, and many others—cannot be used directly as identifiers (table names, column names, alias names, etc.) without proper quoting. When you attempt to create a table or column using a reserved keyword as its name without double quotes, PostgreSQL's parser treats the keyword as a SQL command rather than an identifier, resulting in a syntax error. This error typically occurs during table creation (CREATE TABLE), column definition (ALTER TABLE), or migrations when someone uses a reserved word without realizing its special status in PostgreSQL. The error is often presented as a parse error or syntax error at or near the reserved keyword.
When you see a syntax error at or near a word, that word is likely reserved in PostgreSQL. Common reserved keywords that cause issues include:
- OFFSET, LIMIT, ORDER, GROUP, WHERE, FROM, SELECT, ALL
- USER, ROLE, DATABASE, SCHEMA, TABLE, COLUMN
- CONSTRAINT, CHECK, PRIMARY, FOREIGN, UNIQUE, INDEX
- CASE, END, WHEN, THEN, ELSE
- AND, OR, NOT, EXISTS, IN
Check the PostgreSQL documentation for the complete list of reserved keywords:
https://www.postgresql.org/docs/current/sql-keywords-appendix.html
Alternatively, query your PostgreSQL database directly:
SELECT * FROM pg_get_keywords() WHERE catdesc = 'reserved' ORDER BY word;This will show you all reserved keywords for your PostgreSQL version.
To use a reserved keyword as an identifier, wrap it in double quotes. This tells PostgreSQL to treat it as an identifier name, not as a keyword.
Example - Creating a table with reserved keyword columns:
-- Wrong (will fail):
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
order TIMESTAMP,
limit INTEGER
);
-- Correct (with double quotes):
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
"order" TIMESTAMP,
"limit" INTEGER
);When querying the table, you must also use double quotes:
SELECT "order", "limit" FROM orders;If you have existing CREATE TABLE statements or migrations with unquoted reserved keywords, update them:
-- If table already created without quotes and you can't drop it:
ALTER TABLE users RENAME COLUMN "user" TO username;
-- Or create a new table with proper quoting:
CREATE TABLE users_new (
id INTEGER PRIMARY KEY,
"user" VARCHAR(255),
"order" INTEGER
);
-- Copy data and rename
INSERT INTO users_new SELECT * FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;If using an ORM like Prisma, Sequelize, or TypeORM, ensure it's configured to properly quote reserved keywords:
Prisma example:
model User {
id Int @id @default(autoincrement())
order DateTime // Prisma will automatically quote reserved keywords
limit Int
}For custom SQL or query builders, use parameterized queries with proper escaping:
// Node.js with pg library
const query = 'SELECT * FROM users WHERE "order" > $1';
await client.query(query, [someValue]);The safest approach is to avoid using reserved keywords as identifiers altogether. Rename your columns to non-reserved names:
-- Avoid this:
CREATE TABLE orders (
id INTEGER,
order TIMESTAMP
);
-- Prefer this:
CREATE TABLE orders (
id INTEGER,
order_date TIMESTAMP
);
-- Similarly:
-- user -> username or user_name
-- limit -> max_results or row_limit
-- offset -> skip_count or start_position
-- group -> group_name or departmentThis eliminates the need for quoting and makes code more readable across all database systems.
Case Sensitivity with Quotes: When you use double quotes, identifiers become case-sensitive. "User" (with capital U) is different from "user" (lowercase). Without quotes, PostgreSQL converts all identifiers to lowercase by default.
PostgreSQL vs Other Databases: MySQL allows reserved keywords as identifiers without quoting, which is why code migrated from MySQL often has this issue. PostgreSQL is stricter about reserved keyword usage, which promotes better code standards.
Schema and Table Qualification: You can use quoted keywords in qualified identifiers too: SELECT * FROM "public"."order" WHERE "user" = $1
Function and Procedure Names: Reserved keywords in function definitions also require quoting: CREATE FUNCTION "select"() RETURNS void AS ...
PL/pgSQL Variables: Within PL/pgSQL code, variable names that shadow reserved keywords should also be quoted when referenced in SQL contexts.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL