How to Use a PostgreSQL Generated Column for a Formatted Virtual Server SKU / Flavor

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-016

In this format:

• vserver_type is converted to uppercase
• num_of_vcpu is formatted to 2 digits
• capacity_of_vram is 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 uppercase
• lpad(num_of_vcpu::text, 2, '0') converts the vCPU count to text and left-pads it with zeros to a width of 2
• lpad(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:

• economy becomes ECONOMY
• 4 becomes 04
• 16 becomes 016

The final generated code is therefore:

ECONOMY-04-016

Example 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-016

More examples

STANDARD-01-008
PREMIUM-16-064
ECONOMY-64-128

Recommended 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.