duplicate key value violates unique constraint on type/function

This error occurs when you try to create a type, function, or other database object that already exists.

The already exists error for types and functions means the object name is taken.

Understanding the Error

ERROR: type "user_status" already exists

Or:

ERROR: function "calculate_total" already exists with same argument types

Common Causes

1. Creating Type Twice

SQL

CREATE TYPE user_status AS ENUM ('active', 'inactive');
CREATE TYPE user_status AS ENUM ('active', 'inactive');  -- Error!

2. Creating Function Twice

SQL

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
  SELECT a + b;
$$ LANGUAGE SQL;

-- Same function again
CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
  SELECT a + b;
$$ LANGUAGE SQL;  -- Error!

3. Migration Run Multiple Times

Schema migration script executed repeatedly.

How to Fix It

Solution 1: Use CREATE OR REPLACE for Functions

SQL

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
  SELECT a + b;
$$ LANGUAGE SQL;

Solution 2: Drop Before Create for Types

SQL

DROP TYPE IF EXISTS user_status;
CREATE TYPE user_status AS ENUM ('active', 'inactive');

Solution 3: Check Before Creating

SQL

-- Check if type exists
SELECT EXISTS (
  SELECT FROM pg_type WHERE typname = 'user_status'
);

-- Check if function exists
SELECT EXISTS (
  SELECT FROM pg_proc WHERE proname = 'add_numbers'
);

Solution 4: Use DO Block for Conditional Creation

SQL

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_status') THEN
    CREATE TYPE user_status AS ENUM ('active', 'inactive');
  END IF;
END $$;

Solution 5: Alter Existing Type

SQL

-- Add value to existing enum
ALTER TYPE user_status ADD VALUE 'pending';

-- Note: Cannot remove values from enum

Solution 6: Different Function Signature

SQL

-- Functions with different parameters are different
CREATE FUNCTION calculate(a INT) RETURNS INT AS $$ SELECT a * 2; $$ LANGUAGE SQL;
CREATE FUNCTION calculate(a INT, b INT) RETURNS INT AS $$ SELECT a + b; $$ LANGUAGE SQL;
-- Both can exist (function overloading)

Listing Existing Objects

SQL

-- List all custom types
SELECT typname FROM pg_type
WHERE typnamespace = 'public'::regnamespace
AND typtype = 'e';  -- 'e' = enum

-- List all functions
SELECT proname, pg_get_function_arguments(oid) as args
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace;

Best Practices

Use CREATE OR REPLACE for functions
Check existence before creating types
Use migrations that track what's been applied
Drop carefully - types can't be dropped if used
Version your objects if major changes needed