PostgreSQL - Generate unique name on insert based on other columns I have this table:

create table people (
    id serial primary key,
    name varchar,
    surname varchar,
    uname varchar unique
);


I want to generate the value of uname on insert, based on name and surname values, appending a number if there is a duplicate, for example:

insert into people (name, surname) values ('John', 'Doe');
insert into people (name, surname) values ('John', 'Doe');
insert into people (name, surname) values ('Sarah', 'Doe');
insert into people (name, surname) values ('Sarah', 'Doe');


will produce

 id | name  | surname | uname 
----+-------+---------+----------
  1 | John  | Doe     | JohnDoe
  2 | John  | Doe     | JohnDoe1
  3 | Sarah | Doe     | SarahDoe
  4 | Sarah | Doe     | SarahDoe1


Is it possible to do it with PostgreSQL?
You can achieve this with triggers and trigger procedures (trigger functions). Read Tutorial on triggers and from PostgreSQL documentation 40.9. Trigger Procedures. The following is the code for both the procedure and the trigger (there might be even more elegant ways to form the procedure):

CREATE OR REPLACE FUNCTION create_people() RETURNS trigger AS $$
    DECLARE
    u_num integer;
    BEGIN
    u_num := (SELECT count(*) FROM people WHERE NEW.name = people.name AND NEW.surname = people.surname);
    NEW.uname := NEW.name || NEW.surname || CASE u_num WHEN 0 THEN '' ELSE u_num::text END;
    RETURN NEW;
        END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER create_people BEFORE INSERT ON people
    FOR EACH ROW EXECUTE PROCEDURE create_people();


DISCLAIMER: As mentioned in the comments you should carefully plan on how to handle the possible deletes and updates on uname before using a solution based on this trigger on any production environment.