PostgreSQL 18 Virtual Generated Column Woes

When PostgreSQL 18 was released with support for virtual generated columns, I was genuinely excited. Either I missed this in the pre-release notes or I just got swept up in the moment, but I thought, finally — I can replace a view I've been carrying around with a generated column.

The use case

I have a small personal wardrobe management app. I score items based on two things:

• Cost Per Wear (CPW)
• How long it’s been since the last time they were worn

Today I compute that score in a non-materialized view because the calculation depends on the current time at query. That’s been fine, but a generated column would make the score feel like a first-class attribute on the row without having to sprinkle joins everywhere.

Why virtual got me excited

Historically, generated columns in Postgres had to be stored, which disqualified anything time-dependent. With virtual generated columns, I figured I could let the database compute the value at read time and avoid storing anything.

The immutability catch

The problem is you can't use now() in a generated column expression because it isn't immutable. That makes sense for stored generated columns. For a virtual generated column, it felt like it should be okay to vary at read time — but PostgreSQL still enforces this immutability requirement.

No problem. I'll wrap the logic in a function. That's useful anyway, because I can evolve the implementation without altering the table definition.

The migration I wanted to run

class AddJarvisScoreToItems < ActiveRecord::Migration[8.0]
  def up
    execute <<-SQL
      CREATE FUNCTION calculate_jarvis_score(last_worn_on date, cost_per_wear numeric)
      RETURNS numeric
      AS $$
        BEGIN
          RETURN log(coalesce(extract(day from now() - last_worn_on), 365) + cost_per_wear + 0.1);
        END;
      $$
      LANGUAGE plpgsql
      IMMUTABLE
    SQL

    execute <<-SQL
      ALTER TABLE items
      ADD COLUMN jarvis_score numeric GENERATED ALWAYS AS (calculate_jarvis_score(last_worn_on, cost_per_wear)) VIRTUAL
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE items
      DROP COLUMN jarvis_score
    SQL

    execute <<-SQL
      DROP FUNCTION calculate_jarvis_score(last_worn_on date, cost_per_wear numeric)
    SQL
  end
end

…and the reality

Instead of a neat virtual column, I got a very specific error:

Virtual generated columns that make use of user-defined functions are not yet supported.

From the documentation:

"A virtual generated column cannot have a user-defined type, and the generation expression of a virtual generated column must not reference user-defined functions or types, that is, it can only use built-in functions or types. This applies also indirectly, such as for functions or types that underlie operators or casts. (This restriction does not exist for stored generated columns.)"

So I’m back to keeping the view and joining for now. Virtual generated columns are a great step forward, but for time-based calculations (and anything needing a user-defined function), I guess I’ll have to wait a little longer.

If there’s a safe workaround that keeps reads predictable without contortions, I’d love to hear it.