Postgres - Using enum

Create enum states:
CREATE TYPE states AS ENUM('AL','AK','AZZ','AR');

Insert new values:
INSERT INTO pg_enum (enumtypid,enumsortorder,enumlabel) VALUES ((SELECT OID FROM pg_type WHERE typname = 'states'), 5, 'CA');

Update enum:
UPDATE pg_enum set enumlabel = 'AZ' WHERE enumtypid IN (SELECT OID FROM pg_type WHERE typname = 'states') and enumlabel = 'AZZ';

Select:
SELECT
 pe.enumtypid,
 pe.enumsortorder,
 pe.enumlabel
FROM
 pg_type pt
JOIN
 pg_enum pe ON pt.oid = pe.enumtypid
WHERE
 pt.typname = 'states'
ORDER BY 2;

234301|1|AL
234301|2|AK
234301|3|AZ
234301|4|AR
234301|5|CA


Using in a table:
CREATE TABLE address (
 state STATE NOT NULL, <======
 city VARCHAR NOT NULL,
 street VARCHAR NOT NULL,
 zipcode VARCHAR
WITH (OIDS=FALSE);


This examples is valid on Postgres 9.1

Comentários