How to fix Type "typename" does not exist in PostgreSQL

-- 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';

-- 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 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;

-- 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
);

-- 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 accessible

-- 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));