Lw7VtWv4

1# su postgres
2$ psql
3psql (10.1)
4Type "help" for help.
5
6=# create table users (
7  id serial primary key,
8  email text not null unique
9);
10CREATE TABLE
11
12=# insert into users (email) values ('USER@example.com');
13INSERT 0 1
14
15=# CREATE EXTENSION IF NOT EXISTS citext;
16CREATE EXTENSION
17
18=# ALTER TABLE users ALTER COLUMN email TYPE citext;
19	
20# select * from users where email = 'user@example.com';
21 id |      email
22----+------------------
23  1 | USER@example.com
24(1 row)
25	
26select * from users where email like lower('user@example.com');
27 id |      email
28----+------------------
29  1 | USER@example.com
30(1 row)
31	
32# select * from users where lower(email) = 'user@example.com';
33 id |      email
34----+------------------
35  1 | USER@example.com
36(1 row)
37	
38# select * from users where lower(email) = lower('user@example.com');
39 id |      email
40----+------------------
41  1 | USER@example.com
42(1 row)
43	
44# select * from users where email = lower('user@example.com');
45 id | email
46----+-------
47(0 rows)
48	
49select * from users where lower(email) = lower(lower('user@example.com'));
50 id |      email
51----+------------------
52  1 | USER@example.com
53(1 row)
54	
55# SHOW LC_CTYPE;
56  lc_ctype
57-------------
58 en_US.UTF-8
59(1 row)