Unsigned (non-negative) Integer in PostgreSQL

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

Example

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 (
	id SERIAL PRIMARY KEY,
	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)
VALUES 
  ('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:

• 2 Ways to Rename a Table in PostgreSQL
• 2 Ways to Get the Size of a Database in PostgreSQL
• How to select a Database to use in PostgreSQL
• PostgreSQL: Create Tables with Auto-Increment IDs
• Best open-source ORM and ODM libraries for Node.js
• Node + Mongoose + TypeScript: Defining Schemas and Models

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