Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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.

postgres=# create table people
postgres-# (
postgres(#   first_name varchar(20),
postgres(#   last_name varchar(20),
postgres(#   display_name varchar(40)
postgres(# );
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION people_insert() RETURNS trigger AS '
postgres'#     BEGIN
postgres'#         NEW.display_name := NEW.first_name||'' ''||NEW.last_name;
postgres'#         RETURN NEW;
postgres'#     END;
postgres'# ' LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# CREATE TRIGGER people_insert BEFORE INSERT OR UPDATE ON people FOR EACH ROW EXECUTE PROCEDURE people_insert();
CREATE TRIGGER
postgres=# insert into people values ('Larry','Ellison');
INSERT 0 1
postgres=# select * from people;
 first_name | last_name | display_name
------------+-----------+---------------
 Larry      | Ellison   | Larry Ellison
(1 row)

postgres=#
share|improve this answer
    
Thanks! Wouldn't the line CREATE TRIGGER people_insert BEFORE INSERT OR UPDATE... handle the updating as well? –  MattSayar Aug 3 '12 at 17:41
    
Oh, yeah. Must've been half asleep :) –  Phil Aug 3 '12 at 18:05
    
display_name should probably be 41, if you care. Since you add a space between two 20 char columns. –  isaaclw Jul 8 at 18:08

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.

share|improve this answer

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.

share|improve this answer
    
I actually do need to store the value for backwards compatibility, but this is a pretty cool approach! The OCD part of me also wants there to be a check for NULL last names, but that would probably mean you have bigger data integrity issues to worry about at that point –  MattSayar Aug 3 '12 at 17:39
    
Well, I declared last_name to be NOT NULL in my example. :-) We had name columns like that which were in the table and maintained by triggers and switched to this approach without much pain, but our framework always uses an alias and always qualifies references, so that part was easy. If you have code which isn't consistent about qualifying column references I can see where it could be a pain to track down all those cases. –  kgrittn Aug 3 '12 at 19:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.