DEV Community

ひとし 田畑
ひとし 田畑

Posted on

In information_schema, a generated column looks exactly like a plain one

#postgres #sql #database

I was rendering a \d-style column list — name, type, nullable, default — straight off information_schema.columns. Clean, portable, boring. Then I pointed it at a table with a generated column and the view was quietly, confidently wrong.

The generated column showed up as a perfectly ordinary column. Type: fine. Nullable: fine. Default: NULL. No hint anywhere that the value is computed, not stored. If you trusted my panel, you'd think you could just write to it.

CREATE TABLE orders (
    qty   int,
    price numeric,
    total numeric GENERATED ALWAYS AS (qty * price) STORED
);
Enter fullscreen mode Exit fullscreen mode 
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'orders';
Enter fullscreen mode Exit fullscreen mode 
 column_name | data_type | is_nullable | column_default
-------------+-----------+-------------+----------------
 qty         | integer   | YES         |
 price       | numeric   | YES         |
 total       | numeric   | YES         |          ← computed, but you can't tell
Enter fullscreen mode Exit fullscreen mode

total is a column you literally cannot INSERT into, and information_schema describes it identically to qty. The standard view just doesn't carry the bit.

The bit lives in pg_attribute

PostgreSQL records "is this column generated" in pg_attribute.attgenerated:

  • '' — ordinary column
  • 's' — STORED (the value is materialized on write)
  • 'v' — VIRTUAL (computed on read; new in PostgreSQL 18)

So you stop trusting information_schema for this one fact and join down to the catalog:

SELECT c.column_name, c.data_type, c.is_nullable, c.column_default,
       a.attgenerated AS generated,
       CASE WHEN a.attgenerated <> ''
            THEN pg_get_expr(ad.adbin, ad.adrelid) END AS gen_expr
FROM information_schema.columns c
LEFT JOIN pg_attribute a
       ON a.attrelid = format('%I.%I', c.table_schema, c.table_name)::regclass
      AND a.attname  = c.column_name
      AND NOT a.attisdropped
LEFT JOIN pg_attrdef ad
       ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
WHERE c.table_schema = 'public' AND c.table_name = 'orders'
ORDER BY c.ordinal_position;
Enter fullscreen mode Exit fullscreen mode 
 column_name | generated |   gen_expr
-------------+-----------+---------------
 qty         |           |
 price       |           |
 total       | s         | (qty * price)
Enter fullscreen mode Exit fullscreen mode

Now total carries its truth: generated, stored, computed from qty * price. Three small gotchas earned that result.

Join pg_attribute by name, and skip dropped columns. attnum is only stable if you exclude dropped columns (NOT a.attisdropped) — a dropped column leaves a tombstone row that shifts nothing if you account for it and corrupts your mapping if you don't. Matching on attname against information_schema's already-filtered list keeps the two sides aligned.

The expression comes from pg_attrdef, not pg_attribute. The generation expression is stored where defaults live, and you decompile it with pg_get_expr(ad.adbin, ad.adrelid).

Guard pg_get_expr with the CASE. This is the one that bites. A plain column with a normal default also has a pg_attrdef row — its adbin is the default literal, not a generation expression. Without WHEN a.attgenerated <> '' you'd happily print now() or 0 into a "generated from" column and mislabel every defaulted column in the table. The CASE keeps pg_get_expr pointed only at columns that are actually generated.

Why now: PostgreSQL 18 flips the default

Generated columns aren't new — STORED arrived in PostgreSQL 12, and attgenerated has existed since then, so the query above is version-robust all the way back. What changed is the blast radius.

PostgreSQL 18 adds VIRTUAL generated columns (computed on read, taking no storage) and makes VIRTUAL the default — write GENERATED ALWAYS AS (…) with no STORED/VIRTUAL keyword and you get a virtual one. Which means generated columns are about to get a lot more common in schemas written by people who never typed the word "generated" with intent. Every \d-clone built on information_schema is going to start being wrong about more tables, not fewer. The fix is the same one byte: read attgenerated.

The same hole in MySQL

MySQL has the identical trap from the other direction: information_schema.COLUMNS does carry the info, just smuggled into a free-text EXTRA field that also holds auto_increment and on update flags:

SELECT COLUMN_NAME, EXTRA, GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?;
Enter fullscreen mode Exit fullscreen mode

EXTRA reads STORED GENERATED or VIRTUAL GENERATED (and GENERATION_EXPRESSION is '' for ordinary columns). You substring-match it rather than =, because the flags can combine. Same concept, same two kinds — stored vs virtual — different cubbyhole.

The honest part

This doesn't make a generated column writable or change anything about it — it just stops a schema viewer from lying by omission. If all you ever do is SELECT *, you never noticed the gap. The moment you build tooling that says "here are this table's columns and here's what you can put in them," the one byte in attgenerated is the difference between a true map and a confident wrong one.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What does your \d clone quietly get wrong?

Top comments (0)