Working with Non-Negative Integers in PostgreSQL

While PostgreSQL doesn't have built-in unsigned integer types, it offers several signed integer data types that you can use to store non-negative integers. These include:

• smallint: Stores integers between -32,768 and 32,767
• integer: Stores integers between -2,147,483,648 and 2,147,483,647
• bigint: Stores integers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  stock_count INTEGER NOT NULL CHECK (stock_count >= 0)
);

This code creates a table named products with two columns:

• product_id: This is a serial primary key, which automatically generates a unique integer for each product.
• stock_count: This is an integer column that stores the number of items in stock for a product. The CHECK constraint enforces that the stock_count can only be positive or zero.

Using a DOMAIN for a custom non-negative integer type:

CREATE DOMAIN positive_integer AS int4 CHECK (VALUE >= 0);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  total_price positive_integer NOT NULL
);

This code first defines a domain named positive_integer. This domain essentially acts like a custom data type that inherits from the int4 type (equivalent to integer). It adds a check constraint that ensures the value can only be positive or zero.

• order_id: Similar to the previous example, this is a serial primary key.
• total_price: This column uses the newly created positive_integer domain, guaranteeing the stored value is non-negative.

This is the most common approach. You can use a signed integer data type like integer or bigint and enforce a check constraint during column creation to ensure only non-negative values are stored. This offers good data validation but might require an extra check during data manipulation.

BIGINT with custom logic:

If you need the full range of positive values that a bigint offers (theoretically up to 9 quintillion), you can use it with custom logic in your application. This logic would need to handle the interpretation of negative values as errors or invalid data. This approach requires more development effort but utilizes the full range of the data type.

BIGSERIAL:

While not strictly an unsigned integer, PostgreSQL offers the BIGSERIAL data type. This acts like a sequence that automatically generates unique, ever-increasing bigint values. It's useful for primary keys that are guaranteed to be positive and unique but doesn't offer the flexibility of user-defined values.

Using a dedicated bit for sign:

For smaller ranges of positive integers, you could consider using a standard integer data type (integer) and dedicating the most significant bit (sign bit) to represent a positive or negative value. This approach requires custom logic in your application to interpret and manipulate the data.

Choosing the right method:

• If data validation is crucial and the range of positive values is moderate, a signed integer with a CHECK constraint is a good choice.
• If the full range of a bigint is needed and you can handle negative values as errors, using bigint with custom logic might be suitable.
• For unique, ever-increasing positive identifiers, BIGSERIAL is a simple and efficient option.
• Dedicating a bit for the sign is less common but could be an option for specific use cases with limited range and custom logic.