The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Mimicking Enumerated Types

I used to work a lot with MySQL. It had a cool data type called "Enumerated Types".

Example in MySQL:

([email protected]) [jimk]> CREATE TABLE e (ecol ENUM('Bill', 'Sam', 'Jack'));
Query OK, 0 rows affected (0.10 sec)

([email protected]) [jimk]> INSERT INTO e VALUES('Bill');
Query OK, 1 row affected (0.00 sec) 

([email protected]) [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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.