The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Mimicking Enumerated Types

I used to work a lot with MySQL. It had a cool data type called "Enumerated Types".
Example in MySQL:
(myadmin@localhost) [jimk]> CREATE TABLE e (ecol ENUM('Bill', 'Sam', 'Jack')); Query OK, 0 rows affected (0.10 sec) (myadmin@localhost) [jimk]> INSERT INTO e VALUES('Bill'); Query OK, 1 row affected (0.00 sec) (dbadmin@localhost) [jimk]> INSERT INTO e values ('Sue'); ERROR 1265 (01000): Data truncated for column 'ecol' at row 1
How do we do this in Vertica? With a Check Constraint!
Example in Vertica:
dbadmin=> CREATE TABLE e (ecol VARCHAR(10) CONSTRAINT ecol_ck CHECK (ecol IN ('Bill', 'Sam', 'Jack'))); CREATE TABLE dbadmin=> INSERT INTO e VALUES('Bill'); OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO e VALUES ('Sue'); ERROR 7230: Check constraint 'public.e.ecol_ck' (e.ecol = ANY (ARRAY['Bill', 'Sam', 'Jack'])) violation: 'ecol=Sue'
Have fun!
0