PostgreSQL 42939 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42939: Reserved Name

PostgreSQL error code 42939 (reserved_name) occurs when a user attempts to create a database object — such as a type, function, or schema — using a name that PostgreSQL has reserved for internal system use. These reserved names are protected to ensure the stability and consistency of the database engine's core functionality.

---

Top 3 Causes

1. Using a Built-in Type Name for a Custom Type

PostgreSQL reserves names like record, array, trigger, and all built-in data type names. Attempting to create a custom type with these names triggers error 42939.

-- Bad: 'record' is a reserved internal type name
CREATE TYPE record AS (
    id INTEGER,
    label TEXT
);
-- ERROR:  42939: type name "record" is reserved

-- Good: use a descriptive, application-specific name
CREATE TYPE app_record AS (
    id INTEGER,
    label TEXT
);

2. Redefining a Reserved System Function Name

System function names such as now, user, and current_date are reserved by PostgreSQL and cannot be used as user-defined function names without conflict.

-- Bad: 'now' is a reserved system function
CREATE OR REPLACE FUNCTION now()
RETURNS TIMESTAMP AS $$
BEGIN
    RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;
-- ERROR: 42939: function name "now" is reserved

-- Good: use a meaningful custom name
CREATE OR REPLACE FUNCTION get_server_timestamp()
RETURNS TIMESTAMP AS $$
BEGIN
    RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

3. Creating Objects with the pg_ Prefix

PostgreSQL internally reserves all names beginning with pg_ for system catalog objects. Creating user objects with this prefix will raise an error or warning.

-- Bad: pg_ prefix is reserved for system use
CREATE TABLE pg_audit_log (
    id SERIAL PRIMARY KEY,
    action TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ERROR or WARNING: 42939: unacceptable table name "pg_audit_log"

-- Good: use an application-specific prefix or schema
CREATE SCHEMA audit;

CREATE TABLE audit.log (
    id SERIAL PRIMARY KEY,
    action TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

---

Quick Fix Solutions

1. Rename the conflicting object using an application-specific prefix or suffix.
2. Use a dedicated schema to namespace your objects and avoid global name collisions.
3. Check reserved keywords before DDL execution using the built-in function:

-- Check if a word is reserved in PostgreSQL
SELECT word, catcode, catdesc
FROM pg_get_keywords()
WHERE word = 'your_keyword_here';

-- Check if a type name is already taken by the system
SELECT typname, typtype
FROM pg_catalog.pg_type
WHERE typname = 'your_type_name';

---

Prevention Tips

• Adopt a strict naming convention: Prefix all custom objects with an application identifier (e.g., app_, svc_, fn_). This virtually eliminates conflicts with PostgreSQL reserved names and makes objects instantly identifiable as user-defined.

-- Recommended naming pattern
CREATE TYPE app_user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE FUNCTION app_get_active_users() RETURNS SETOF app_users AS $$ ... $$ LANGUAGE plpgsql;

• Isolate all user objects in a dedicated schema: Never create objects directly in public or any system schema. Enforce this through role-level permissions so developers cannot accidentally pollute reserved namespaces.

-- Set up a safe application schema
CREATE SCHEMA myapp;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA myapp TO myapp_admin;

By following these practices, you can avoid 42939 errors entirely and maintain a clean, conflict-free PostgreSQL environment.

---

📖 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.