0

I have in post a column I need to generate, which follows a naming pattern. The pattern is unique for each table, but will always include some sort of suffix or prefix, and the current row value of the some of it columns.

-- Create table_registration
ALTER TABLE public.person
DROP COLUMN IF EXISTS entry_name, 
ADD COLUMN entry_name TEXT generated ALWAYS as (concat_ws(' ', hobby)) stored; 

This sometimes trigger an error like

Npgsql.PostgresException: '42P17: generation expression is not immutable'

I then became aware of this might not be what I would like to to do, since it basically the same as a new column and takes up space - from basically do logic operation.

Is there someway I can generate this - and modify this without taking of space, and if that is not possible why I am not able to drop the columns once it has been added?

GMB
  • 216,147
  • 25
  • 84
  • 135
kafka
  • 573
  • 1
  • 11
  • 28

1 Answers1

0

As of Postgres 12, generated columns are always stored. Support for virtual generated columns, whose values are computed on the fly (and hence occupy no storage) might be implemeted in future versions.

A straight-forward alternative is to use a view instead:

create view person_view as
select p.*, concat_ws(' ', hobby) entry_name
from public.person p
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I would still need to drop the view in case of the pattern changed.. and when I do that I get the same issue with `generation expression is not immutable` - some how I can overcome this? – kafka Apr 26 '20 at 12:54