PostgreSQL cannot find the data type you referenced in your query or CREATE statement. This error occurs when a custom type, enum, domain, or composite type is undefined, misspelled, in a different schema, or hasn't been created yet. Error code 42704 (undefined_object).
The "Type does not exist" error (SQLSTATE 42704) means PostgreSQL cannot find a data type referenced in your query. This happens when you reference a custom type, enum, domain, or composite type that either doesn't exist in your database, is defined in a different schema not included in your search_path, or is misspelled. PostgreSQL checks type existence at parse time for CREATE statements and at execution time for DML operations.
Check that the type is actually created and visible in your current schema.
-- List all user-defined types
\dT
-- List types in a specific schema
SELECT typname FROM pg_type WHERE typnamespace = 'public'::regnamespace;
-- Find your specific type
SELECT * FROM pg_catalog.pg_type WHERE typname = 'your_type_name';If the type doesn't appear, you need to create it.
PostgreSQL identifiers are case-sensitive. Verify that the type name matches exactly, including case.
-- Incorrect: PostgreSQL is case-sensitive
CREATE TABLE users (status MY_STATUS); -- ERROR if type is my_status
-- Correct: match the exact case
CREATE TABLE users (status my_status);
-- Or use double quotes for mixed case
CREATE TABLE users (status "MyStatus");
-- Check what types are available
\dT my_* -- Shows types starting with my_If the type is in a different schema, use the fully qualified name (schema.typename).
-- If your type is in the myschema schema
CREATE TABLE users (status myschema.my_status);
-- Or add the schema to your search path
SET search_path TO myschema, public;
CREATE TABLE users (status my_status); -- Now it finds it
-- View current search_path
SHOW search_path;If the type doesn't exist, create it before using it. Common type creations:
-- Create an ENUM type
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending');
-- Create a DOMAIN (constrained type)
CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');
-- Create a COMPOSITE type (record-like)
CREATE TYPE address AS (
street VARCHAR(100),
city VARCHAR(50),
zip_code VARCHAR(10)
);
-- Now use the type
CREATE TABLE users (
id SERIAL PRIMARY KEY,
status user_status,
email email,
home_address address
);When creating types in migrations or deployment scripts, be explicit about schemas to avoid path issues.
-- Explicitly specify schema during type creation
CREATE TYPE public.my_type AS ENUM ('value1', 'value2');
-- In an extension or schema-specific context, use IF NOT EXISTS when possible
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_type') THEN
CREATE TYPE public.my_type AS ENUM ('value1', 'value2');
END IF;
END$$;
-- Set search path at the beginning of your migration
SET search_path TO public, myschema;
CREATE TABLE ... -- Now types in public and myschema are accessibleIf you're migrating from another database (MySQL, SQLite), some type names don't exist in PostgreSQL.
-- Common migrations needed:
-- STRING -> VARCHAR(n) or TEXT
-- NUMBER -> NUMERIC, INTEGER, or DECIMAL
-- SERIAL -> Use PostgreSQL SERIAL type
-- DATETIME -> TIMESTAMP or TIMESTAMPTZ
-- Incorrect: STRING type doesn't exist in PostgreSQL
CREATE TABLE users (name STRING); -- ERROR
-- Correct: use PostgreSQL types
CREATE TABLE users (name VARCHAR(255));For complex scenarios: If using types in stored procedures or functions, remember that type resolution happens at function creation time for some contexts but at execution time for others. For custom types defined in different schemas within extensions, use CREATE EXTENSION ... WITH SCHEMA public to put them in an accessible location. When working with inherited types or arrays of custom types, ensure the base type exists first. For performance-critical type lookups, consider using the pg_catalog tables directly: SELECT * FROM pg_type WHERE typname = 'your_type' AND typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');. The "type does not exist" error can also happen at deployment time if your migration scripts run in the wrong order—use transaction blocks or explicit ordering to ensure type definitions precede their usage.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL