Create a column composed of several columns in a postgres data table

Question

I want to create a column in a table in postgres for special needs, this column must consist for example of a concatenation of two another column.

for example i have

LAYER: 'accessory'
TYPE: 'copper'

I want a column (symbology) to be filled automatically during an insertion or an update by

SYMBOLOGY: 'accessory - copper'

do I need a trigger to do this or is there some other ways to avoid the trigger.

Illustration

LAYER	TYPE	SYMBOLOGY
accessory	copper	accessory - copper

cordially

Schwern · Accepted Answer · 2021-03-17 09:17:25Z

1

alter table things
  add column symbology text
  generated always as (layer || ' - ' || type)
  stored;

Or, if its only for some specific circumstances, you can create a view

create view thing_symbols as
select
  *,
  (layer || ' - ' || type) as symbology
from things;

answered Mar 17, 2021 at 9:17

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

is there a solution in 9.x versions for the generated column ?

@Kalifornium . . . In earlier versions of Postgres, you would use either a trigger or a view.