How to Set Unsigned Integer in PostgreSQL Table Column

Introduction

When dealing with integers in databases, sometimes there’s a need to enforce that a number is non-negative. Although PostgreSQL does not have an unsigned integer data type explicitly, there are workarounds that can be used to enforce unsigned integer behavior.

Understanding Data Types

Before we explore how to simulate unsigned integers, it’s important to understand the available data types. PostgreSQL offers SMALLINT, INTEGER, and BIGINT as standard integer types, each with a different range. For an unsigned equivalent, you can use various techniques to ensure non-negative values within these data types.

Creating a Check Constraint

One approach is to create a column with the standard integer type and then enforce non-negativity with a check constraint:

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    unsigned_column INTEGER CHECK (unsigned_column >= 0)
);

This ensures that whenever an INSERT or UPDATE operation is performed, the value for unsigned_column will be checked to be non-negative.

Using a Domain

To make it easier to reuse the unsigned behavior, you can define a domain:

CREATE DOMAIN unsigned_int AS INTEGER
CHECK (VALUE >= 0);

CREATE TABLE another_table (
    id SERIAL PRIMARY KEY,
    positive_count unsigned_int
);

With a domain, you embed the check logic within the type definition, streamlining the table creation process.

Advanced Unsigned Integer Techniques

For a more advanced setup, you could create custom functions or use them in combination with triggers for additional control. Let’s explore how these advanced techniques can be used.

Custom Function for Validation

Create a custom function to validate an integer value and use it in conjunction with the check constraint:

CREATE OR REPLACE FUNCTION check_unsigned(integer) RETURNS boolean AS $
BEGIN
    RETURN $1 >= 0;
END;
$ LANGUAGE plpgsql;

CREATE TABLE advanced_table (
    id SERIAL PRIMARY KEY,
    unsigned_column INTEGER CHECK (check_unsigned(unsigned_column))
);

This function adds an abstraction layer, making adjustments to unsigned behavior centralized in function logic.

Using Triggers for Validation

A trigger can be used to enforce the unsigned property before an insert or update occurs:

CREATE OR REPLACE FUNCTION before_insert_update_trigger()
RETURNS trigger AS $
BEGIN
    IF NEW.unsigned_column < 0 THEN
        RAISE EXCEPTION 'unsigned_column must be non-negative.';
    END IF;
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_unsigned_check
BEFORE INSERT OR UPDATE
ON advanced_table
FOR EACH ROW
EXECUTE FUNCTION before_insert_update_trigger();

This trigger adds another layer of protection to ensure data integrity with more custom behavior if needed.

Social Use Cases of Unsigned Integers

Whether you’re counting inventory, tracking user scores, or storing age data, unsigned integers ensure that your values stay within an acceptable range. They help maintain data integrity and can trigger meaningful exceptions when data rules are violated.

Performance Considerations

While constraints and triggers can enforce data integrity, they come with a performance cost. Benchmarking these methods in the context of your application’s use cases is essential to balance integrity and performance.

Conclusion

In conclusion, even though PostgreSQL does not natively support unsigned integers, you can use check constraints, domains, or even triggers to simulate this behavior effectively. Whether you are ensuring that an inventory count never goes negative, or keeping a user score within bounds, these techniques will provide the necessary mechanisms to maintain data validity in your PostgreSQL tables.