PostgreSQL 42939 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42939: Reserved Name

PostgreSQL error code 42939 (reserved_name) occurs when a user attempts to create a database object — such as a table, function, type, or operator — using a name that PostgreSQL internally reserves for its own system operations. Unlike simple syntax errors, this error specifically guards names that PostgreSQL considers critical to its internal machinery, and in many cases, even quoting the identifier with double quotes will not bypass the restriction.

Top 3 Causes

1. Using SQL Reserved Keywords as Object Names

The most common trigger is attempting to name a table, column, or schema using a reserved SQL keyword like order, select, table, user, or default.

-- This will fail with error 42939
CREATE TABLE order (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(10,2)
);
-- ERROR:  42939: "order" is a reserved name

-- Fix: use a descriptive, non-reserved name
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(10,2)
);

-- Check if a word is reserved before using it
SELECT word, catdesc
FROM pg_get_keywords()
WHERE word = 'order';

2. Creating Custom Types or Functions with Built-in Names

PostgreSQL protects the names of built-in data types (text, int, boolean) and system functions (now, current_user). Attempting to create a custom type or function with these names will raise 42939.

-- Fails: 'text' is a reserved built-in type name
CREATE TYPE text AS ENUM ('a', 'b', 'c');
-- ERROR:  42939: "text" is a reserved name

-- Fix: use a domain-specific name
CREATE TYPE label_size AS ENUM ('small', 'medium', 'large');

-- Fails: 'now' is a reserved function name
CREATE FUNCTION now() RETURNS TIMESTAMPTZ AS $$
    SELECT CURRENT_TIMESTAMP;
$$ LANGUAGE SQL;
-- ERROR:  42939: "now" is a reserved name

-- Fix: prefix with your application namespace
CREATE FUNCTION app_now() RETURNS TIMESTAMPTZ AS $$
    SELECT CURRENT_TIMESTAMP;
$$ LANGUAGE SQL;

3. Name Collisions During Database Migration or Extension Installation

When migrating from Oracle, MySQL, or other databases, existing object names may clash with PostgreSQL's reserved names. Similarly, installing or upgrading extensions can expose naming conflicts that were not present in earlier PostgreSQL versions.

-- During migration, identify problematic names before creating objects
SELECT word, catdesc
FROM pg_get_keywords()
WHERE word IN ('order', 'user', 'table', 'index', 'value', 'session')
  AND catdesc LIKE '%reserved%';

-- Use schema namespacing to reduce collision risk
CREATE SCHEMA myapp;

CREATE TABLE myapp.user_account (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

-- Set search_path to prioritize your schema
SET search_path TO myapp, public;

Quick Fix Solutions

-- Option 1: Rename the object to avoid the reserved word
-- Bad
CREATE TABLE "select" (id INT);

-- Good
CREATE TABLE selections (id INT);

-- Option 2: Use double quotes (use sparingly — causes maintenance headaches)
CREATE TABLE "order" (id SERIAL PRIMARY KEY);
-- Must always be referenced with double quotes
SELECT * FROM "order";

-- Option 3: Rename an existing conflicting type
ALTER TYPE conflicting_type_name RENAME TO safe_type_name;

-- Option 4: Add application prefix to functions and types
CREATE FUNCTION myapp_current_session() RETURNS TEXT AS $$
    SELECT current_setting('myapp.session_id', true);
$$ LANGUAGE SQL;

Prevention Tips

1. Always validate names against pg_get_keywords() before writing DDL.

Make it a standard step in your database design process to check any planned object name against PostgreSQL's keyword list. Integrate this check into peer code reviews and migration scripts.

-- Quick pre-flight check for any new object name
SELECT word, catdesc
FROM pg_get_keywords()
WHERE word = 'your_planned_name';
-- If catdesc returns 'reserved', choose a different name immediately.

2. Enforce a team-wide naming convention that avoids reserved words.

Adopt conventions such as using plural nouns for tables (orders, users, products), domain prefixes for custom types (app_, biz_), and application namespaces for functions. Integrate a SQL linting tool like sqlfluff into your CI/CD pipeline to automatically catch reserved name violations before they ever reach production.

Related Errors

Error Code	Name	Relationship
`42601`	`syntax_error`	Grammar-level error often confused with 42939
`42P07`	`duplicate_table`	Name conflict when object already exists
`42723`	`duplicate_function`	Function signature conflict during creation
`42704`	`undefined_object`	Raised when referencing a non-existent object by a reserved name

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community