How to ignore case sensitive rows in PostgreSQL

Question

I know this question may duplicate but i really got confused since I'm new to PostgreSQL. I'm trying to store emails which must not be case sensitive. For example Johndoe@gmail.com must be identical with JoHnDoE@gmail.com in PostgreSQL but i can't find any solution. I know about lower() function, But i don't want use it.

Any solution? Or this is not possible at all in PostgreSQL ? I'm using pgAdmin 4

"*But i don't want use it*" - why? Please expand on your use case if you're throwing out perfectly fine options for some reason. — Bergi, Mar 04 '21 at 22:24

emi · Accepted Answer · 2021-03-04T23:23:43.383

3

Use Non deterministic collation (only PostgreSQL version >= 12):

From https://dba.stackexchange.com/questions/101294/how-to-create-postgres-db-with-case-insensitive-collation :

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

Edit

From https://stackoverflow.com/a/59101567/2928168 :

    CREATE COLLATION case_insensitive (
      provider = icu,
      locale = 'und-u-ks-level2',
      deterministic = false
    );

    CREATE TABLE names(
      first_name text,
      /* Example collation used in schema directly */
      last_name text COLLATE "case_insensitive",
    );

    insert into names values
      ('Anton','Egger'),
      ('Berta','egger'),
      ('Conrad','Egger');

    select * from names
      order by
        last_name,
        /* Example collation used only in some query */
        first_name collate case_insensitive;

edited Mar 04 '21 at 23:23

answered Mar 04 '21 at 22:22

emi

2,786
1
16
24

But how to enable it for specific column? like as i said, "email" field. Explain please – Aqilhex Mar 04 '21 at 23:16
Added example . – emi Mar 04 '21 at 23:24
Thanks for your help. Appreciate – Aqilhex Mar 04 '21 at 23:32
How to combine both of them (unaccent + case insensitive) and which index to use for both sided LIKE queries, please? thanks! – Lunack Mar 29 '22 at 13:13
@Lunack Your question is different than the posted here, as the author wanted to declare the field this way, not only use it in a comparison. You should [ask a new question](https://stackoverflow.com/questions/ask). – emi Mar 30 '22 at 06:50

score 0 · Answer 2 · answered Mar 04 '21 at 21:57

0

Use upper() in both members of the comparison.

answered Mar 04 '21 at 21:57

emi

2,786
1
16
24

I don't want use function at all but thanks. – Aqilhex Mar 04 '21 at 22:06

score 0 · Answer 3 · answered Mar 04 '21 at 22:12

0

Use ILIKE operator (or its equivalent ~~* for ILIKE and !~~* for NOT ILIKE).

answered Mar 04 '21 at 22:12

emi

2,786
1
16
24

I don't want use function at all but thanks – Aqilhex Mar 04 '21 at 22:15
1

This is not a function. – emi Mar 04 '21 at 22:15
If you post an example of the query you are trying to use, I can make an example with it. – emi Mar 04 '21 at 22:15
Yes but i mean " Is there any data type like MySQL " which ignore case sensitive ? – Aqilhex Mar 04 '21 at 22:16
Ahhh, you mean how to declare a field as case insensitive? – emi Mar 04 '21 at 22:16
Yeaaaaah that's what i mean but because i'm confused and it's late here...How to do it? – Aqilhex Mar 04 '21 at 22:19
1

@AqilHex there is: https://www.postgresql.org/docs/current/citext.html – Bergi Mar 04 '21 at 22:26

How to ignore case sensitive rows in PostgreSQL

3 Answers3

Edit

Linked