Capture which key Unique was raped

Question

I have a function that does insert in a table. This table has two columns that are unique email and login. In other tables there are also unique Keys compostas. Thus, an exception of the type UNIQUE_VIOLATION code: 23505 can happen if repeated data is attempted into any of the columns that are restricted by unique Keys.

Question:

• How do I capture which of the unique Keys were raped, when there is more than one in the same table, type to return her name, or do some procedure based on that?

Answer 1

Since the version I am using is 9.5, the ideal solution is the command GET STACKED DIAGNOSTICS.

Example:

create table foo (a int not null, b int not null, unique(a), unique(b));
insert into foo (a,b) values(1,1);
insert into foo (a,b) values(2,2);
create function ffoo(x integer, y integer) returns void as $$
declare
    tabela text;
    restricao text;
begin
insert into foo (a,b) values(x,y);
exception when unique_violation then
    get stacked diagnostics tabela = TABLE_NAME,
                restricao = CONSTRAINT_NAME;
    raise notice 'tabela: % ; constraint: %', tabela, restricao;
end;
$$ language plpgsql;
select ffoo(2,3);
select ffoo(5,1);

Credits for Euler Taveira da Comunidade Postgresql Brasileira ([email protected])

Answer 2

When an error occurs, together with the code representing the type of the same, Postgresql also returns a text containing error message; in the case of Unique constraints, the actual text of the error message contains which Constraint was breached.

Answer 3

create table t (
    email text unique,
    login text unique
);

with d (email, login) as ( values
    ('[email protected]','fulano')
), e as (
    select
        exists (select 1 from t inner join d using(email)) as email,
        exists (select 1 from t inner join d using(login)) as login
), i as (
    insert into t (email, login)
    select email, login
    from d
    where not(select email or login from e)
    returning 'email inexistente' as email, 'login inexistente' as login
)
select
    case when email then 'violação de email único' else 'email inexistente' end as email,
    case when login then 'violação de login único' else 'login inexistente' end as login
from e
where email or login
union all
select email::text, login::text
from i

It may be easier to parse the error message if the driver used returns the message.