Postagens

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
Postar um comentário
Leia mais

Postgres - Using table as a type

Create table address, which will be used as a type: CREATE TABLE address (state STATE, city VARCHAR, street VARCHAR, zipcode VARCHAR) WITH (OIDS=FALSE); Create the table which will use the type address: CREATE TABLE person (name VARCHAR, address ADDRESS); INSERT: INSERT INTO person VALUES ('John Doe', ROW('AZ','PHOENIX','300 BOYLSTON AVE E','98102')); INSERT INTO person (name, address.state, address.city, address.street, address.zipcode) VALUES ('John Doe','AZ','PHOENIX','300 BOYLSTON AVE E','98102'); SELECT: SELECT name, address FROM person; John Doe|(AZ,PHOENIX,300 BOYLSTON AVE E,98102) SELECT name, (address).state, (address).city, (address).street, (address).zipcode FROM person; John Doe|AZ|PHOENIX|300 BOYLSTON AVE E|98102 UPDATE: UPDATE person SET address.street='302 BOYLSTON AVE E' WHERE (address).zipcode='98102'; UPDATE person SET address=ROW('AZ...
Postar um comentário
Leia mais