`generation_expression` in `information_schema.columns` is NULL for a generated column
- Dominant language
- Go
- Stars
- 2.1k
- Forks
- 73
- Avg merge
- 1d 10h
- Merged PRs (30d)
- 129
Description
On DoltgreSQL 1.3.1, `information_schema.columns` answers NULL in `generation_expression` for the stored
generated column `b int GENERATED ALWAYS AS (a + 1) STORED`, although the column computes its values and
`is_generated` answers `ALWAYS`. PostgreSQL 18.6 answers the expression, `(a + 1)`.
## Reproduction
[`repro.sql`](https://github.com/Reliable-Collaboration/repro-doltgresql-bug-generation-expression/blob/main/repro.sql):
```sql
-- Print NULL as (null), unlike an empty string.
\pset null '(null)'
-- A generated column whose expression needs no brackets.
CREATE TABLE t (
a int,
b int GENERATED ALWAYS AS (a + 1) STORED
);
-- The generated column works.
INSERT INTO t (a) VALUES (1);
SELECT a, b FROM t;
-- The generated column in information_schema.columns.
SELECT is_generated, generation_expression,
column_default
FROM information_schema.columns
WHERE table_name = 't' AND column_name = 'b';
```
## Expected behavior
The generated column computes `b` = 2 for `a` = 1, and `information_schema.columns` answers its expression
in `generation_expression`, next to `is_generated` `ALWAYS` and a NULL `column_default`. This is what
PostgreSQL 18.6 does:
```
-- The generated column works.
INSERT INTO t (a) VALUES (1);
INSERT 0 1
SELECT a, b FROM t;
a | b
---+---
1 | 2
(1 row)
-- The generated column in information_schema.columns.
SELECT is_generated, generation_expression,
column_default
FROM information_schema.columns
WHERE table_name = 't' AND column_name = 'b';
is_generated | generation_expression | column_default
--------------+-----------------------+----------------
ALWAYS | (a + 1) | (null)
(1 row)
```
## Actual behavior
The generated column computes the same value, and `is_generated` and `column_default` answer the same, but
`generation_expression` is NULL. This is what DoltgreSQL 1.3.1 does:
```
-- The generated column works.
INSERT INTO t (a) VALUES (1);
INSERT 0 1
SELECT a, b FROM t;
a | b
---+---
1 | 2
(1 row)
-- The generated column in information_schema.columns.
SELECT is_generated, generation_expression,
column_default
FROM information_schema.columns
WHERE table_name = 't' AND column_name = 'b';
is_generated | generation_expression | column_default
--------------+-----------------------+----------------
ALWAYS | (null) | (null)
(1 row)
```
## Run it
A runnable reproduction is at https://github.com/Reliable-Collaboration/repro-doltgresql-bug-generation-expression. Its script runs the test on PostgreSQL and DoltgreSQL in throwaway containers and prints the two outputs side by side:
```sh
git clone https://github.com/Reliable-Collaboration/repro-doltgresql-bug-generation-expression.git
cd repro-doltgresql-bug-generation-expression
./repro.sh
```
## Other observations
Each was checked on DoltgreSQL 1.3.1 and PostgreSQL 18.6 with the same kind of test:
- `generation_expression` is NULL on DoltgreSQL for every generated column tried, while `is_generated`
answers `ALWAYS` and `column_default` NULL on both servers: `(upper(s))`, `(length(s))`, `(a)`,
`(a::text)`, `(a * 2)` added with `ALTER TABLE ... ADD COLUMN`, and `(a + 1)` in a table in another
schema. PostgreSQL answers `upper(s)`, `length(s)`, `a`, `(a)::text`, `(a * 2)` and `(a + 1)`.
- It stays NULL from a new connection and, on DoltgreSQL, after `SELECT dolt_commit('-Am', 'generated')`.
- `((a + 1) * 2)` answers NULL too, where PostgreSQL answers `((a + 1) * 2)`. The test uses an expression
without brackets because that column stores 3 for `a = 1` on DoltgreSQL 1.3.1, where PostgreSQL stores 4,
a separate bug.
- In `pg_attribute`, `attgenerated` is `s` on both servers, but `atthasdef` is `f` on DoltgreSQL and `t` on
PostgreSQL. `pg_attrdef` has no row for the generated column on DoltgreSQL; on PostgreSQL its row answers
`(a + 1)` from `pg_get_expr(adbin, adrelid)`.
- For a plain `DEFAULT 7`, `pg_attrdef` has a row on DoltgreSQL, but `pg_get_expr(adbin, adrelid)` answers
NULL, where PostgreSQL answers `7`. `column_default` answers `7` on both.
- `dtd_identifier` is NULL for both columns of the table on DoltgreSQL, where PostgreSQL answers `1` and
`2`.
- For an identity column, `id int GENERATED ALWAYS AS IDENTITY`, DoltgreSQL answers `is_generated` `ALWAYS`,
`is_identity` `NO` and a NULL `identity_generation`, where PostgreSQL answers `NEVER`, `YES` and `ALWAYS`.
- PostgreSQL 18.6 creates `GENERATED ALWAYS AS (a + 1) VIRTUAL`, and the same clause without `STORED`, as
virtual columns (`attgenerated` `v`). DoltgreSQL refuses both, with
`ERROR: at or near "virtual": syntax error` and `ERROR: at or near ")": syntax error`.
- Another report on the same view: [dolthub/doltgresql#3244](https://github.com/dolthub/doltgresql/issues/3244),
where `information_schema.columns` answers blank metadata for the columns of a view.
## Possibly related
#3244 (open), about `information_schema.columns` returning blank metadata for view columns, concerns the same view but a different fault.
## Environment
- DoltgreSQL 1.3.1, the newest release when this was written: image `dolthub/doltgresql:1.3.1`, digest
`sha256:6c85cb1f35beabf47f094336a420255130b841b1645f36d79ef046276af36851`, built for linux/amd64 and
linux/arm64. Its bundled `psql` is 17.11.
- PostgreSQL 18.6: image `postgres:18.6-bookworm`, digest
`sha256:1c59e2c3c818eaa0f0628f695b36e7c9e362d6b219b36a54a32df645cbd7e1af`. Its `psql` is 18.6.
- Reproduced on 2026-09-11 (UTC) with Docker 29.7.2 on Ubuntu 26.04.1 LTS under WSL2 (Linux 6.18.33.2,
x86_64).
Contributor guide
Assessment
This issue has not been assessed yet.