Add support for generated columns in PostgreSQL (Redux) by MSNexploder · Pull Request #41856 · rails/rails

MSNexploder · 2021-04-06T17:48:28Z

Basically this is a friendly takeover of #39368 which adds support for generated columns in PostgreSQL (which are available since PostgreSQL 12).

Sadly there was no progress since July 2020.
I kept @fanfilmu as author and just rebased onto current main and incorporated most of the given feedback.

MSNexploder · 2021-04-06T17:49:19Z

activerecord/lib/active_record/connection_adapters/postgresql/schema_creation.rb

This is the only place where I disagree with some of the given feedback regarding future proofing (e.g. #39368 (comment)). In PostgreSQL 14 there is still no VIRTUAL support planed.
In my opinion it's better to provide a precise error message rather than adding support for something that may not happen anytime soon (and maybe even create incorrect SQL statements in the process).

Note: This only affects migration support. Reading any type of virtual column is supported.

fschwahn · 2021-07-14T09:27:23Z

We've been running this PR as a monkeypatch for a few months without issues. It would be great to have this merged!

luizkowalski · 2021-09-15T15:16:31Z

Any chances that this PR could fix this issue? #42469

matthewd · 2021-09-16T14:05:26Z

Yeah, with time marching on, I'm feeling more inclined towards the current implementation's conservatism: Postgres will presumably implement virtual columns at some point in the future, but our ability to predict exactly what that'll look like is limited. Better to make support available for the existing feature, and leave an attempt to use the still-theoretical future behaviour to fail in a clear way (which is no better or worse than how it would already fail today if we continue to not ship this PR).

The current implementation (rails#41856) required `stored: true` to be passed when using `t.virtual()` https://github.com/rails/rails/pull/41856/files#diff-5b1b920fc3bebbb1d4c144b302603e5003a1d9e5a493e3234bf764b9e26fbe30R68-R75

bbugh · 2021-12-17T19:51:42Z

Apologies for commenting, did this make it into Rails 7? I was looking through changelogs and couldn't find anything conclusive.

ghiculescu · 2021-12-17T19:52:57Z

Yep, it's there: https://github.com/rails/rails/blob/v7.0.0/activerecord/lib/active_record/connection_adapters/postgresql/column.rb#L21

jhubert · 2022-04-12T23:17:29Z

We're not quite on Rails 7 yet, so I back-ported this to a gem for Rails 6.1.5 in the hopes that it may be useful for others as well:

https://github.com/retailzipline/pg_generated_column_support

PostgreSQL 18 Beta 1 introduces virtual (not persisted) generated columns, which are now the default unless the STORED option is explicitly specified. Rails previously supported only stored generated columns on PostgreSQL 12+, via rails#41856, as only stored generated columns were supported by PostgreSQL at the time. This commit adds support for virtual generated columns on PostgreSQL 18 and later: - `t.virtual ... stored: false` now generates virtual generated columns. - Omitting `stored:` also creates virtual generated columns on PostgreSQL 18+. - On PostgreSQL < 18, specifying `stored: false` or omitting `stored:` raises ArgumentError. consistent with the existing behavior in earlier Rails versions. Reference: PostgreSQL 18 release notes https://www.postgresql.org/docs/18/release-18.html > Allow generated columns to be virtual, and make them the default > (Peter Eisentraut, Jian He, Richard Guo, Dean Rasheed) > > Virtual generated columns generate their values when the columns are read, not written. > The write behavior can still be specified via the STORED option. Source: - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=83ea6c540 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cdc168ad4 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e4351af3

PostgreSQL 18 Beta 1 introduces virtual (not persisted) generated columns, which are now the default unless the STORED option is explicitly specified. Rails previously supported only stored generated columns on PostgreSQL 12+, via rails#41856, as only stored generated columns were supported by PostgreSQL at the time. This commit adds support for virtual generated columns on PostgreSQL 18 and later: - `t.virtual ... stored: false` now generates virtual generated columns. - Omitting `stored:` also creates virtual generated columns on PostgreSQL 18+. - On PostgreSQL < 18, specifying `stored: false` or omitting `stored:` raises ArgumentError. consistent with the existing behavior in earlier Rails versions. Reference: PostgreSQL 18 release notes https://www.postgresql.org/docs/18/release-18.html > Allow generated columns to be virtual, and make them the default > (Peter Eisentraut, Jian He, Richard Guo, Dean Rasheed) > > Virtual generated columns generate their values when the columns are read, not written. > The write behavior can still be specified via the STORED option. Source: - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=83ea6c540 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cdc168ad4 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e4351af3 Co-authored-by: fatkodima <[email protected]>

PostgreSQL 18 Beta 1 introduces virtual (not persisted) generated columns, which are now the default unless the STORED option is explicitly specified. Rails previously supported only stored generated columns on PostgreSQL 12+, via rails#41856, as only stored generated columns were supported by PostgreSQL at the time. This commit adds support for virtual generated columns on PostgreSQL 18 and later: - `t.virtual ... stored: false` now generates virtual generated columns. - Omitting `stored:` also creates virtual generated columns on PostgreSQL 18+. - On PostgreSQL < 18, specifying `stored: false` or omitting `stored:` raises ArgumentError. consistent with the existing behavior in earlier Rails versions. Reference: PostgreSQL 18 release notes https://www.postgresql.org/docs/18/release-18.html > Allow generated columns to be virtual, and make them the default > (Peter Eisentraut, Jian He, Richard Guo, Dean Rasheed) > > Virtual generated columns generate their values when the columns are read, not written. > The write behavior can still be specified via the STORED option. Source: - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=83ea6c540 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cdc168ad4 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e4351af3 Co-authored-by: fatkodima <[email protected]> Co-authored-by: Ryuta Kamizono <[email protected]>

rails/rails#41856

PostgreSQL 18 Beta 1 introduces virtual (not persisted) generated columns, which are now the default unless the STORED option is explicitly specified. Rails previously supported only stored generated columns on PostgreSQL 12+, via rails#41856, as only stored generated columns were supported by PostgreSQL at the time. This commit adds support for virtual generated columns on PostgreSQL 18 and later: - `t.virtual ... stored: false` now generates virtual generated columns. - Omitting `stored:` also creates virtual generated columns on PostgreSQL 18+. - On PostgreSQL < 18, specifying `stored: false` or omitting `stored:` raises ArgumentError. consistent with the existing behavior in earlier Rails versions. Reference: PostgreSQL 18 release notes https://www.postgresql.org/docs/18/release-18.html > Allow generated columns to be virtual, and make them the default > (Peter Eisentraut, Jian He, Richard Guo, Dean Rasheed) > > Virtual generated columns generate their values when the columns are read, not written. > The write behavior can still be specified via the STORED option. Source: - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=83ea6c540 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cdc168ad4 - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e4351af3 Co-authored-by: fatkodima <[email protected]> Co-authored-by: Ryuta Kamizono <[email protected]>

rails-bot bot added activerecord docs labels Apr 6, 2021

MSNexploder commented Apr 6, 2021

View reviewed changes

MSNexploder force-pushed the virtual-columns branch 3 times, most recently from 6e1b337 to 4233c97 Compare April 13, 2021 06:53

MSNexploder force-pushed the virtual-columns branch 2 times, most recently from 89d5577 to 249e327 Compare April 18, 2021 09:36

fanfilmu and others added 2 commits April 29, 2021 13:01

Add support for generated columns in PostgreSQL

2cf41d7

Incorporate feedback

64fd666

MSNexploder force-pushed the virtual-columns branch from 249e327 to 64fd666 Compare April 29, 2021 11:01

judy mentioned this pull request May 11, 2021

Add support for generated columns in PostgreSQL #39368

Closed

matthewd merged commit e1a09e6 into rails:main Sep 16, 2021

choznerol mentioned this pull request Sep 16, 2021

Add mandatory 'stored' option to generated column example #43229

Merged

MSNexploder deleted the virtual-columns branch September 16, 2021 18:55

bnjamin mentioned this pull request Sep 19, 2021

Error when persisting model with virtual column in PG #43262

Closed

DNNX mentioned this pull request Nov 4, 2021

Schema dumper incorrectly dumps operator-based generated PG columns #43590

Closed

bbugh mentioned this pull request Feb 2, 2022

Virtual PostgreSQL generated column in schema.rb is always as: nil, but correct in database #44318

Closed

fatkodima mentioned this pull request May 11, 2022

Fixtures with multiple records error out when table has an generated column #42469

Closed

Envek mentioned this pull request Dec 6, 2022

Ignore virtual columns in insert statements evilmartians/evil-seed#16

Merged

mitsuru mentioned this pull request Mar 30, 2023

virtual depth_cache stefankroes/ancestry#662

Closed

$@fractaledmind$ fractaledmind mentioned this pull request Sep 21, 2023

Add support for generated columns in SQLite3 adapter #49346

Merged

4 tasks

yahonda mentioned this pull request Jun 2, 2025

Support virtual (not persisted) generated columns on PostgreSQL 18+ #55142

Merged

4 tasks

ttilberg added a commit to ttilberg/activerecord-sqlserver-adapter that referenced this pull request Jun 23, 2025

Translate the psql virtual column work

1d86429

rails/rails#41856

Sebmono mentioned this pull request Jan 19, 2026

[PR] Support virtual (not persisted) generated columns on PostgreSQL 18+ Sandgarden-Demo/rails#155

Closed

4 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Comments

Add support for generated columns in PostgreSQL (Redux)#41856

Add support for generated columns in PostgreSQL (Redux)#41856
matthewd merged 2 commits intorails:mainfrom
MSNexploder:virtual-columns

MSNexploder commented Apr 6, 2021

Uh oh!

MSNexploder Apr 6, 2021

Uh oh!

fschwahn commented Jul 14, 2021

Uh oh!

luizkowalski commented Sep 15, 2021

Uh oh!

matthewd commented Sep 16, 2021

Uh oh!

bbugh commented Dec 17, 2021

Uh oh!

ghiculescu commented Dec 17, 2021

Uh oh!

jhubert commented Apr 12, 2022

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants

Comments

Conversation

MSNexploder commented Apr 6, 2021

Uh oh!

MSNexploder Apr 6, 2021

Choose a reason for hiding this comment

Uh oh!

fschwahn commented Jul 14, 2021

Uh oh!

luizkowalski commented Sep 15, 2021

Uh oh!

matthewd commented Sep 16, 2021

Uh oh!

bbugh commented Dec 17, 2021

Uh oh!

ghiculescu commented Dec 17, 2021

Uh oh!

jhubert commented Apr 12, 2022

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants