Mimicking Enumerated Types: Quick Tip

Posted September 6, 2018 by Jim Knicely, Big Data Solutions Architect, Vertica

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' Helpful link:

Have fun!