Set a column's default value to the concatenation of two other columns' values?
You don't need to actually store the value; you can create a function that can be referenced much like a generated column. The one caveat is that references must always be qualified with the table or alias name.
CREATE TABLE person
(
id int PRIMARY KEY,
first_name text,
last_name text NOT NULL
);
INSERT INTO person
VALUES
(1, 'John', 'Smith'),
(2, 'Jane', 'Doe'),
(3, NULL, 'Prince');
CREATE FUNCTION display_name(rec person)
RETURNS text
STABLE
LANGUAGE SQL
COST 5
AS $$
SELECT
CASE
WHEN $1.first_name IS NULL THEN ''
ELSE $1.first_name || ' '
END || $1.last_name;
$$;
SELECT p.id, p.display_name FROM person p;
The results:
id | display_name ----+-------------- 1 | John Smith 2 | Jane Doe 3 | Prince (3 rows)
You can even index on the generated value, including using KNN searches based on trigram similarity. For example:
CREATE EXTENSION pg_trgm;
CREATE INDEX person_trgm_name
ON person
USING gist
(display_name(person) gist_trgm_ops);
SELECT
p.id,
p.display_name,
similarity(p.display_name, 'Jane')
FROM person p
ORDER BY p.display_name <-> 'Jane'
LIMIT 2;
This type of search returns rows from the index scan in order of "distance" from the search string. If you want to see how "close" they were, you could either use the distance operator (<->
) or the similarity()
function (which is 1 - distance). A KNN search can return the K "nearest neighbors" very quickly, even with a very large data set.
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=#
With the DEFAULT setting on a column table, nope.
Your best bet here is a TRIGGER with the NEW value for each column you want computed.