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.