Skip to content

Instantly share code, notes, and snippets.

@zmiftah

zmiftah/postgre_enum_type.sql

Last active January 6, 2017 11:54
Show Gist options
  • Save zmiftah/c5f1b6a74076cc90f94d to your computer and use it in GitHub Desktop.
Save zmiftah/c5f1b6a74076cc90f94d to your computer and use it in GitHub Desktop.
Download ZIP
Postgre | Enum Type
Raw
postgre_enum_type.sql
-- Create Enum
CREATE TYPE enum_type AS ENUM ('Value 1', 'Value 2');
-- List All Enum
SELECT UNNEST(ENUM_RANGE(null::enum_type));
-- Add Value
ALTER TYPE enum_type ADD VALUE 'New Value';
-- Delete Value
SELECT * FROM pg_enum where enumlabel='New Value';
DELETE FROM pg_enum en WHERE en.enumtypid=123 AND en.enumlabel='New Value';
-- List All Enum Tables
SELECT n.nspname as enum_schema,
t.typname as enum_name,
e.enumlabel as enum_value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE n.nspname='public'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment