Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the impact of PostgreSQL localization settings on SQL features". In daily operation, I believe many people have doubts about the impact of PostgreSQL localization settings on SQL features. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the impact of PostgreSQL localization settings on SQL features?" Next, please follow the editor to study!

PostgreSQL provides a localized parameter locale when initializing the database with initdb. If this parameter is not specified, it is empty by default, even if it is set by OS's locale.

Localization settings affect the following SQL properties:

1. Sort and compare operations: Sort order in queries using ORDER BY or the standard comparison operators on textual data

two。 Built-in functions: The upper, lower, and initcap functions

3. Pattern matching: Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions

4.to_char related function: The to_char family of functions

Can 5.LIKE use the index: The ability to use indexes with LIKE clauses

Sort

The same data is output differently using different LC_COLLATE,SQL:

Postgres=# SELECT name FROM unnest (ARRAY ['MYNAME',' my_name', 'my-image.jpg',' my-third-image.jpg']) name ORDER BY name collate "C" Name-my_name MYNAME my-image.jpg my-third-image.jpg (4 rows) postgres=# SELECT name FROM unnest (ARRAY ['MYNAME',' my_name', 'my-image.jpg',' my-third-image.jpg']) name ORDER BY name collate "zh_CN" Name-my-image.jpg my_name MYNAME my-third-image.jpg (4 rows)

If collate is specified as "C", the default binary ASCII code value of the string is used for comparison, while specifying zh_CN is not.

The behavior of using zh_CN is treated as case-insensitive

Postgres=# SELECT name FROM unnest (ARRAY ['MYNAME1',' my_name2', 'my-image.jpg',' my-third-image.jpg']) name ORDER BY name collate "zh_CN" Name-my-image.jpg MYNAME1 my_name2 my-third-image.jpg (4 rows) postgres=# SELECT name FROM unnest (ARRAY ['myname1',' myname2', 'myimage.jpg',' mythirdimage.jpg']) name ORDER BY name collate "zh_CN"; name-myimage.jpg myname1 myname2 mythirdimage.jpg (4 rows)

The explanation in the mailing list is as follows:

The behavior of each collation comes from the operating system's own

Libc, except for the C collation, which is based on the ordering

Implied by strcmp () comparisons. Generally, most implementations have

The behavior you describe, in that they assign least weight of all to

Caseness and whitespace, and somewhat more weight to punctuation. I

Don't think that there is much that can be done about it in practice

Though in principal there could be a collation that has all the

Properties you want.

Built-in function

Initcap, for example, will be different under French and C.

Postgres=# select initcap ('é Lys é e 'collate "C"); initcap-é Lys é E (1 row) postgres=# select initcap (' é Lys é e 'collate "fr_FR"); initcap-É Lys é e (1 row)

In the Chinese context, lowercase letters of full-width characters are converted to full-width uppercase letters

Postgres=# select initcap ('a 'collate "zh_CN"); initcap-A (1 row) postgres=# select initcap (' a 'collate "C"); initcap-a (1 row)

Under LC_COLLATE, only ASCII characters below 7F will take effect, and other characters will not.

Pattern matching

Postgres=# select'é lys é e'~'^\ column?-f (1 row) collate "fr_FR";? column?-t (1 row) postgres=# select'é lys é e 'COLLATE "C" ~' ^\ wigs

Can LIKE use the index?

Postgres=# CREATE TABLE t_sort (postgres (# a text COLLATE "zh_CN", postgres (# b text COLLATE "C"); CREATE TABLEpostgres=# postgres=# INSERT INTO t_sort SELECT md5 (n::text), md5 (n::text) postgres-# FROM generate_series (1, 1000000) n; INSERT 0 1000000postgres=# CREATE INDEX ON t_sort USING btree (a); CREATE INDEXpostgres=# CREATE INDEX ON t_sort USING btree (b); CREATE INDEXpostgres=# ANALYZE tasking sorter and ANALYZEpostgresses # SELECT * FROM t_sort LIMIT 2 A | b-+-c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c (2 rows) postgres=# explain SELECT * FROM t_sort WHERE a LIKE 'c4ca4238a0%' QUERY PLAN-Gather (cost=1000.00..18564.33 Rows=100 width=66) Workers Planned: 2-> Parallel Seq Scan on t_sort (cost=0.00..17554.33 rows=42 width=66) Filter: (a ~ 'c4ca4238a0%'::text) (4 rows) postgres=# explain SELECT * FROM t_sort WHERE b LIKE' c4ca4238a0%' QUERY PLAN-Index Scan using t_sort _ b_idx on t_sort (cost=0.42..8.45 rows=100 width=66) Index Cond: ((b > = 'c4ca4238a0'::text) AND (b <' c4ca4238a1'::text)) Filter: (b ~ 'c4ca4238a0%'::text) (3 rows)

Indexes cannot be used with zh_CN, but indexes can be used with C

At this point, the study on "what is the impact of PostgreSQL localization settings on SQL features" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.