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

Solution

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);

Comments

[John]

How do I do it if I already have the table created, i have a table called stock_availability and a stock_quantity column which I want the constraint applied to, how do I update this?

[John]

sorry, I will from now on, by the way there is no need for the bracket before the check it should be just - check (stock_quantity > 0)

[John]

Also another issue, stock values should be able to equal zero, but not below zero, therefore it should be stock_quantity >= 0

[Craig Ringer]

@John To learn more, see postgresql.org/docs/current/static/ddl-constraints.html