Table field with combined fields values in PostgreSQL

Question

I want to create a table ex. Person. Where Person has name and last_name. I want to add a third field called full_name which is the combination of name and last name.

CREATE TABLE person(
   id BIGINT,
   name VARCHAR(50),
   last_name VARCHAR(50),
   full_name VARCHAR(100) *COMBINE(name,last_name)
)

I'm aware that I can create a function like explained here Combine two columns and add into one new column like this

CREATE FUNCTION combined(rec person)
  RETURNS text
  LANGUAGE SQL
AS $$
  SELECT $1.name || $1.last_name;
$$; 

SELECT *, person.combined FROM person;

But I would like to declare it in the table as another field, is it possible?

Gordon Linoff · Answer 1 · 2021-09-13T13:08:25.870

2

You can add a computed column:

alter table person add column full_name text generated always as
    (name || last_name) stored;

Here is a db<>fiddle.

edited Sep 13 '21 at 13:08

answered Sep 13 '21 at 12:54

Gordon Linoff

1,242,037
58
646
786

It throws an error syntax error at or near "GENERATED" – César Castro Aroche Sep 13 '21 at 13:04
CREATE TABLE person ( id BIGINT DEFAULT nextval('id_sequence') CONSTRAINT pk_id_person PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) NOT null, display_name TEXT GENERATED ALWAYS AS ( first_name || last_name ) stored ); – César Castro Aroche Sep 13 '21 at 13:05
@CésarCastroAroche . . . Ahh. Generated columns were added in Postgres 12. You might just want to use a view. – Gordon Linoff Sep 13 '21 at 13:08

score 0 · Answer 2 · answered Sep 13 '21 at 13:23

In Postgres 12:

CREATE TABLE person ( 
     id BIGINT DEFAULT nextval('id_sequence') CONSTRAINT pk_id_person 
             PRIMARY KEY, 
     first_name VARCHAR(50), 
     last_name VARCHAR(50) NOT null, 
     full_name VARCHAR(100) GENERATED ALWAYS AS ( first_name || last_name ) stored );

Older Postgres:

Create a table without full name. And add a view that combines the field like this:

CREATE VIEW person_view AS
SELECT id, first_name, last_name, (first_name || last_name) as full_name
FROM person;

Table field with combined fields values in PostgreSQL

2 Answers2