Prevent less than zero values in postgresql

Question

I am wondering in postgresql if it is possible to prevent values which are less than zero from being entered in the table.

In my example I have a stock table, which everytime an item is bought the stock is minused by one using a java application, however once it gets to zero I want it to not allow entry of values.

I know I can do this inside the java application I have made, but is it possible in postgres table itself, so when any negative numbers are entered below zero it doesn't accept the value?

I would like a method for which I can alter the table to add the constraints as I already have the table created called stock_availability and a stock_quantity column for which I want to apply the constraints of it not being less than zero to, I would prefer not to delete this table and re create it

Answer 1

Use a check constraint:

create table stock_availability
(
   stock_quantity integer not null,
   constraint stock_nonnegative check (stock_quantity >= 0)
);

To add this to an existing table, use ALTER TABLE

alter table stock_availability
   add constraint stock_nonnegative check (stock_quantity >= 0);