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

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.