Less Than Constraint for a Column in Vertica

[varuna.bhat]

Hello,

I am migrating some tables from oracle to vertica and i have some less than constraints created on some columns in oracle(for e.g CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,).

How I can add same constraint in vertica while creating the table ?

Thanks-

[JimKnicely]

Hi,

Vertica does not support check constraints. You should try to check the data before you load it into Vertica during the ETL process.

But, there is also a quirky work around you can do with your table. But I wouldn't recommend it in production because it'll degrade performance.

Anyway, you can add an extra column to the table to check the value... Like this:

Code: Select all

dbadmin=> create table jim (c1 int);
CREATE TABLE

dbadmin=> alter table jim add column c2 int default 1 / decode(sign(c1), 1, 1, 0);
ALTER TABLE

dbadmin=> insert into jim (c1) values (1);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> insert into jim (c1) values (0);
ERROR 3117:  Division by zero

dbadmin=> insert into jim (c1) values (-1);
ERROR 3117:  Division by zero

See how I could not insert a 0 or -1?

[varuna.bhat]

Thanks Jim for your reply.