0

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?

3
  • Possible, of course, but you would have to define how to deal with deletes and updates. More importantly: I would stick with name and surname in combination with the serial id and drop the redundant column uname, which adds nothing but pain. Commented Mar 7, 2015 at 22:35
  • @ErwinBrandstetter I want to have an human readable identifier, that's the reason to use uname. If I stick with name, surname and id, and remove uname, I'll end up with an identifier like JohnDoe519348, which is not so readable.
    – José Luis
    Commented Mar 8, 2015 at 15:06
  • 1
    I still wouldn't do it. The best solution (if you must) depends on the exact setup. In your definition both name and surname can be NULL, which doesn't make sense. Is white space allowed ('Sue Allen')? How to deal with it? Is uname supposed to be case-sensitive? Are digits allowed in name and surname? If not, add a CHECK constraint to disallow or it will break some day. Are rows updated or deleted? You'll end up with JohnDoe7 and no other JohnDoe. Concurrent access and race conditions are a problem, too. This can go wrong in many ways. Simo's function is bound to break ... Commented Mar 8, 2015 at 17:45

2 Answers 2

1

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.

2
  • As Erwin mentioned in comments to the question, this will break easily. Insert three John Does, remove the first, insert a fourth one. You have two JohnDoe2:s, unless the column has a unique constraint, in which case you can never insert a fourth John Doe unless you "fix" the others first. Commented Mar 13, 2015 at 15:08
  • Ok, thanks for the comment, added a disclaimer. Commented Mar 13, 2015 at 16:35
0

I have applied the following solution.

drop trigger if exists unique_username on users;
drop function if exists unique_username();
drop table if exists users;

create table users (
  id serial primary key,
  username varchar unique,
  name varchar not null,
  surname varchar not null,
  check (username is not null and username != '')
);

create or replace function unique_username() returns trigger as
$$
declare
  unique_username varchar;
  i integer;
begin
  unique_username := coalesce(new.username, LOWER(CONCAT(new.name, new.surname)));
  unique_username := TRANSLATE(unique_username, 'çğıöşü', 'cgiosu');
  new.username = unique_username;
  i := 1;
  while exists(select * from users where username = new.username) loop
    new.username = TRANSLATE(unique_username, 'çğıöşü', 'cgiosu') || i;
    i := i + 1;
  end loop;
  return new;
end
$$ language plpgsql;

create trigger unique_username
before insert on users
for each row execute procedure unique_username();

insert into users(name,surname) values ('mehmet','seven'), ('mehmet', 'seven');
insert into users(username, name, surname) values ('mehmetseven', 'mehmet', 'seven');
1
  • please ealborate what you have done, so that other can use that knowledge
    – nbk
    Commented Jan 14, 2021 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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