Postgres - Using enum
Create enum states:
Insert new values:
Update enum:
Select:
Using in a table:
This examples is valid on Postgres 9.1
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
Postar um comentário