Unsigned (non-negative) Integer in PostgreSQL

Last updated on February 5, 2022 A Goodman Loading... Post a comment

In PostgreSQL, you can add a constraint that enforces a column to have only non-negative integers by using the CHECK keyword.


A product can have a price greater or equal to 0 (e.g. a free ebook) but it doesn’t make sense if a product has a negative price (this means a shop owner must pay customers for taking goods from their shop). The statement below creates a table name products whose price cannot be a signed integer:

CREATE TABLE products (
	name VARCHAR(200) NOT NULL,
	price INTEGER CHECK (price >= 0)

If I try to insert a new product with a negative price like this:

INSERT INTO products (name, price)
  ('Blue Book', -2);

I will end up with the following error:

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, Blue Book, -2).
SQL state: 23514

In case you want to add a non-negative constraint to an existing table, you can use the ALTER TABLE statement like so:

ALTER TABLE products
ADD CONSTRAINT unsigned_price
CHECK (price >= 0);

That’s it. Further reading:

You can also check out our database topic page for the latest tutorials and examples.

Notify of
Inline Feedbacks
View all comments

You May Also Like