patternsqlModerate

Set a column's default value to the concatenation of two other columns' values?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thecolumnscolumnvaluedefaulttwovaluesotherconcatenationset

Problem

I have a Postgresql 8.1 database. In one table, there are three columns: first_name, last_name, display_name.

Is it possible to set the default value of display_name to be first_name + " " + last_name?

Solution

Use a trigger.

Here's some code you can use as a base. If you also need to handle UPDATEs, only a small change is required.

create table people
 (
   first_name varchar(20),
   last_name varchar(20),
   display_name varchar(40)
 );
CREATE TABLE
 CREATE OR REPLACE FUNCTION people_insert() RETURNS trigger AS '
     BEGIN
         NEW.display_name := NEW.first_name||'' ''||NEW.last_name;
         RETURN NEW;
     END;
 ' LANGUAGE plpgsql;

 postgres=# CREATE FUNCTION

 CREATE TRIGGER people_insert BEFORE INSERT OR UPDATE ON people FOR 
 EACH ROW EXECUTE PROCEDURE people_insert();

postgres=# CREATE TRIGGER
 insert into people values ('Larry','Ellison');

postgres=# INSERT 0 1

 postgres=# select * from people;
 first_name | last_name | display_name
------------+-----------+---------------
 Larry      | Ellison   | Larry Ellison
(1 row)

postgres=#

Code Snippets

create table people
 (
   first_name varchar(20),
   last_name varchar(20),
   display_name varchar(40)
 );
CREATE TABLE
 CREATE OR REPLACE FUNCTION people_insert() RETURNS trigger AS '
     BEGIN
         NEW.display_name := NEW.first_name||'' ''||NEW.last_name;
         RETURN NEW;
     END;
 ' LANGUAGE plpgsql;

 postgres=# CREATE FUNCTION


 CREATE TRIGGER people_insert BEFORE INSERT OR UPDATE ON people FOR 
 EACH ROW EXECUTE PROCEDURE people_insert();

postgres=# CREATE TRIGGER
 insert into people values ('Larry','Ellison');

postgres=# INSERT 0 1

 postgres=# select * from people;
 first_name | last_name | display_name
------------+-----------+---------------
 Larry      | Ellison   | Larry Ellison
(1 row)

postgres=#

Context

StackExchange Database Administrators Q#21897, answer score: 19

Revisions (0)

No revisions yet.