📋

Description

This error indicates that an identifier used in your SQL statement, such as a table, column, or function name, is a word reserved by PostgreSQL. It typically occurs when creating or modifying database objects, preventing the operation due to a fundamental syntax violation.

💬

Error Message

reserved name

🔍

Known Causes

3 known causes

⚠️

Using PostgreSQL Reserved Keywords

An identifier in your SQL statement is a word that PostgreSQL reserves for its own syntax, functions, or data types, causing a conflict.

⚠️

Conflicting with System Object Names

The chosen name for a database object might accidentally clash with an existing internal PostgreSQL system object, function, or data type.

⚠️

Accidental Keyword Match

A typo or an oversight in naming an identifier inadvertently resulted in using a string that is a PostgreSQL reserved keyword.

🛠️

Solutions

3 solutions available

1. Avoid Using PostgreSQL Reserved Keywords as Identifiers easy

Rename tables, columns, or other database objects to not use PostgreSQL reserved keywords.

1

Identify the object (table, column, function, etc.) that is causing the error. The error message typically indicates the name that is problematic. For instance, if you are trying to create a table named `order`, this error will occur because `ORDER` is a reserved keyword.

text

2

Rename the object to something that does not conflict with PostgreSQL reserved keywords. You can find a comprehensive list of reserved keywords in the PostgreSQL documentation. A common practice is to append a suffix like `_tbl` for tables or `_col` for columns, or use a more descriptive name.

ALTER TABLE order RENAME TO orders;
-- Or for a column:
ALTER TABLE my_table ALTER COLUMN name RENAME TO user_name;

3

If you absolutely must use a reserved keyword as an identifier (which is highly discouraged), you can enclose it in double quotes. This will allow PostgreSQL to treat it as a regular identifier. However, this can lead to confusion and makes queries more verbose as you'll always need to double-quote it.

CREATE TABLE "order" (id serial primary key, "date" timestamp);
INSERT INTO "order" ("date") VALUES (NOW());

2. Review and Correct SQL Statements medium

Examine your SQL queries and DDL statements for any instances of reserved keywords being used incorrectly.

1

Carefully review all `CREATE TABLE`, `ALTER TABLE`, `CREATE FUNCTION`, `CREATE VIEW`, and other DDL statements that are being executed. Pay close attention to the names of tables, columns, functions, and other objects.

text

2

Compare the identifiers used in your SQL statements against the list of PostgreSQL reserved keywords. You can find this list in the official PostgreSQL documentation for your specific version. Common problematic keywords include `SELECT`, `FROM`, `WHERE`, `ORDER`, `GROUP`, `TABLE`, `COLUMN`, `DATE`, `TIME`, `CONSTRAINT`, `PRIMARY`, `FOREIGN`, `KEY`, etc.

text

3

Modify the SQL statements to use valid identifiers. This typically involves renaming the problematic object as described in the previous solution, or if the keyword is being used in a context where it's not an identifier (e.g., a string literal), ensure it's correctly quoted.

text

3. Use a Database Schema/Extension for Reserved Names medium

Leverage schemas to namespace your objects, avoiding conflicts with reserved keywords at the database level.

1

Create a new schema to house your custom tables and objects. This is particularly useful if you are working with a database that might have pre-existing objects or if you want to organize your database logically.

CREATE SCHEMA my_schema;
-- Or to set a default search path:
ALTER ROLE current_user SET search_path = my_schema, public;

2

When creating objects within this schema, explicitly qualify their names with the schema name, or ensure your `search_path` is set correctly. This prevents your custom names from clashing with PostgreSQL's internal reserved names or names in other schemas.

CREATE TABLE my_schema.order (id serial primary key, item_name text);
-- If search_path is set correctly, you can omit the schema name:
CREATE TABLE order (id serial primary key, item_name text);

3

When querying objects in this schema, ensure the schema is included in your `search_path` or explicitly qualify the object name.

SELECT * FROM my_schema.order;
-- Or if search_path is set:
SELECT * FROM order;

🔗

Related Errors

5 related errors

PostgreSQL Error 42939: Reserved Name Syntax Error