PostgreSQL 42704: undefined object
A named object that isn't a table or column doesn't exist: a type, role, index, constraint, extension, collation or configuration parameter.
- SQLSTATE
42704- Name
undefined_object
What it means
42704 (undefined_object) is PostgreSQL's code for a missing object other than relations (42P01), columns (42703), functions (42883) and schemas (3F000). The message says which kind: type "..." does not exist, role "..." does not exist, index "..." does not exist, constraint "..." of relation "..." does not exist, extension "..." does not exist, unrecognized configuration parameter "...".
The usual causes are a typo, an object in another schema or database, or an extension that defines the type and isn't installed.
Common causes
- A typo in a type name, or a type from an extension that isn't installed in this database (
citext,hstore,geometry). - A role referenced in
GRANT,ALTER ... OWNER TOorSET ROLEthat hasn't been created on this server, common when restoring a dump from another server. - Dropping an index or constraint by a name that differs from the one PostgreSQL generated.
- Setting a configuration parameter that doesn't exist in this version, or an extension's parameter before the extension is loaded.
How to fix it
-
Install the extension that provides the type.
CREATE EXTENSION IF NOT EXISTS citext;
-
Create missing roles before restoring a dump, or restore without ownership and privileges (
pg_restore --no-owner --no-privileges).CREATE ROLE reporting NOLOGIN;
-
Look up the real name of an index or constraint.
SELECT conname FROM pg_constraint WHERE conrelid = 'orders'::regclass; SELECT indexname FROM pg_indexes WHERE tablename = 'orders';
-
Use
IF EXISTSin cleanup scripts.DROP INDEX IF EXISTS orders_old_idx; ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_old_check;
Reproduce it safely
SELECT '{}'::jsonbb; -- ERROR: type "jsonbb" does not exist
Related errors
42P01relation does not exist42883function does not exist3F000schema does not exist58P01undefined file42710duplicate object
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42703column does not exist
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.