Postgres insert unique column ignoring accent and case

Question

Problemn

I have a unique column in the table, and it's work when i do:

insert ... name = funcionario... save

insert ... name = funcionario... duplicate error

but I would like it to do some equalities that it doesn't, like:

funcionário == funcionario (ignoring accents) == Funcionario (case insensitive)

if they are equals, do not insert

What the best form to do that?

COLLATE Latin1_General_CI_AI;
First do SELECT ... after, if not find equals, INSERT...;
Lower in insertion;
Create another column with normalized names and use them to check;

The last option requires twice as much disk space, but is faster; The lower; The comparison with lower I saw that it ignores the indexes, so it is much slower; COLLATE I don't know the computational complexity;

What the best form to do that? Is there another option?

Take a look into Mike Sherrill 'Cat Recall' answer at https://stackoverflow.com/questions/7005302/how-to-make-case-insensitive-query-in-postgresql. I like citext approach — Julius Tuskenis, Feb 21 '23 at 06:58
hi @JuliusTuskenis i had seen this answer, it's good for the case insencitive, but not for accents, right? — Roberto Alves Neto, Feb 21 '23 at 17:38

score 0 · Answer 1 · answered Feb 22 '23 at 00:39

Answer

I found the answer that best suits my problem.

Accents are difficult to handle in my language, so i create new column name_slug and this column is the real unique.

I do trigger function to run before my insert and update, this function get name, remove accents and do lowercase.

links that helped me:

POSTGRES:
- https://www.postgresql.org/docs/current/plpgsql-trigger.html
TypeORM (typescript):
- Efficiently replace all accented characters in a string?
- https://snyk.io/advisor/npm-package/typeorm/functions/typeorm.BeforeInsert

Code

// typeORM

@BeforeInsert()
@BeforeUpdate()
validate(): void {
  this.name_slug = Latinise.transform(this.name);
}

// Function
type LatiniseMap = { [key: string]: string };

export class Latinise {
  static latinMap: LatiniseMap = {
    Á: 'A',
    Ă: 'A',
    Ắ: 'A',
    Ặ: 'A',
    Ằ: 'A',
    ...
}

  static transform(text: string): string {
    return text.replace(/[^A-Za-z0-9\[\] ]/g, function (match: string): string {
      return Latinise.latinMap[match];
    });
  }

  static isLatin(text: string): boolean {
    return text == this.transform(text);
  }
}