PostgreSQL case sensitivity sorting

Create the following table in PostgreSQL and insert the following data:

Create table "TTT "(

ID varchar (32) primary key not null,

NAME varchar (128) NOT NULL

);

Create unique index TTT_NAME_IDX ON "TTT" (NAME );

Insert into "TTT" values ('1', 'A ');

Insert into "TTT" values ('2', 'A ');

Insert into "TTT" values ('3', 'B ');

Insert into "TTT" values ('4', 'B ');

If you execute

Select * from "TTT" order by NAME;

The following result is displayed:

Id | name

---------- + ------------

2 |

1 |

4 | B

3 | B

(4 rows)

Here we expect the name to be sorted first by uppercase letters and then by lowercase letters.

 

After checking the information, you can solve the preceding sorting problem through the following operations:

1. First, back up the database configuration file and data file (preferably export data). By default, it is all the data in the/var/lib/pgsql/data directory.

2. $ sudo/etc/init. d/postgresql stop

3. $ sudo su-postgres

# Initdb -- lc-collate = C

4. $ sudo/etc/init. d/postgresql start

5. Execute the following query again

Select * from "TTT" order by NAME;

The result is as follows:

Id | name

---- + ------

1 |

3 | B

2 |

4 | B

(4 rows)