When designing a service catalog or virtual server definition table in PostgreSQL, it is common to need a derived attribute that is automatically generated from several existing columns.
A practical example is a virtual server flavor code (SKU - Stock Keeping Unit) derived from these attributes:
- vserver_type – values such as
ECONOMY,STANDARD,PREMIUM - num_of_vcpu – number of vCPUs, for example 1 to 64
- capacity_of_vram – RAM size, for example 1 to 128
The requirement is to automatically generate a formatted code such as:
ECONOMY-04-016In this format:
vserver_typeis converted to uppercasenum_of_vcpuis formatted to 2 digitscapacity_of_vramis formatted to 3 digits- the delimiter is a hyphen (
-)
Why use a generated column?
PostgreSQL generated columns are a clean solution for simple derived values. Instead of maintaining the derived attribute manually or using a trigger, PostgreSQL computes the value automatically from other columns in the same row.
This approach has several advantages:
- the derived value is always consistent
- there is no trigger logic to maintain
- the column can still be indexed if needed
- the formatting logic is centralized in the table definition
Final solution
CREATE TABLE vserver (
vserver_type varchar(20),
num_of_vcpu int,
capacity_of_vram int,
generated_code text GENERATED ALWAYS AS (
upper(vserver_type) || '-' ||
lpad(num_of_vcpu::text, 2, '0') || '-' ||
lpad(capacity_of_vram::text, 3, '0')
) STORED
);How it works
The generated column uses three PostgreSQL functions and operators:
upper(vserver_type)converts the server type to uppercaselpad(num_of_vcpu::text, 2, '0')converts the vCPU count to text and left-pads it with zeros to a width of 2lpad(capacity_of_vram::text, 3, '0')converts the RAM value to text and left-pads it with zeros to a width of 3||concatenates the parts into a single string
So for example:
economybecomesECONOMY4becomes0416becomes016
The final generated code is therefore:
ECONOMY-04-016Example insert
INSERT INTO vserver (vserver_type, num_of_vcpu, capacity_of_vram)
VALUES ('economy', 4, 16);If we query the table:
SELECT * FROM vserver;The result will look like this:
vserver_type | num_of_vcpu | capacity_of_vram | generated_code
--------------+-------------+------------------+----------------
economy | 4 | 16 | ECONOMY-04-016More examples
STANDARD-01-008
PREMIUM-16-064
ECONOMY-64-128Recommended validation
In production, it is a good idea to enforce valid input values with CHECK constraints.
CREATE TABLE vserver (
vserver_type varchar(20),
num_of_vcpu int,
capacity_of_vram int,
generated_code text GENERATED ALWAYS AS (
upper(vserver_type) || '-' ||
lpad(num_of_vcpu::text, 2, '0') || '-' ||
lpad(capacity_of_vram::text, 3, '0')
) STORED,
CHECK (vserver_type IN ('ECONOMY', 'STANDARD', 'PREMIUM')),
CHECK (num_of_vcpu BETWEEN 1 AND 64),
CHECK (capacity_of_vram BETWEEN 1 AND 128)
);This ensures that:
- only valid server types are accepted
- vCPU count stays within the expected range
- RAM capacity stays within the expected range
Why this pattern is useful
This pattern is useful for building a canonical service code or flavor identifier that is human-readable and deterministic. In infrastructure and cloud platforms, such a code can be reused across:
- service catalogs
- pricing definitions
- provisioning logic
- reporting and analytics
- API output
Because the value is generated directly by PostgreSQL, applications do not need to duplicate the formatting logic.
Conclusion
If you need a simple derived value in PostgreSQL that depends only on other columns in the same row, a generated column is often the best solution.
In this example, PostgreSQL automatically creates a clean server code such as ECONOMY-04-016 from three separate attributes, without requiring triggers or procedural logic.
For straightforward formatting and deterministic derived values, this is usually the simplest and most maintainable approach.
