Mimicking Enumerated Types

Jim_KnicelyJim_Knicely - Select Field - Administrator

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')));

dbadmin=> INSERT INTO e VALUES('Bill');
(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'

Helpful link:

Have fun!

Sign In or Register to comment.