PostgreSQL Reserved Name (SQLSTATE 42939)

When you attempt to use a name that PostgreSQL reserves for its own internal purposes, you will see an error like:

ERROR:  unacceptable schema name "pg_myschema"
DETAIL:  The prefix "pg_" is reserved for system schemas.

The SQLSTATE code is 42939, the condition name is reserved_name, and it belongs to error class 42 — Syntax Error or Access Rule Violation.

What This Error Means

SQLSTATE class 42 covers a broad range of syntax and access rule violations. Within this class, error 42939 specifically signals that a name you provided conflicts with a namespace or naming convention that PostgreSQL has reserved for its own system-level use. The most well-known restriction in this class is the pg_ prefix: any schema, database, or other identifier whose name begins with pg_ is considered off-limits for user-created objects because PostgreSQL uses that prefix for its system catalog schemas (such as pg_catalog and pg_toast).

This error is enforced at parse or execution time, before any object is created. Your transaction remains open and valid after the error — you can continue to issue other statements or roll back as needed. The error does not indicate any data corruption or lock contention; it is purely a naming policy violation.

While the pg_ prefix restriction on schemas is the most common trigger, the same SQLSTATE can in principle be raised for other reserved names that future PostgreSQL versions may protect. The check is intentionally conservative: even if no system schema named pg_myschema exists today, PostgreSQL rejects the name to prevent collisions with names it may need to use internally in the future.

Common Causes

1. Creating a schema with the pg_ prefix. Statements like CREATE SCHEMA pg_app_data or CREATE SCHEMA pg_reporting will fail with 42939. This prefix is reserved exclusively for PostgreSQL system schemas.

2. Restoring or migrating a dump that contains a pg_-prefixed schema. A pg_dump or pg_restore run against a source database that somehow contains such a schema (perhaps created in a non-standard or older PostgreSQL environment) will fail on the target cluster.

3. ORM or migration tool auto-generating schema names. Some frameworks or infrastructure-as-code tools construct schema names programmatically. A bug or misconfiguration can produce a name with the pg_ prefix.

4. Attempting to use other system-reserved identifiers. While less commonly encountered, PostgreSQL documentation notes that additional names may be reserved in specific contexts.

How to Fix reserved_name

1. Rename the schema to one without the pg_ prefix. Choose an application-specific prefix or namespace instead:

   -- Instead of:
   CREATE SCHEMA pg_reporting;
   
   -- Use:
   CREATE SCHEMA app_reporting;
   -- or
   CREATE SCHEMA reporting;
   

2. Fix migration scripts or ORM configuration. Search your migration files and ORM schema configuration for any schema names beginning with pg_ and rename them before running migrations:

   grep -r 'pg_' ./migrations/ --include="*.sql"
   

3. Update pg_dump/pg_restore workflows. If you are migrating data, inspect the dump file for reserved names and rename them before restoring:

   # Search for reserved schema names in a dump file
   grep 'CREATE SCHEMA pg_' mydump.sql
   

   Then either edit the dump file to rename the schema or, preferably, fix the source database before dumping.

4. Use search_path instead of a pg_-prefixed schema. If the intent was to make objects globally accessible, configure search_path to include a properly named schema instead:

   ALTER ROLE myapp SET search_path TO myapp_schema, public;
   

Additional Information

• The pg_ prefix restriction on schemas has been enforced since early versions of PostgreSQL and is documented in the PostgreSQL manual under "Schema Creation."
• Related SQLSTATE codes in class 42 include: 42501 (insufficient_privilege), 42601 (syntax_error), 42701 (duplicate_column), 42710 (duplicate_object), and 42P01 (undefined_table).
• Most PostgreSQL client libraries (libpq, psycopg2, asyncpg, JDBC) surface this as a ProgrammingError or SQLSyntaxError with the SQLSTATE 42939 included in the exception detail.
• This error is non-recoverable for the offending statement but does not abort the surrounding transaction, so you can catch it and attempt a corrected statement within the same transaction block.

Frequently Asked Questions

Why does PostgreSQL reserve the pg_ prefix specifically? PostgreSQL uses the pg_ prefix for all of its built-in system catalog schemas (pg_catalog, pg_toast, pg_temp_N, etc.). Reserving the prefix prevents user-created schemas from shadowing or colliding with current or future system schemas, which could cause unpredictable behavior when PostgreSQL resolves object names during query planning.

Can I override this restriction using superuser privileges? No. The pg_ prefix restriction on schema names is enforced unconditionally regardless of the role's privilege level, including superusers. There is no configuration parameter or override flag to bypass it.

Does this restriction apply to table names or column names, not just schemas? No — for ordinary tables and columns, the pg_ prefix is allowed (though strongly discouraged for clarity). The 42939 error is raised specifically when the restricted name applies to an object type where the reservation is enforced, most notably schemas.

I see this error during pg_restore — how do I fix it without editing the dump file? If you control the source database, rename the offending schema there before dumping: ALTER SCHEMA pg_myschema RENAME TO myschema;, then re-dump. If you only have the dump file, you can use sed to rename it before restoring: sed 's/pg_myschema/myschema/g' mydump.sql > fixed_dump.sql, then restore from fixed_dump.sql. Be careful to replace all occurrences including SET search_path lines.