How to Fix PostgreSQL Error: Type Does Not Exist

The "Type does not exist" error occurs when referencing a data type that hasn't been defined, commonly custom types, enums, or types from uninstalled extensions.

Impact

Prevents table creation, alterations, or queries that reference the missing type. Common during migrations or when using custom data types.

Common Causes

1. Custom type not created
2. ENUM type not defined
3. Extension providing type not installed
4. Type in different schema
5. Typo in type name

Troubleshooting and Resolution Steps

1. List existing types:

   -- List all types
   \dT
   
   -- Search for specific type
   \dT+ status_enum
   
   -- Query system catalog
   SELECT
       n.nspname AS schema,
       t.typname AS type_name,
       t.typtype AS type
   FROM pg_type t
   JOIN pg_namespace n ON t.typnamespace = n.oid
   WHERE t.typname LIKE '%status%';
   

2. Create ENUM type:

   -- Create ENUM
   CREATE TYPE order_status AS ENUM (
       'pending',
       'processing',
       'shipped',
       'delivered',
       'cancelled'
   );
   
   -- Use in table
   CREATE TABLE orders (
       id SERIAL PRIMARY KEY,
       status order_status DEFAULT 'pending'
   );
   
   -- Add value to ENUM
   ALTER TYPE order_status ADD VALUE 'returned';
   

3. Create composite type:

   -- Create composite type
   CREATE TYPE address AS (
       street VARCHAR(255),
       city VARCHAR(100),
       state VARCHAR(2),
       zip VARCHAR(10)
   );
   
   -- Use in table
   CREATE TABLE customers (
       id SERIAL PRIMARY KEY,
       name VARCHAR(255),
       shipping_address address
   );
   

4. Install extension for type:

   -- UUID type
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   
   -- hstore type
   CREATE EXTENSION IF NOT EXISTS hstore;
   
   -- PostGIS geometry types
   CREATE EXTENSION IF NOT EXISTS postgis;
   

5. Use schema-qualified type name:

   -- If type is in different schema
   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       status myschema.status_type
   );
   

Additional Information

• ENUM types are useful for predefined value sets
• Composite types group related fields
• Domain types add constraints to base types
• Types are schema-specific
• Changing ENUM types can be tricky in production

Frequently Asked Questions

Q: How do I modify an ENUM type?
A: You can ADD values but cannot REMOVE or RENAME. For major changes, create new type and migrate.

Q: Should I use ENUM or CHECK constraint?
A: ENUM for rarely-changing fixed sets. CHECK constraint for more flexibility.

Q: Can I drop a type that's in use?
A: No, must drop all dependent objects first or use CASCADE (which drops dependent objects).