Mimicking Enumerated Types

Jim_Knicely · September 2018

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:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htm

Have fun!