言成言成啊 | Kit Chen's Blog

CentOS7安装PostgreSQL11

发布于2022-01-04 20:08:49，更新于2024-12-21 20:14:09，标签：devops sql  文章会持续修订，转载请注明来源地址：https://meethigher.top/blog

简单记录一下，在CentOS7上面安装PostgreSQL11与postgis3的操作。

所有的操作都是关闭防火墙后进行的，不然远程访问可能存在问题。

参考

  1. PostgreSQL: Linux downloads (Red Hat family)
  2. CentOS安装PostgreSQL - 玄同太子 - 博客园
  3. CentOS7上为PostgreSQL11安装PostGIS - abce - 博客园
  4. org.postgresql.util.PSQLException: ERROR: type “geometry“ does not exist_安小然然的博客-CSDN博客
  5. PostgreSQL 11.2 手册

一、Postgresql

1.1 安装

安装

1
2
3
4
5
6
7
8
9
10
# 安装 RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装 PostgreSQL:
sudo yum install -y postgresql11-server

# 初始化数据库并设置开机自启动
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

1.2 命令行

1.2.1 版本

查看postgresql版本

1
select version();

1.2.2 登录并创建用户

切换用户，管理PostgreSQL

1
su - postgres

登录数据库

1
psql

获取帮助

1
2
3
4
5
6
7
8
9
# 获取所有帮助
help
# 获取sql命令帮助
\h
# 获取查询命令帮助
# 如查所有库\l，查当前库下模式\dn
\?
# 切换数据库
\connect postgres

更新密码

1
alter user postgres with encrypted password '1';

创建用户并授权

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建用户
create user pgrouting with password 'meethigher';  
-- 创建数据库
create database pgrouting_test owner pgrouting;   
-- 授权
grant all privileges on database pgrouting_test to pgrouting; 
-- 查看当前数据库下所有的表
select tablename from pg_tables where schemaname='public'
-- 查看指定表vehicle的表的结构
\d vehicle
-- 修改表vehicle的类型
alter table vehicle alter driver_name type text;

退出

1
exit

开启远程访问

1
2
3
4
5
6
7
vi /var/lib/pgsql/11/data/postgresql.conf
#下面是修改内容，*表示允许所有，默认是localhost
listen_addresses='*'

vi /var/lib/pgsql/11/data/pg_hba.conf
#下面是添加内容
host all all 0.0.0.0/0 md5

切换到root用户，重启postgresql

1
sudo systemctl restart postgresql-11

1.2.3 释放连接

下面记录一下，postgresql释放连接的操作，用于有时候执行时间过长的连接释放。

1
2
3
4
5
6
7
8
-- 查看最大连接数
show max_connections;
-- 查看配置文件路径
show config_file;
-- 查看所有连接用户
select * from pg_stat_activity;
-- 释放使用pgrouting_test数据库的连接
select pg_terminate_backend(pid) from pg_stat_activity where datname='pgrouting_test';

1.2.4 配置模式search_path

创建库、创建模式、设置搜索路径

1
2
3
4
5
6
7
8
9
10
# 创建库
create database aaa;
# 删除模式
drop schema if exists aaa cascade;
# 创建模式
create schema postgis;
# 查询并设置搜索路径
#(这样就可以不用指定模式，比如搜索时，select from tableA，既可以查public.tableA和postgis.tableA)
show search_path;
alter database "aaa" set search_path = public,postgis;

1.2.5 导入/导出sql文件

1
2
3
4
5
# 导入
psql -d pgrouting_test -h 127.0.0.1 -p 5432 -U pgrouting -f /home/sql/test.sql

# 导出
pg_dump -h 127.0.0.1 -p 5432 -U pgrouting -f /home/sql/test.sql pgrouting_test

若存在类似于

pg_dump: [归档 (db)] 与数据库 “bug-test” 联接失败: 致命错误: 用户 “postgres” Ident 认证失败

则需要检查下/var/lib/pgsql/11/data/pg_hba.conf中的配置，如果只对127.0.0.1启用了ident，则换用ip即可

1.2.6 导入/导出shp数据

1.) 命令行操作，推荐

1
2
3
4
5
6
7
# 前提是先安装yum -y install postgis30_11-client，参照博客一键脚本，此处不多赘述
# 导出shp
pgsql2shp --help
pgsql2shp -h 10.0.0.10 -p 5432 -P 密码 -u 用户 -g geometry字段 库名 表名;
# 导入shp
shp2pgsql --help
shp2pgsql -c newtable.shp 表名| psql -h 10.0.0.10 -U 用户 -d 库名 -p 5432

2.) windows上的postgis shapefile import/export manager，原理同上

1.2.7 导入/导出csv数据

0.) navicat/dbeaver，支持无脑导入导出csv、xlsx、xml、json、mdb，并支持自动建表，但是缺陷也有，不如命令行导出的自由性强大

1.) psql导入/导出，导入需提前建表

PostgreSQL导入导出CSV_萬手哥的博客-CSDN博客_postgresql导入csv

1
2
3
4
5
6
# 导入 copy from 
PGPASSWORD=123456 psql -h 127.0.0.1 -p 5432 -d db_name -U postgres -c "\copy tab_name FROM './test.csv' WITH csv header delimiter ',' encoding 'UTF8'";
# 指定字段
PGPASSWORD=123456 psql -h 127.0.0.1 -p 5432 -d db_name -U postgres -c "\copy tab_name(id,name,age) FROM './test.csv' WITH csv header delimiter ',' encoding 'UTF8'";
# 导出 copy to
PGPASSWORD=123456 psql -h 127.0.0.1 -p 5432 -d db_name -U postgres -c "\copy (SELECT * FROM tab_name ) to './test.csv' WITH csv header delimiter ',' encoding 'UTF8'";

若存在类似于

pg_dump: [归档 (db)] 与数据库 “bug-test” 联接失败: 致命错误: 用户 “postgres” Ident 认证失败

则需要检查下/var/lib/pgsql/11/data/pg_hba.conf中的配置，如果只对127.0.0.1启用了ident，则换用ip即可

2.) pgfutter导入，自动建表。

存在bug，比如数据多一个空行

lukasmartinelli/pgfutter: Import CSV and JSON into PostgreSQL the easy way

1
2
3
4
# 查看帮助
./pgfutter --help
# 导入csv数据到test_gg表，并自动建表
./pgfutter --host 192.168.110.198 --username postgres --pass postgres --dbname sh_map --schema public  --table test_gg csv sh_subway_station84.csv

1.2.8 清空数据库表

1
2
3
4
5
6
7
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'drop TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

1.2.9 索引及大小相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 开启顺序扫描
set enable_seqscan=true
-- 开启索引扫描
set enable_indexscan=true
-- 查看执行计划
explain(analyze,buffers) select count(0) from tbb_feature where layerid='183041b7dae21000'

-- 查询postgersql中某张表中各索引的大小
select indexrelname,indexrelid,pg_relation_size(indexrelid) as size,pg_size_pretty(pg_relation_size(indexrelid)) as pretty_size from pg_stat_user_indexes where schemaname='public' and relname='test_data';

-- 查询表内总数据大小
select pg_table_size('test_data'),pg_size_pretty(pg_table_size('test_data'));

-- 查询表内总索引大小
select pg_indexes_size('test_data'),pg_size_pretty(pg_indexes_size('test_data'));

-- 查询表的总大小，包含数据和索引
select pg_total_relation_size('test_data'),pg_size_pretty(pg_total_relation_size('test_data'));

1.2.10 日志配置

配置文件为/var/lib/pgsql/11/data/postgresql.conf，若没有，也可直接执行systemctl status postgresql-11查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 开启日志
logging_collector = on                  

# 日志存储的文件夹
log_directory = 'log'                   

# %a表示范围为周一到周日，这样就能实现，不管日志再多，只会有7个。之后就会被覆盖                                         
log_filename = 'postgresql-%a.log'  

# 0600 仅用户具有读写权限                                   
#log_file_mode = 0600                   

# 开启归档
log_truncate_on_rotation = on           

# 一天归档一次
log_rotation_age = 1d

# 不限制大小
log_rotation_size = 0

1.2.11 中文排序

内置函数

按照拼音的首字母排序，比如武汉、武汉庭、武昌、武侠。

按照拼音首字母排序后的结果就是

  • 武昌：wc
  • 武汉：wh
  • 武汉厅：wht
  • 武侠：wx
1
select name from test order by convert_to(name, 'GBK') asc;

自定义函数

自定义函数本质上实现了与GBK编码下的中文排序规则，但是对于非中文支持并不友好。

创建函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
 
CREATE OR REPLACE FUNCTION "zh_cn"("ahzstr" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
 
declare
  sresult varchar ;
    
begin
  with
    hzlb as(
      --传入参数列转行 
      select  
        regexp_split_to_table(ahzstr,'') as hzstr
    )
   ,asclb as(
      --
      select  
         hzstr
        ,ascii(hzstr) as asicchz
        from hzlb 
    )
   ,hzpdlb as (
      --根据字节数判定首字母
      select
        case when asicchz in (21834,38165,21956,21526,33100,38463) then 'a'
             when asicchz in (21696,21710,21769,21964,22003,22467,23250,25384,25457,26279,29233,29815,30284,30353,30702,30777,30861,33406,34108,38207,38552,38701)then 'ai'
             when asicchz in (20474,22511,23433,23736,24245,25353,25566,26263,26696,26697,27688,29364,33018,35865,38133,38797,40524,40687)then 'an'
             when asicchz in (26114,30414,32942)then 'ang'
             when asicchz in (20658,20985,22007,22387,22885,23210,23705,24274,25034,25303,25942,28595,29100,29522,32753,32881,34735,34948,36968,37834,37846,39580,40140)then 'ao'
             when asicchz in (20843,21485,21543,22365,23708,24052,25170,25226,25300,25420,28766,29240,30116,31494,31889,32610,32793,33453,33543,33757,36299,38063,38712,38774,39747,40069)then 'ba'
             when asicchz in (20336,25308,25453,25520,25670,26575,30333,30334,31255,36133)then 'bai'
             when asicchz in (20276,21150,21322,22338,25198,25203,25292,25644,26001,26495,29256,29677,29923,30242,30285,32458,33320,33324,38051,38442,39041)then 'ban'
             when asicchz in (20621,24110,26758,26834,27036,27996,30917,32465,33152,33953,34444,35876,37030,38225)then 'bang'
             when asicchz in (20445,21093,21241,21253,22561,23394,23453,25253,25265,26292,26333,28689,28846,29042,29190,32990,33502,33862,34180,35090,35091,35961,36277,38649,39281,40077,40488,40837)then 'bao'
             when asicchz in (20493,21271,21329,21591,22791,23387,24726,24754,24811,26479,28953,29384,30865,30874,32972,34003,34987,35097,36125,36744,37046,37950,38049,38466,38836,40526)then 'bei'
             when asicchz in (22348,22831,22868,26412,30042,31528,33519,38171)then 'ben'
             when asicchz in (22051,23849,27893,29967,29997,32503,36454,36856)then 'beng'
             when asicchz in (20478,21269,21537,21716,22721,22947,23138,23318,24065,24199,24243,24330,24380,24444,24517,24846,25949,26610,27604,27605,27606,27609,28375,28638,29428,29863,30016,30201,30565,30887,31189,31508,31578,31621,31718,33218,33325,33432,33628,33656,33798,34006,34109,34204,35048,35166,36146,36344,36767,36924,36991,37145,38091,38381,38491,39616,40763)then 'bi'
             when asicchz in (20415,21310,21342,21464,24321,24557,25153,27764,29048,30765,30885,31366,31550,32527,32534,33476,34649,35082,36140,36776,36777,36779,36793,36941,38829,40138)then 'bian'
             when asicchz in (23114,24426,26631,30253,33176,34920,35057,38230,38259,39121,39129,39130,39584,40148)then 'biao'
             when asicchz in (21035,24971,30250,36457,40150)then 'bie'
             when asicchz in (20647,23486,24428,25672,25996,27103,27553,28392,28626,29602,32548,33169,35955,38228,39628,39699)then 'bin'
             when asicchz in (19993,20853,20912,23631,24182,25682,26564,28851,30149,31104,31177,37044,39292)then 'bing'
             when asicchz in (20147,20271,21187,21338,21877,24091,25320,25615,25773,25816,27287,27850,27874,28001,28196,29627,31028,31636,31800,33046,33162,33334,33760,36315,36387,38069,38073,38082,39293,39539,40513)then 'bo'
             when asicchz in (19981,21340,21343,21754,22484,22496,24067,24598,25429,26209,27493,29951,31807,34917,36875,37096,37293,38042,38072)then 'bu'
             when asicchz in (22163,25830,31012)then 'ca'
             when asicchz in (24425,25165,26448,29468,30572,33756,34081,35009,36130,36393,37319)then 'cai'
             when asicchz in (21442,24808,24813,27531,28799,29864,31922,34453,39184,39574,40682)then 'can'
             when asicchz in (20179,20263,27815,33329,33485,34255)then 'cang'
             when asicchz in (22024,25805,26361,27133,28437,31961,33370,33609,34732)then 'cao'
             when asicchz in (20391,20876,21397,24699,27979,31574)then 'ce'
             when asicchz in (23697,28052)then 'cen'
             when asicchz in (22092,23618,26366,36461)then 'ceng'
             when asicchz in (21049,21449,21939,23033,23519,23700,24046,25554,25661,26440,26597,26946,27086,27307,27722,29497,30900,33580,33590,34921,35815,38200,38258,39303)then 'cha'
             when asicchz in (20394,25286,26612,30245,34431,35962,38039)then 'chai'
             when asicchz in (20135,20865,23157,23409,24283,24527,25530,25600,28538,28598,31109,32544,32700,33927,34633,34814,35271,35844,35863,36500,38130,38416,39076,39307,39587)then 'chan'
             when asicchz in (20261,20513,20607,21378,21809,22330,23100,23270,23581,24120,24476,24581,24797,25950,26124,26166,27653,29462,30021,32928,33484,33750,35059,38271,38410,39727,40115)then 'chang'
             when asicchz in (21119,21557,22066,24034,24586,25220,26177,26397,28526,28818,28975,32790,36229,38046)then 'chao'
             when asicchz in (22396,24443,25199,25507,25764,28552,30743,36710)then 'che'
             when asicchz in (21972,23480,23576,24561,25275,26216,27015,27784,27785,29723,30876,33251,34924,35852,35894,36225,36784,37108,38472,40832)then 'chen'
             when asicchz in (19998,20056,21576,22478,22485,22605,24809,25104,25215,25745,26536,26621,27225,28548,29685,29732,30624,31204,31216,31243,34511,35022,35802,36894,37234,38102,38107,39563)then 'cheng'
             when asicchz in (20360,20666,21273,21489,21507,21735,21883,21988,22656,23224,23610,24347,24435,25345,25941,26021,27744,28861,30196,30235,30517,31518,31722,32709,32827,33548,34473,34733,35115,35913,36196,36383,36831,39276,39536,39761,40497,40831)then 'chi'
             when asicchz in (20805,20914,23456,23815,24545,24999,33282,33375,33594,34411,37325,38131)then 'chong'
             when asicchz in (19985,20167,20454,24113,24774,24833,25277,30068,30259,30597,31264,31609,32504,33261,36364,37228,38624)then 'chou'
             when asicchz in (20109,20648,20986,21005,21021,21416,22788,24629,25015,25616,26485,26970,26990,27159,27249,28353,30044,30679,30784,32460,34573,35098,35302,36464,36487,38148,38500,38607,40668)then 'chu'
             when asicchz in (22060,25571,25611,33194,36409)then 'chuai'
             when asicchz in (20018,20256,21912,24029,26941,27674,31359,33307,33313,33337,36932,38031)then 'chuan'
             when asicchz in (21019,24162,24202,24582,30126,31383,38383)then 'chuang'
             when asicchz in (21561,22402,25462,26864,26894,27084,28810,38180,38514)then 'chui'
             when asicchz in (21767,26149,26943,28147,32431,33724,34685,34850,37255,40529)then 'chun'
             when asicchz in (21852,25139,32496,36372,36749,40842)then 'chuo'
             when asicchz in (21050,24904,27425,27492,29943,30133,30913,31072,31949,33544,33576,35789,36176,36766,38604,40538)then 'ci'
             when asicchz in (19995,20174,21254,22257,26526,28121,29742,29825,32874,33473,33905,39586)then 'cong'
             when asicchz in (20945,26993,33120,36751)then 'cou'
             when asicchz in (20419,21330,24450,27522,29469,31751,31895,34079,36441,36468,37218,37259)then 'cu'
             when asicchz in (25786,25874,27718,29224,31388,31713,36479,38249)then 'cuan'
             when asicchz in (20652,21840,23828,24756,25703,27057,27635,28140,29824,30209,31929,32736,33030,33795,34928)then 'cui'
             when asicchz in (23384,23544,24534,26449,30388)then 'cun'
             when asicchz in (21405,23919,25387,25514,25619,25774,30180,30700,30923,33054,36425,38153,38169,40574)then 'cuo'
             when asicchz in (21714,21970,22231,22823,22962,24603,25171,25645,27795,30249,31530,31572,32823,35105,36798,38780,38801)then 'da'
             when asicchz in (20195,20643,21574,21588,22509,23729,24102,24453,24608,25140,27513,27526,29619,29977,32464,34955,36151,36840,36910,40667)then 'dai'
             when asicchz in (20025,20294,20747,20988,21333,21846,23445,24377,24814,25285,25528,26086,27546,27694,28129,28601,30136,30213,30472,30707,31658,32829,32835,32966,33770,33807,34507,35806,36181,37112)then 'dan'
             when asicchz in (20826,24403,25377,26723,30720,33633,35014,35872,38107)then 'dang'
             when asicchz in (20498,20992,21040,21480,23548,23707,24764,25443,27672,28952,30423,31095,31291,32411,36424,36947)then 'dao'
             when asicchz in (24471,24503,30340,38173)then 'de'
             when asicchz in (20979,22100,23965,25125,28783,30331,30634,30964,31561,31782,36460,37011,38251)then 'deng'
             when asicchz in (20302,22016,22320,22395,22564,23075,23265,24093,24213,24351,25269,25932,26594,26851,27664,28068,28404,29380,30535,30757,30898,31063,31515,31532,31860,32532,32669,32735,33659,33922,35276,35787,35867,36842,36882,37048,38237,39606)then 'di'
             when asicchz in (22002)then 'dia'
             when asicchz in (20022,20291,20856,22379,22443,22880,24005,24215,24806,25474,27583,28096,28359,28857,29623,30005,30008,30300,30315,30872,31775,36398,38079,38461,38747,39072)then 'dian'
             when asicchz in (20939,20993,21500,21514,25481,30857,35843,35970,38035,38110,38613,40119)then 'diao'
             when asicchz in (21472,21899,22436,22558,25586,29241,29266,29918,30879,32779,34678,35853,36300,36845,40125)then 'die'
             when asicchz in (19969,20163,21486,21878,23450,29582,30010,30100,30447,30855,32821,33114,35746,37194,38025,38189,39030,40718)then 'ding'
             when asicchz in (20002,38117)then 'diu'
             when asicchz in (19996,20375,20908,20923,21160,21658,22412,23741,23762,24683,25026,26635,27681,27934,30800,33000,33012,33891,40491)then 'dong'
             when asicchz in (20828,25238,26007,30168,31398,31740,34104,34474,35910,36887,37117,38061,38497)then 'dou'
             when asicchz in (22047,22581,22930,24230,26460,26911,27602,28174,28193,29261,29322,29420,30563,30585,31491,32922,33423,34873,35835,36172,38208,39633,40681)then 'du'
             when asicchz in (26029,26932,27573,28997,30701,31471,31766,32526,38203)then 'duan'
             when asicchz in (20817,22534,23545,24636,24989,30867,38246,38431)then 'dui'
             when asicchz in (21544,22244,22697,25958,27788,28822,30457,30462,30744,30981,36280,36466,36929,38045,39039)then 'dun'
             when asicchz in (21057,21636,21702,21722,22427,22549,22810,22842,24816,25479,26421,27826,32525,33333,35056,36346,36401,36530,38094)then 'duo'
             when asicchz in (20420,21380,21571,21734,22121,22441,23077,23104,23641,23784,24694,24853,25212,33133,33482,33706,33852,34558,35769,35860,36717,36943,37122,38151,38199,38463,39066,39069,39295,40132,40517,40535)then 'e'
             when asicchz in (35830)then 'ei'
             when asicchz in (21999,24681,25665,33981)then 'en'
             when asicchz in (20108,20799,23572,27953,29669,32780,32819,36144,36841,38098,39285,40085,40504)then 'er'
             when asicchz in (20047,20240,21457,22433,27861,29648,30749,31567,32602,38400)then 'fa'
             when asicchz in (20961,21453,22786,24070,24161,25909,26805,27146,27867,28735,28902,29140,29359,30024,30058,30718,32321,32763,33539,34115,34281,34345,36137,36463,36820,38034,39277)then 'fan'
             when asicchz in (20223,22346,22952,25151,25918,26041,26507,32442,32938,33323,33459,35775,37025,38059,38450,40066)then 'fang'
             when asicchz in (21290,21536,21857,22915,24223,24753,25161,26000,27047,27832,28125,29394,30193,30761,31706,32495,32737,32933,32954,33107,33778,34586,35837,36153,38212,38671,38750,39134,40113)then 'fei'
             when asicchz in (20221,20606,20998,21545,22367,22859,24575,24868,26876,27675,27774,28725,28954,31881,31914,32439,33452,37210,40124,40738)then 'fen'
             when asicchz in (20016,20472,20911,20964,21802,22857,23553,23792,26539,27811,28925,30127,30748,32541,33873,34562,35773,36898,37190,38155,39118)then 'feng'
             when asicchz in (20315)then 'fo'
             when asicchz in (21542,32566)then 'fou'
             when asicchz in (20184,20239,20440,20463,20613,20971,21103,21264,21579,21586,21648,22797,22827,22919,23386,23413,23500,24133,24158,24220,24343,24619,25206,25242,25282,25290,25975,26023,26381,26740,27679,28014,28074,28367,29238,29995,31059,31119,31235,31526,32450,32459,32538,32600,32932,33071,33104,33105,33145,33396,33433,33470,33531,33583,33670,33705,33748,34472,34569,34656,34670,34993,35206,35747,36127,36171,36185,36212,36282,36311,36741,36752,37083,37340,38428,38468,39333,39544,40075,40134,40632,40699,40700)then 'fu'
             when asicchz in (22030,22134,22419,22841,23573,23580,23596,26094,36711,38022)then 'ga'
             when asicchz in (19984,25124,25913,27010,28297,30422,33445,35813,36165,38041,38484)then 'gai'
             when asicchz in (22377,23604,24178,24863,25792,25954,26096,26438,26577,27204,27860,28134,28553,29976,30131,30712,31174,31487,32448,32925,33527,36195,36214,37200)then 'gan'
             when asicchz in (20872,21018,23703,25094,26464,28207,31611,32434,32568,32609,32923,38050)then 'gang'
             when asicchz in (21578,25630,26482,27073,27092,30347,30590,31295,31705,31957,32543,32660,33167,34241,35824,37084,38150,38224,39640)then 'gao'
             when asicchz in (20010,20193,21106,21508,21512,21679,21733,21759,21981,22314,22629,25096,25601,25663,26684,27468,30105,30796,32421,33011,33160,33336,33883,34428,34532,35004,38124,38217,38401,38548,38761,39052,39612,39730,40509)then 'ge'
             when asicchz in (32473)then 'gei'
             when asicchz in (20120,21711,26681,33390,33563,36319)then 'gen'
             when asicchz in (21757,22466,24218,26356,26775,32480,32697,32789,32831,36179,40096)then 'geng'
             when asicchz in (20379,20844,20849,21151,22632,23467,24037,24041,24339,24685,25329,25915,27742,29657,32945,34467,35301,36129,36524,40858)then 'gong'
             when asicchz in (20317,21246,22434,22815,23230,23715,24384,26500,26552,27807,29399,31537,31709,32529,33503,35279,35807,36141,36952,38057,38834)then 'gou'
             when asicchz in (20272,21476,21653,22031,22266,22993,23396,23854,25925,26767,27586,27753,27837,29295,29311,30204,30653,31629,32607,32929,33228,33735,33776,34500,34506,35290,35778,35895,36158,36721,36764,37220,38068,38178,38599,39038,39592,40116,40490,40516,40536,40723)then 'gu'
             when asicchz in (21038,21072,21350,21617,23521,25346,26653,29916,32973,35074,35798,40505)then 'gua'
             when asicchz in (20054,24618,25296,25524)then 'guai'
             when asicchz in (20492,20851,20896,23448,24815,25532,26874,28075,28748,30437,30684,31649,32438,32592,33694,35266,36143,39302,40143,40563)then 'guan'
             when asicchz in (20809,21667,24191,26692,29367,33009,36891)then 'guang'
             when asicchz in (21053,21055,21286,22317,22955,23428,24203,24402,26231,26588,26690,26727,28805,29808,30328,30344,30789,31755,35268,35809,36149,36330,36712,38394,39740,40081,40156,40863)then 'gui'
             when asicchz in (26829,28378,30937,32498,34926,36746,40103)then 'gun'
             when asicchz in (21593,22269,22490,23838,24124,26524,26881,29459,32850,34402,34622,34632,35065,36807,37101,38149,39320)then 'guo'
             when asicchz in (21704,34532,38122)then 'ha'
             when asicchz in (20133,21992,23401,23475,27686,28023,33010,37282,39559,39608)then 'hai'
             when asicchz in (20989,21547,21898,23506,24717,25000,25022,25421,25750,25788,26097,26199,27721,27735,27995,28085,28698,28938,28947,29364,32597,32752,33761,34486,37015,37039,37219,38426,38889,39032,39060,40766)then 'han'
             when asicchz in (21549,22831,26477,27782,32471,33322,34892,39043)then 'hang'
             when asicchz in (21495,21989,22150,22158,22741,22909,26122,27627,28009,28111,28640,28751,30355,32791,33983,34181,34461,35946,37085,39074)then 'hao'
             when asicchz in (20309,21182,21512,21621,21644,21917,21996,22737,26359,26680,27827,28088,30413,30418,31166,32750,33655,33743,34485,35088,35779,35977,36154,36203,38402,38422,39052,40548)then 'he'
             when asicchz in (22079,40657)then 'hei'
             when asicchz in (24456,24680,29408,30165)then 'hen'
             when asicchz in (20136,21756,24658,26689,27178,29673,34309,34913)then 'heng'
             when asicchz in (21700,23439,24344,27859,27946,28888,32418,33645,34171,34216,34425,35335,35751,36720,38387,40511,40649)then 'hong'
             when asicchz in (20399,20505,21402,21518,21564,21897,22560,24460,29492,30218,31692,31943,36869,39610,40078)then 'hou'
             when asicchz in (20046,20114,20913,21628,21804,21823,22251,22774,23733,24359,24573,24601,24794,25143,25149,25160,25252,26011,27122,27818,27986,28246,28409,28864,29043,29392,29474,29733,29786,29920,31068,31503,31946,32993,33899,34382,34676,35315,36727,37264,40516,40533,40536,40561)then 'hu'
             when asicchz in (21010,21270,21326,21719,26726,28369,29502,30011,30729,33457,35805,38119,39557)then 'hua'
             when asicchz in (22351,24458,24576,27088,28142,36381)then 'huai'
             when asicchz in (21796,22300,22850,23462,23536,24187,24739,25442,25808,26707,27426,27961,28003,28067,28470,28949,29566,29615,30186,32531,32563,33809,35938,36824,36909,37063,38206,39711,40105)then 'huan'
             when asicchz in (20976,24140,24488,24653,24822,24908,26179,28255,28514,29004,29852,30272,30343,30970,31681,31783,32915,33618,34647,34789,35854,36945,38541,40135,40644)then 'huang'
             when asicchz in (20250,21321,21684,21913,22238,24407,24509,24666,24674,24724,24800,24935,25381,26198,26214,26727,27585,27719,27908,28784,28905,29682,31229,32472,32523,33588,33631,34137,34426,34516,34794,35763,35801,35826,36159,36745,38579,40638)then 'hui'
             when asicchz in (23130,26127,27985,28151,28343,33636,35816,38413,39300,39746)then 'hun'
             when asicchz in (20249,21136,22191,22821,24785,25110,25865,27963,28779,31096,32800,33719,34303,34838,35905,36135,38060,38186,38252,38669)then 'huo'
             when asicchz in (20073,20127,20238,20342,20552,20864,20960,20987,21058,21086,21363,21450,21501,21513,21677,21724,21799,22334,22522,22716,22931,23020,23241,23395,23490,23492,23632,23692,23879,23924,24049,24524,24613,24760,25119,25122,25216,25380,25486,26082,26280,26426,26497,26840,26987,27547,27762,27918,27982,28608,29316,29585,30072,30079,30142,30240,30710,31085,31215,31287,31293,31492,31496,31637,31821,31995,32423,32426,32473,32487,32489,32521,32641,32908,33034,33448,33456,33632,33978,34015,34170,34414,35274,35745,35749,35760,36173,36347,36349,36753,36857,38469,38598,38657,39269,39569,39589,39675,40090,40107,40481,40578,40785)then 'ji'
             when asicchz in (20215,20285,20339,20551,21152,22025,22841,23233,23478,23724,24669,25115,26550,26551,27971,29640,30002,30146,30229,31292,31539,32987,33540,33626,33901,34545,34952,36158,36303,36838,37071,38078,38103,38227,39050,39550)then 'jia'
             when asicchz in (20214,20461,20581,20717,20860,20943,21073,21098,22237,22362,22904,23574,24314,25099,25132,25315,25441,25627,26535,26604,26816,26967,27099,27516,27645,28071,28176,28244,28293,29006,29294,29325,30417,30545,30839,30897,31509,31546,31616,31661,32516,32547,32742,32937,33137,33328,33392,33575,33616,33733,33977,35045,35265,35591,35855,35883,36145,36341,36410,36423,37492,38159,38190,38388,38831,39279,40099,40547)then 'jian'
             when asicchz in (20725,21280,22870,23004,23558,24378,26728,27743,27930,27974,29343,30086,30995,31976,32475,32560,32809,33587,33931,35762,35911,37233,38477,38739)then 'jiang'
             when asicchz in (20132,20348,20389,20716,21119,21483,22093,22204,23011,23047,23780,24508,25378,25605,25945,25963,26657,26898,27975,28267,28966,29409,30350,30699,30977,31382,32478,32564,33014,33050,33405,33581,34121,34527,35282,36324,36735,36739,37066,37237,37294,38128,39290,39556,40091,40554)then 'jiao'
             when asicchz in (20171,20511,21163,21180,21896,21983,22992,23125,23377,23626,25106,25130,25326,25463,25509,25581,26480,26688,26708,27905,30028,30102,30117,30342,30571,30883,31224,31469,32467,32687,33410,33445,34249,34471,34903,35299,35750,35800,35819,38454,39049,39601,40082)then 'jie'
             when asicchz in (20165,20170,21170,21370,22116,22535,22935,23613,24062,24273,26020,26187,27135,27941,28024,28908,29710,29822,30684,31105,31563,32039,32537,33641,34943,35167,35280,35880,36166,36817,36827,37329,38182,38771,39313)then 'jin'
             when asicchz in (20117,20140,20742,20834,20928,21037,22659,23143,24362,24452,24778,25004,25964,26060,26223,26230,27902,29517,29855,30153,30555,31454,31455,31632,31923,31934,32463,32956,33003,33096,33550,33606,33729,35686,36851,38236,38449,38742,38745,39048,40120)then 'jing'
             when asicchz in (25155,28847,31384,36837)then 'jiong'
             when asicchz in (20037,20061,20710,21417,21646,21886,23601,25578,25937,26087,26601,26709,28792,29590,30106,31350,32416,33276,33285,36211,37202,38404,38893,39695,40480,40555)then 'jiu'
             when asicchz in (19988,20030,20465,20520,20855,21095,21477,21632,23616,23621,23654,24040,24807,25298,25304,25454,25516,26552,26896,27017,27032,27224,27822,28844,29323,29401,29722,30141,30697,31405,32858,33507,33524,33682,33738,35070,35765,36228,36317,36382,36413,36710,36989,37301,38044,38164,38191,38606,38816,38827,39123,39545,40835)then 'ju'
             when asicchz in (20518,21367,23071,25424,26698,28051,29431,30519,32482,34866,37124,38185,38220,38589,40515)then 'juan'
             when asicchz in (20500,20915,21122,21413,22104,22129,23379,23835,25225,25496,25733,25899,26743,27227,29213,29237,29527,29647,30669,32477,34152,35273,35286,35776,35890,36470,38242)then 'jue'
             when asicchz in (20426,20891,21531,22343,23803,25411,27994,29690,30386,31459,31584,33740,37089,38055,39567,40583)then 'jun'
             when asicchz in (20327,21345,21652,21654,21679,21888,33001)then 'ka'
             when asicchz in (20975,21056,22450,24320,24574,24698,24936,25577,26999,33928,38112,38158,38196)then 'kai'
             when asicchz in (20355,21002,21208,22350,22570,25121,27099,30475,30640,30733,33712,38426,40859)then 'kan'
             when asicchz in (20130,20233,24247,24951,25179,25239,28821,31968,38058,38390)then 'kang'
             when asicchz in (23611,25335,26674,28900,29330,32771,38096,38752)then 'kao'
             when asicchz in (20811,21051,21487,21683,21969,22391,22771,23458,23714,24682,26607,26869,27690,28212,28312,29634,30132,30604,30933,31185,31262,31392,32514,33499,34636,35838,36722,38070,38174,39055,39063,39570,39617)then 'ke'
             when asicchz in (21827,22438,24691,32943,35017,40840)then 'ken'
             when asicchz in (21549,22353,38143)then 'keng'
             when asicchz in (20517,23380,23814,24656,25511,31354,31644)then 'kong'
             when asicchz in (21475,21481,22231,23495,25187,25248,30477,31576,33444,34107)then 'kou'
             when asicchz in (21043,21741,21950,22528,24211,26543,31391,32468,33510,35044,37239,39607)then 'ku'
             when asicchz in (20361,22446,22840,25358,33007,36328)then 'kua'
             when asicchz in (20250,20393,21721,22359,24555,27981,29423,31607,33037,33967,37072)then 'kuai'
             when asicchz in (23485,27454,39627)then 'kuan'
             when asicchz in (20917,21281,21712,22329,22844,26103,26694,29378,30518,30719,31568,32425,35795,35827,36150,37021)then 'kuang'
             when asicchz in (20111,20608,21294,21919,21945,22804,22862,23743,24733,24870,24871,25542,26252,28291,30420,30589,31397,31697,32873,33909,33929,34672,36332,36917,38551,39304,39319,39745)then 'kui'
             when asicchz in (22256,22372,24707,25414,26118,28956,29736,37260,38175,38403,39649,40114)then 'kun'
             when asicchz in (24275,25193,25324,34526,38420)then 'kuo'
             when asicchz in (21068,21862,21895,22403,25289,26095,30220,30764,33098,34593,36771,37003)then 'la'
             when asicchz in (23811,24469,26469,28062,28625,30302,30544,31809,33713,36169,36182,38140)then 'lai'
             when asicchz in (20848,23146,23706,25042,25318,25597,26003,26639,27012,28389,28452,28572,28866,31726,32518,32625,34013,35124,35272,35888,38247,38417)then 'lan'
             when asicchz in (21879,24266,26391,27028,28010,29436,29701,31234,33943,34690,37070,38406)then 'lang'
             when asicchz in (20332,21171,21792,23013,23810,24521,25438,26675,28061,28889,29282,30184,32769,32802,37226,37290,38097,38137)then 'lao'
             when asicchz in (20048,20162,21202,21499,27856,40147)then 'le'
             when asicchz in (20769,22046,22418,23256,25794,27281,27882,30922,31867,32047,32551,32696,32786,32907,34174,35796,37241,38253,38647)then 'lei'
             when asicchz in (20919,22596,24867,26865,26974)then 'leng'
             when asicchz in (20029,20363,20432,20442,20458,20616,21033,21147,21169,21382,21385,21400,21519,21590,21737,21811,21937,22364,23052,23264,25150,26446,26533,26638,26647,26792,27813,28327,28435,28583,29313,29432,29441,29702,29827,30112,30124,30178,30778,30782,31036,31163,31435,31520,31717,31729,31890,31901,32545,32633,33190,33480,33620,33669,33673,34016,34268,34510,34570,34849,35400,36318,36729,36902,37094,37300,37324,38146,38582,38643,39562,40097,40100,40162,40514,40654,40679)then 'li'
             when asicchz in (20457)then 'lia'
             when asicchz in (22849,24088,24265,24604,24651,25947,26973,27539,28063,28491,28610,28860,29711,32451,32852,33080,33217,33714,34105,34826,35042,35043,36830,38142,38256,40098)then 'lian'
             when asicchz in (20004,20142,20510,20937,22682,26238,26753,26891,31918,31921,33391,33704,35845,36361,36742,37327,38162,38739,39753)then 'liang'
             when asicchz in (20102,20698,22073,23525,23534,23589,24278,25730,25769,26009,28518,29134,29536,30103,32557,32842,34044,36797,38028,38243,40553)then 'liao'
             when asicchz in (20925,21015,21155,21671,22482,25449,27916,28872,29454,35010,36244,36496,39715)then 'lie'
             when asicchz in (20020,20955,21533,21833,23961,24298,25044,25294,26519,27305,28107,29747,29848,30645,30967,31932,34106,36161,36495,36762,36980,37051,38678,40158,40607)then 'lin'
             when asicchz in (20196,20278,20940,21478,21604,22265,23725,26563,26818,27872,28789,29618,29940,32491,32666,32718,32838,33491,33777,34505,37187,38083,38517,38646,39046,40110,40836)then 'ling'
             when asicchz in (20845,21016,26066,26611,27060,27969,27983,28316,29080,29705,30041,30244,30827,32506,36955,37775,38157,38223,39311,39581,40552)then 'liu'
             when asicchz in (21679)then 'lo'
             when asicchz in (21657,22404,22405,25314,26634,27895,29649,30275,30779,31423,31548,32843,32999,33551,38471,38534,40857)then 'long'
             when asicchz in (21949,23044,23901,25602,27004,28431,30232,31699,32807,33932,34684,38210,38475,39621)then 'lou'
             when asicchz in (21346,21348,22108,22406,24208,24405,25134,25523,25784,26636,27257,27655,27896,28172,28425,28510,28809,29840,30860,31108,31759,33002,33339,33446,34383,36162,36335,36723,36738,36760,36911,38245,38470,38706,39045,40065,40072,40492,40557,40575,40595)then 'lu'
             when asicchz in (20081,21365,23048,23402,23782,25371,26686,28390,33044,37550,40510)then 'luan'
             when asicchz in (25504,30053,38154)then 'lue'
             when asicchz in (20177,20262,22261,25249,27814,32438,35770,36718)then 'lun'
             when asicchz in (20526,25694,26916,27898,27931,28463,29473,29662,30256,31657,32476,32599,33078,33638,33821,33853,34746,34819,35064,36923,38179,38233,38610,39558,39585)then 'luo'
             when asicchz in (20387,20603,21525,23649,23653,24459,25419,26053,27016,27695,28388,29575,31238,32511,32533,33154,34385,35099,38109,38398,39540)then 'lv'
             when asicchz in (21586)then 'm'
             when asicchz in (21527,21787,22043,22920,26473,29368,29595,30721,34434,34758,39532,39554,40635)then 'ma'
             when asicchz in (20080,21154,21334,22475,33033,33644,36808,38718,40614)then 'mai'
             when asicchz in (22657,23258,24148,24930,26364,28385,28459,29107,30610,32550,34067,34542,34728,35881,38232,38804,39071,39314,40151)then 'man'
             when asicchz in (24537,27667,28461,30450,30829,33426,33579,33725,34770,37017)then 'mang'
             when asicchz in (20882,21359,23745,24125,25035,26052,26164,27611,27862,29286,29483,29761,30592,30683,32772,33538,33541,33542,34661,34762,34980,35980,36152,38086,38170,39654)then 'mao'
             when asicchz in (20040,40637)then 'me'
             when asicchz in (22969,23186,23194,23504,23883,26151,26522,26757,26979,27599,27809,28028,28228,29028,29496,29611,30473,32654,33683,34946,37238,38209,38213,38665,39749,40539)then 'mei'
             when asicchz in (20204,25041,25194,28950,38036,38376,38391)then 'men'
             when asicchz in (21200,23391,25077,26406,26790,27308,29467,29965,30431,30626,31006,33355,33384,33804,33945,34427,34594,34835,38192)then 'meng'
             when asicchz in (21674,22055,23443,23494,24130,24357,24365,25929,27752,27852,29461,30511,31074,31192,31859,31964,32315,33042,33416,34588,35269,35868,35879,36855,37274,38753,40587)then 'mi'
             when asicchz in (20813,20885,21193,23081,26825,27796,28177,28238,30468,30496,32501,32517,33148,38754)then 'mian'
             when asicchz in (21941,22937,24217,25551,26474,28156,28218,30471,30596,31186,32520,32554,33495,34256,37000,40523)then 'miao'
             when asicchz in (20060,21673,28781,31742,34065,34843)then 'mie'
             when asicchz in (23735,24751,24845,25279,25935,26107,26108,27665,27887,29599,29641,30399,32535,33504,38389,38397,40152,40702)then 'min'
             when asicchz in (20901,21517,21629,26126,26269,27962,28319,30609,33559,34719,37225,38125,40483)then 'ming'
             when asicchz in (35884)then 'miu'
             when asicchz in (22696,23275,23351,23518,25273,25705,25720,25721,26411,27169,27521,27819,28448,30268,30952,31203,32817,33180,33545,33707,34022,34321,35871,35978,35992,38214,38476,39309,39764,40664)then 'mo'
             when asicchz in (20372,21726,26576,29279,30520,34513,35851,37738)then 'mou'
             when asicchz in (20137,20203,21215,22390,22675,22982,24149,24917,25287,26286,26408,27597,27626,27792,29281,29287,30446,30566,31302,33500,38076)then 'mu'
             when asicchz in (21584,21738,23068,25343,25466,32435,32941,34930,37027,38048,38222)then 'na'
             when asicchz in (20035,20340,22856,22902,26608,27670,32784,33407,33816,40720)then 'nai'
             when asicchz in (21335,21891,22237,22241,26976,30007,33129,34683,36199,38590)then 'nan'
             when asicchz in (22218,22228,25902,26345,39317)then 'nang'
             when asicchz in (21622,22452,23404,24700,25376,28118,29489,29785,30791,33041,34546,38105,38393)then 'nao'
             when asicchz in (21602,35767)then 'ne'
             when asicchz in (20869,39297)then 'nei'
             when asicchz in (23273,24641)then 'nen'
             when asicchz in (33021)then 'neng'
             when asicchz in (20274,20320,20522,21311,22381,22958,23612,24617,25311,26062,26165,27877,28346,29450,30568,33147,34364,36870,38092,38675,40117)then 'ni'
             when asicchz in (22493,24180,24319,24565,25288,25467,25781,30910,31896,34091,36743,40071,40118,40655)then 'nian'
             when asicchz in (23064,37247)then 'niang'
             when asicchz in (23346,23615,33074,33553,34949,40479)then 'niao'
             when asicchz in (20060,21870,21995,23421,25423,28037,32834,33260,34326,36433,38218,38221,38503,39070)then 'nie'
             when asicchz in (24744)then 'nin'
             when asicchz in (20318,20957,21659,23425,25319,26592,27870,29406,29999,32845)then 'ning'
             when asicchz in (22942,24568,25197,25303,29275,29379,32445,38062)then 'niu'
             when asicchz in (20396,20892,21725,24324,27987,33043)then 'nong'
             when asicchz in (32808)then 'nou'
             when asicchz in (21162,22900,23397,24361,24594,33004,39549)then 'nu'
             when asicchz in (26262)then 'nuan'
             when asicchz in (25386,30111,34384)then 'nue'
             when asicchz in (20649,21903,25062,25638,31983,35834,38168)then 'nuo'
             when asicchz in (22899,24679,34884,38037)then 'nv'
             when asicchz in (21734,22114)then 'o'
             when asicchz in (20598,21589,24580,27431,27572,27812,29935,32806,34261,35764,40485)then 'ou'
             when asicchz in (21866,24085,24597,25170,26487,29228,29750,31586,32793,33897,36276)then 'pa'
             when asicchz in (20467,21708,24472,25293,25490,27966,28227,29260,33934)then 'pai'
             when asicchz in (21028,21467,25306,25856,27886,28504,29247,30036,30424,30460,30928,34784,34978,35195,36434)then 'pan'
             when asicchz in (20051,22786,24222,24439,25909,26049,28354,30917,32810,32982,34691,36868)then 'pang'
             when asicchz in (21032,21263,21638,24214,25243,27873,28846,29389,30129,33068,34957,36305)then 'pao'
             when asicchz in (20329,21624,22521,24084,26054,27803,32986,35060,36180,36756,37197,37253,38187,38506,38664)then 'pei'
             when asicchz in (21943,28243,30406)then 'pen'
             when asicchz in (22061,22539,24429,24614,25256,25447,26379,26842,28558,28921,30768,30844,30896,31735,33192,34028,34779,40527)then 'peng'
             when asicchz in (19989,20211,20731,21128,21305,21542,21860,22140,22318,22383,22500,23218,23617,24192,25209,25259,25815,26503,27607,28128,29749,29971,30091,30130,30174,30294,30382,30738,32432,32628,33086,33432,34445,34609,34987,35692,35988,36767,37043,37099,38093,38516,38713,40729)then 'pi'
             when asicchz in (20415,20559,29255,29327,31687,32745,33020,35869,36417,39560,39575)then 'pian'
             when asicchz in (21117,22028,23254,27533,28418,29922,30623,31080,32549,33705,34741,39128)then 'piao'
             when asicchz in (20031,24417,25735,27669,30629,33508)then 'pie'
             when asicchz in (21697,23000,23252,25340,27008,29277,32856,36139,39057,39078)then 'pin'
             when asicchz in (20050,20444,20911,20973,22378,23049,23631,24179,26544,29942,33529,33805,35780,40070)then 'ping'
             when asicchz in (21493,22369,23110,27900,29632,30372,30772,31544,31893,36843,37169,38027,38071,39047,39748)then 'po'
             when asicchz in (21078,25482,35026)then 'pou'
             when asicchz in (20166,21261,22103,22275,22484,25169,26222,26292,26333,26420,27654,28006,28325,28654,28689,29854,33670,33769,33889,33970,35889,36476,38138,38244,38248)then 'pu'
             when asicchz in (19971,20062,20115,20225,20447,20854,20932,21551,22017,22120,22331,22855,22865,22971,23674,23682,23696,23822,24323,25001,25114,26071,26399,26462,26578,26646,26724,26827,27117,27450,27495,27668,27732,27773,27791,27875,28103,28422,29734,29738,30054,30732,30875,31041,31048,31098,32166,32494,32521,32774,33040,33425,33450,33793,33803,33914,34162,34548,34590,35755,36215,36804,39040,39568,39569,40141,40594,40784)then 'qi'
             when asicchz in (22841,24688,25488,27965,33884,34999,39618)then 'qia'
             when asicchz in (20094,20191,20325,20521,21069,21315,22545,23693,23884,24749,24838,24906,25190,25518,25652,26912,27424,27465,27973,28508,29301,31614,31645,32420,32561,32951,33418,33441,33564,33640,34388,35120,35878,35892,36801,36963,38030,38052,38065,38067,38085,38433,39582,40660)then 'qian'
             when asicchz in (21595,22681,23281,23558,24378,25109,25111,25250,26538,27183,28829,32652,32671,33108,34103,34595,35137,36292,38166,38197,38250)then 'qiang'
             when asicchz in (20052,20392,20431,21066,21121,22771,23780,23789,24039,24708,24832,24980,25772,25970,26725,27189,27207,30631,30807,31373,32562,32728,33630,35822,35887,36343,38201,38802,38808)then 'qiao'
             when asicchz in (19988,20999,22974,24623,24812,25352,31363,31655,33540,36228,37060,38194)then 'qie'
             when asicchz in (20146,20405,21220,21539,21994,22105,23517,25599,25810,27278,27777,28337,29748,30684,31165,31206,33449,33465,34707,34942,35203,38054,38163)then 'qin'
             when asicchz in (20542,21375,22282,24198,24773,25806,26228,27296,27682,27696,28165,30956,32174,32580,33496,34619,35622,35831,36731,38738,39031,40109,40677)then 'qing'
             when asicchz in (29756,31351,31353,31559,33557,34537,36331,37019,37518)then 'qiong'
             when asicchz in (19992,20421,22234,24047,27000,27714,27845,28267,29360,29699,31179,31959,34412,34479,34660,35032,36167,36881,36946,37041,37195,40133,40765)then 'qiu'
             when asicchz in (21164,21306,21435,21462,23094,23624,23702,26354,26384,27661,28192,29865,30319,30655,30962,31067,33507,34134,34343,34502,34512,34876,34914,35281,35790,36235,36259,36527,38418,39537,40498,40628,40674,40843)then 'qu'
             when asicchz in (20840,21048,21149,22280,24731,25331,26435,27849,29356,29357,30030,30154,31564,32507,33603,34615,35808,36737,37275,38120,39079,39688)then 'quan'
             when asicchz in (21364,24747,27063,28820,30264,30830,32570,38421,38425,38592,40522)then 'que'
             when asicchz in (32676,35033,36897,40583)then 'qun'
             when asicchz in (20873,26579,28982,29123,33490,34490,39663)then 'ran'
             when asicchz in (22199,22756,25880,29924,31155,31344,35753)then 'rang'
             when asicchz in (23046,25200,26721,32469,33627,39286)then 'rao'
             when asicchz in (21903,24825,28909)then 're'
             when asicchz in (20154,20161,20190,20219,20995,22764,22922,24525,31252,32427,33615,34941,35748,36715,38887,39274)then 'ren'
             when asicchz in (20173,25172)then 'reng'
             when asicchz in (26085)then 'ri'
             when asicchz in (20887,23481,23896,25102,27029,28342,29076,29416,32466,32924,33592,33635,33993,34686,34701)then 'rong'
             when asicchz in (25545,26580,31941,32905,36418,38819)then 'rou'
             when asicchz in (20083,20754,20837,22149,22914,23418,27741,27955,28349,28641,32539,33593,34000,34231,34837,35109,35174,36785,38135,39077)then 'ru'
             when asicchz in (26378,36719,38446)then 'ruan'
             when asicchz in (26520,29790,30591,33454,34122,34148,34443,38160)then 'rui'
             when asicchz in (28070,38384)then 'run'
             when asicchz in (20556,23164,24369,31660,33509)then 'ruo'
             when asicchz in (21317,25746,27922,33038,33832,39122)then 'sa'
             when asicchz in (22139,22622,33134,36187,40131)then 'sai'
             when asicchz in (19977,20200,20254,21441,24417,25955,27637,39315)then 'san'
             when asicchz in (20007,21971,25633,26705,30921,39073)then 'sang'
             when asicchz in (22525,23234,25195,25620,30233,32555,33226,39578,40139)then 'sao'
             when asicchz in (21868,28073,29791,31313,33394,38127)then 'se'
             when asicchz in (26862)then 'sen'
             when asicchz in (20711)then 'seng'
             when asicchz in (20667,21049,21414,21820,21861,26432,27459,27801,29022,30183,30722,32433,33678,35039,38121,38670,40104)then 'sha'
             when asicchz in (26194,31579,37246)then 'shai'
             when asicchz in (21024,21089,21892,22479,22999,23319,23665,25159,25797,26441,26629,27733,28536,29053,29642,30109,32558,33203,33211,33314,33439,33515,34798,34923,35754,36193,36314,37167,38032,38378,38485,39583,40157)then 'shan'
             when asicchz in (19978,20260,21830,22439,22674,23578,26188,27527,27895,29109,32497,35059,35294,36175)then 'shang'
             when asicchz in (21165,21242,21736,23569,25422,26451,26786,28530,28903,31245,31602,32461,33348,33421,33493,34552,37045,38808,38902)then 'shao'
             when asicchz in (20312,21389,22882,23556,24913,25586,25668,27481,28041,28384,29470,30066,31038,33292,33293,34503,35774,36170,36198,40605)then 'she'
             when asicchz in (20280,21442,21627,21698,23072,23158,23457,24910,26937,27784,28145,28182,28183,29637,29978,30003,30695,30775,31070,31937,32453,32958,32962,33688,33882,34563,35804,35842,36523)then 'shen'
             when asicchz in (20056,21097,21319,22307,22768,23882,26119,26207,28177,28262,29298,29983,29989,30427,30465,30490,31513,32499,32988)then 'sheng'
             when asicchz in (19990,20107,20160,20181,20351,20365,21183,21273,21313,21490,21980,22040,22124,22488,22763,22833,22987,23454,23460,23608,23630,24066,24072,24335,24337,24643,25325,25342,26045,26102,26159,26623,27663,28287,28859,29422,30690,30707,31034,31035,31598,33296,33715,33997,34417,34432,34731,35270,35475,35782,35797,35799,35877,35925,36147,36732,36866,36893,37322,38088,39135,39280,39542,40101,40122)then 'shi'
             when asicchz in (20861,21463,21806,23432,23551,25163,25480,25910,29417,30246,32502,33359,39318)then 'shou'
             when asicchz in (20008,20070,20446,20495,21460,22654,22661,23005,23408,23646,24246,24661,25101,25234,25669,25968,26257,26329,26415,26463,26530,26641,26803,27530,27571,27641,27821,28113,28465,28557,29087,30095,31211,31446,32446,32626,33127,33298,33789,34092,34223,34560,36174,36755,36848,40653,40736)then 'shu'
             when asicchz in (21047,21808,32781)then 'shua'
             when asicchz in (24069,25684,29575,29993,34752,34928)then 'shuai'
             when asicchz in (25332,26643,28078,38377)then 'shuan'
             when asicchz in (21452,23360,29245,38684)then 'shuang'
             when asicchz in (27700,30561,31246,35841)then 'shui'
             when asicchz in (21550,30636,33308,39034)then 'shun'
             when asicchz in (22913,25632,26388,27082,28865,30805,33972,35828,38084)then 'shuo'
             when asicchz in (19997,20282,20284,20447,20821,21422,21430,21496,21661,21987,22070,22235,22994,23546,24051,24605,25749,26031,27515,27740,27863,28556,31040,31169,31525,32524,32796,32902,34547,38198,39282,39543,40502)then 'si'
             when asicchz in (20935,23435,23847,23913,24554,24578,24730,26494,28126,31462,32824,33752,35772,35829,36865,39042)then 'song'
             when asicchz in (21471,21974,22013,22014,25628,25822,28338,30605,33368,34222,34699,38204,39125,39306)then 'sou'
             when asicchz in (20439,20723,21961,22609,22809,23487,24875,28049,28335,29990,31267,31756,31903,32032,32899,33487,34060,35307,35785,35873,36895,37221)then 'su'
             when asicchz in (29435,31639,33948,37240)then 'suan'
             when asicchz in (23681,28617,29159,30509,30562,30862,31071,31319,32485,33661,34429,35847,36930,36995,38539,38543,38567,39635)then 'sui'
             when asicchz in (23385,25439,27051,29426,31499,33642,38588,39143)then 'sun'
             when asicchz in (21766,21794,21965,21990,23057,25152,25394,26731,26797,29712,30531,32034,32553,32679,34001,38145)then 'suo'
             when asicchz in (20182,21970,22604,22612,22905,23427,25374,27067,27795,28347,28463,29549,36287,36367,36427,36962,38090,38396,40142)then 'ta'
             when asicchz in (21488,22823,22826,24577,25260,27760,27888,28849,32957,32974,33492,34233,36294,37040,37214,38043,39552,40080)then 'tai'
             when asicchz in (21497,22349,22363,22374,24377,24528,25506,25674,26137,27264,27631,28393,28525,28601,28845,30192,30251,30899,34962,35203,35848,35885,36138,37103,38077,38188)then 'tan'
             when asicchz in (20504,20645,21776,22530,22616,24081,24797,25642,26848,27160,27748,28108,28303,28907,29805,31958,32688,32805,33179,34711,34739,36255,36538,37283,38132,38231)then 'tang'
             when asicchz in (21480,21845,22871,25487,26691,27950,28059,28120,28372,32486,33796,35752,36867,38518,38892,39253,40727)then 'tao'
             when asicchz in (24529,24530,24925,29305,38141)then 'te'
             when asicchz in (28373,30140,33150,34276,35466)then 'teng'
             when asicchz in (20307,20508,21059,21076,21884,22159,23198,23625,24716,24789,25552,26367,26799,28053,32488,32519,33617,35068,36386,36420,36886,37261,38161,39064,40520)then 'ti'
             when asicchz in (22635,22825,24541,24684,25517,27524,28155,28233,29980,30000,30027,33094,33300,38423)then 'tian'
             when asicchz in (20347,25361,26465,30522,31079,31381,31524,31900,33493,34601,35843,36339,36834,39659,40102,40838)then 'tiao'
             when asicchz in (24086,33820,36148,38081,39214)then 'tie'
             when asicchz in (20141,20572,21381,21548,23159,24237,24311,25402,26755,27712,28867,29693,33351,33691,33910,34579,38116,38662)then 'ting'
             when asicchz in (20189,20319,21516,22005,24420,24696,25413,26704,26742,28540,30171,30643,30780,31461,31570,32479,33596,36890,37230,38108)then 'tong'
             when asicchz in (20599,22836,25237,36879,39600)then 'tou'
             when asicchz in (20820,20984,21520,22270,22303,22541,23648,24466,28034,31171,31361,33660,33759,36884,37236,38029)then 'tu'
             when asicchz in (22242,24406,25247,28237,30083)then 'tuan'
             when asicchz in (25512,29050,33151,34581,35114,36864,39059)then 'tui'
             when asicchz in (21534,22244,23663,26302,27709,33216,35930,39272)then 'tun'
             when asicchz in (20039,20311,21822,22376,22949,24249,25176,25299,25302,26561,26589,26925,27216,27825,30755,31656,33073,36302,37217,38464,39534,39548,40501,40717)then 'tuo'
             when asicchz in (20324,21703,23043,23090,25366,27964,29926,33149,34521,34972)then 'wa'
             when asicchz in (22806,23860,27498)then 'wai'
             when asicchz in (19975,20024,21084,23113,23436,23451,24367,24779,25405,26202,28286,28919,29609,29740,30073,30358,30871,32424,32510,33048,33109,33412,33694,33728,34067,34623,35916,39037)then 'wan'
             when asicchz in (20129,22916,24448,24536,24792,26106,26395,26505,27754,29579,32593,32596,36747,39757)then 'wang'
             when asicchz in (20026,20255,20266,20301,20558,21355,21361,21619,21807,21890,22260,22313,22996,23041,23059,23561,23614,23916,24013,24079,24119,24494,24799,24944,26410,26693,27817,27943,28064,28205,28493,28828,29032,29477,29484,29614,30031,30207,32428,32500,32963,33353,33479,33806,33907,34074,34183,35839,35859,36558,36829,36918,38385,38536,38551,38886,38890,39759,40084)then 'wei'
             when asicchz in (21006,21563,22786,25909,25991,27766,28201,29882,30239,31283,32010,32441,34442,38382,38395,38412,38639)then 'wen'
             when asicchz in (21985,29934,32705,33994,34169)then 'weng'
             when asicchz in (20525,21351,21908,24132,25105,25373,25569,26017,27779,28065,28197,30826,31389,32927,33716,34583,40844)then 'wo'
             when asicchz in (20044,20116,20213,20237,20398,20800,21153,21247,21320,21556,21566,21596,21780,22316,22366,22953,23162,23524,23627,24043,24209,24548,24579,24694,24735,25098,25410,26080,26212,26444,26791,27494,27595,27745,28944,29289,29310,30182,33310,33436,33460,34568,35820,35823,36821,37036,37576,38056,38434,38654,39579,40521,40540,40751)then 'wu'
             when asicchz in (20064,20694,20846,21560,21775,21916,22075,22805,22874,23219,23305,23651,24076,24109,24473,24687,24713,24796,25103,26132,26224,26342,26512,26646,27176,27268,27447,27481,27728,27927,28000,28101,28330,28911,29060,29081,29113,29306,29312,29626,30361,30717,30802,31114,31143,31232,31352,31902,31995,32454,32690,32725,33181,33284,33342,33564,33765,33912,34032,34597,34693,34763,34989,35068,35199,35275,36426,37079,37295,38115,38177,38411,38553,38576,39273,40759)then 'xi'
             when asicchz in (19979,20384,21283,21414,21523,21623,22799,23777,26247,26585,29390,29421,29781,30606,30806,32581,34430,36758,36944,38686,40672)then 'xia'
             when asicchz in (20185,20808,20924,21439,21688,23092,23244,23466,23704,24358,25472,26174,26297,27673,28046,29177,29443,29486,29616,29625,30187,31046,31557,31868,32420,32447,32673,33146,33335,33483,33718,34259,34476,34900,35265,36132,36323,36345,37232,38184,38386,38480,38505,38519,38704,39301,40092,40519)then 'xian'
             when asicchz in (20065,20139,20687,21410,21521,21709,24055,24224,24819,27233,28248,30456,31077,31665,32515,32724,33431,33881,34771,35140,35814,35937,38262,38477,39033,39144,39287,39321,39591,40094)then 'xiang'
             when asicchz in (21066,21715,21742,21880,22179,23389,23477,23567,23844,25928,26195,26541,26549,26657,28040,28102,28487,30813,31505,31601,31659,32481,32918,33831,34552,36877,38144,38660,39553,39752)then 'xiao'
             when asicchz in (20123,20149,20565,20889,21232,21327,21368,23633,24296,25032,25375,25658,25783,26012,26800,26964,27021,27053,27463,27844,27899,28203,28707,29166,29548,32449,32556,32961,34212,34638,34809,35856,35874,36416,36510,36994,37034,38795,39049)then 'xie'
             when asicchz in (20449,22239,24515,24571,26032,26133,27427,27462,33455,33688,34218,34885,36763,37995,38156,38241,39336)then 'xin'
             when asicchz in (20852,21009,22411,22995,24184,24418,24615,24763,24826,25828,26143,26447,27920,29481,30465,30798,33125,33607,33637,34892,37026,37266,38473,39271)then 'xing'
             when asicchz in (20804,20982,21256,27769,29066,33016,33422,38596)then 'xiong'
             when asicchz in (20241,20462,21691,21957,23723,24229,26429,28340,31168,32483,32670,33261,34966,35973,38152,39312,39673,40506)then 'xiu'
             when asicchz in (21206,21465,21505,22040,22313,22687,22977,23167,24207,24464,24676,25100,26093,26665,27947,28294,29030,30044,30449,31944,32110,32490,32493,32951,32997,33988,34047,34394,35768,35817,37207,37265,38656,39035,39036)then 'xu'
             when asicchz in (20743,21927,23459,24748,25550,26059,26244,26982,27883,28210,28457,28843,28892,29002,29572,29764,29831,30147,30307,30505,30905,32474,33841,35862,36713,36873,38089,38239)then 'xuan'
             when asicchz in (22129,23398,27894,31348,34203,34880,35857,36357,38634,38772,40149)then 'xue'
             when asicchz in (21195,21235,22489,23547,23755,24033,24061,24455,24490,24642,26092,26331,27529,27739,27957,27988,27994,29071,29551,31400,33600,33640,34120,34224,35757,35759,35810,36805,36874,37063,37306,39535,40095)then 'xun'
             when asicchz in (20011,20122,20258,21387,21568,21713,22445,23045,23688,23830,25276,25568,26720,27689,28079,29273,29706,30166,30554,30737,33469,34460,34905,35766,36711,36819,38597,40486,40493)then 'ya'
             when asicchz in (20005,20456,20547,20822,21089,21388,21411,21693,21761,22479,22576,22852,22925,23267,23359,23476,23721,23846,24310,24422,24697,25513,26191,26858,27280,27839,28153,28270,28383,28436,28814,28895,28937,28976,28977,29141,29744,30416,30524,30740,30746,31605,32616,33005,33100,33395,33784,34578,34893,35328,35866,35891,36189,36284,37118,37154,37245,38379,38409,38414,38415,38593,39068,39181,39564,39751,40761)then 'yan'
             when asicchz in (20208,20335,20859,22830,24457,24591,24665,25196,26104,26472,26679,27523,27687,27889,27915,28478,28800,28874,30113,30162,31207,32650,34520,38451,38789,40495)then 'yang'
             when asicchz in (21510,21676,22426,22829,22934,23002,23591,23870,24186,24493,25671,26332,26483,29243,29671,29814,31368,31377,32768,32948,33136,33280,33647,35201,35875,36730,36965,36992,38123,40144,40542,40637)then 'yao'
             when asicchz in (19994,20063,20918,21494,22094,22812,25341,25494,25586,25590,26196,26355,26928,28082,28904,29239,32822,33099,35858,37050,37326,38104,38757,39029)then 'ye'
             when asicchz in (19968,20041,20057,20134,20159,20197,20202,20234,20314,20350,20381,20506,21000,21139,21307,21587,21670,21695,21964,22123,22319,22520,22777,22839,22869,23016,23452,23673,23748,23991,24050,24322,24328,24331,24413,24441,24518,24609,24639,24722,24847,25087,25233,25401,25558,26070,26131,26885,27449,27562,27589,27778,28005,28322,28458,29088,29463,30097,30123,30157,30231,30292,30410,30489,30691,31227,32462,32546,32703,32714,32716,32755,32764,32900,33008,33222,33315,33402,33406,33505,33617,34191,34433,34503,34612,34915,35028,35758,35793,35794,35811,35850,36155,36726,36836,36920,36951,37009,37199,38023,38129,38226,38257,39056,39284,39551,40671)then 'yi'
             when asicchz in (21360,21535,21554,21905,22240,22432,22553,22820,23035,23493,23609,24341,27575,27684,27911,28139,29434,30270,31400,32996,33562,33589,33643,34451,37150,38111,38134,38452,38544,38698,38899,39278,40840)then 'yin'
             when asicchz in (22052,23156,23221,23348,24212,24433,25732,26144,27001,27185,28386,28486,28699,29787,29838,30271,30408,30828,32552,32578,33210,33521,33556,33637,33639,33721,33722,33828,33829,33830,34021,34631,36194,36814,37090,39053,39062,40550,40560)then 'ying'
             when asicchz in (21727,21815)then 'yo'
             when asicchz in (20323,20433,21191,21647,21889,22665,22725,24248,24703,24949,25317,27704,27891,28044,29992,29996,30152,33219,34553,36362,37013,38235,38605,39252,40153)then 'yong'
             when asicchz in (20248,20305,20369,21347,21448,21451,21491,21606,22271,23461,23586,23588,24188,24189,24551,24736,25912,26377,26586,27833,28216,29270,29369,29495,30001,30115,32327,33692,33696,33720,34480,34484,34659,34660,35825,37038,37193,37321,38080,38101,40063,40669,40748)then 'you'
             when asicchz in (19982,20104,20110,20251,20313,20446,20451,21505,21947,22276,22281,22495,22948,22954,23089,23431,23507,23561,23679,23786,23899,24254,24423,24481,24840,24841,24858,25540,26044,26161,27014,27428,27442,27603,28020,28132,28180,28189,29020,29152,29425,29427,29577,29760,29788,30066,30208,30224,30402,31161,31162,31404,31411,31485,31909,32417,32701,32895,32946,33140,33278,33281,33286,33419,33848,34019,34398,34606,34643,35029,35278,35465,35821,35840,35861,35947,36802,36926,36935,37057,38064,38408,38533,38632,38633,39044,39275,39296,39533,39739,40060,40518,40556,40841)then 'yu'
             when asicchz in (20803,20900,21407,21592,22253,22278,22300,22435,22456,22636,23195,24616,24895,25534,25588,27260,27781,28170,28304,29232,29503,29783,30498,31650,32536,33451,33489,34696,34945,36757,36828,38498,40482,40499,40715)then 'yuan'
             when asicchz in (20048,21014,21717,23731,24742,26352,26376,26638,27198,28729,29605,31908,32422,35828,36234,36291,38053,38074,38405,40864)then 'yue'
             when asicchz in (20113,20801,21248,23381,24701,24864,26112,26197,27538,27698,29096,29377,31584,32429,32792,33464,34164,36103,36191,36816,37075,37095,37213,37574,38504,38642,38891,38901)then 'yun'
             when asicchz in (21277,21634,25334,26434,30776)then 'za'
             when asicchz in (20877,21705,22312,23472,23869,26685,28798,30014,36733)then 'zai'
             when asicchz in (21681,25874,26141,26242,29906,31786,31948,36190,36273,37694)then 'zan'
             when asicchz in (22872,33039,33255,33900,36163,39541)then 'zang'
             when asicchz in (20991,21795,22122,26089,26531,28577,28790,29157,30338,31967,34299,34468,36481,36896,36973)then 'zao'
             when asicchz in (20164,21017,21863,24123,25321,26115,27901,31654,33332,36131,36188,36846)then 'ze'
             when asicchz in (36156)then 'zei'
             when asicchz in (24590,35886)then 'zen'
             when asicchz in (22686,24974,26366,29111,29969,32559,32638,36192,38147)then 'zeng'
             when asicchz in (20045,21522,21643,21668,21747,21939,25166,25592,26413,26629,26946,27048,28195,28856,30148,30504,30751,34481,35784,36711,38113,38392,40772)then 'zha'
             when asicchz in (20538,23429,23528,25688,25995,30261,30758,31364,32735)then 'zhai'
             when asicchz in (21344,23637,23853,25112,25612,26025,26051,26632,27617,27838,28251,30415,30651,31449,31896,32509,34360,35449,35893,36759)then 'zhan'
             when asicchz in (19976,20169,20183,23260,23938,24080,24155,24352,24432,25484,26454,27167,28072,28467,29520,29835,30260,31456,32960,34769,36134,37155,38271,38556)then 'zhang'
             when asicchz in (20806,21484,25214,25307,26157,26397,26873,27836,29031,31498,32617,32903,35791,36213,38026)then 'zhao'
             when asicchz in (20039,21746,21894,25240,25722,26584,27993,30932,32773,34071,34544,34567,34731,35126,35882,36205,36740,36761,36825,36974,38167,40551)then 'zhe'
             when asicchz in (20390,22323,24103,25391,26015,26389,26517,26722,26937,27035,27976,29645,29956,30043,30137,30495,30759,31087,31289,31668,32540,32983,33275,33985,35786,36126,36168,36728,38024,38215,38453,38663,40489)then 'zhen'
             when asicchz in (20105,23781,24449,24501,24596,25327,25379,25919,25972,27491,29424,30151,30529,31581,33976,35777,35812,37073,38066,38126)then 'zheng'
             when asicchz in (20043,20356,20540,21046,21358,21482,21553,21675,22336,22516,23769,24089,24092,24408,24535,24558,25191,25351,25370,25527,25709,25903,26088,26234,26525,26547,26624,26633,26702,26893,27490,27542,27713,27835,28382,28825,30164,30179,30452,30693,31049,31209,31258,31378,32119,32440,32455,32622,32844,32930,32989,33026,33187,33267,33268,33437,33463,34541,34584,35311,35960,36136,36157,36286,36310,36396,36399,36725,36734,37061,37231,38495,38601,39576,40503,40697)then 'zhi'
             when asicchz in (20013,20210,20247,20898,24544,24554,30405,31181,32456,32959,33327,34749,34935,36405,37325,38047,38202)then 'zhong'
             when asicchz in (21608,21650,21825,22959,23449,24030,24090,26172,27954,30385,30881,31808,31909,32327,32419,32457,32920,32964,33311,33646,35788,36724,37198,39588)then 'zhou'
             when asicchz in (20027,20267,20303,20367,21161,22065,23646,25284,26417,26492,26609,26666,27104,27237,27880,27929,28186,28532,28855,28891,29038,29482,29664,30128,30211,30633,31069,31481,31482,31569,31672,32741,33331,33486,33585,33879,34496,34523,35803,35832,36142,36485,36880,37054,38114,38136,39547,40584)then 'zhu'
             when asicchz in (25235,25373,29226)then 'zhua'
             when asicchz in (25341)then 'zhuai'
             when asicchz in (19987,20256,21869,25776,30742,31686,36186,36716,39067,39316)then 'zhuan'
             when asicchz in (20718,22766,22918,24162,24196,25758,26729,29366,35013)then 'zhuang'
             when asicchz in (22368,24820,26894,32512,32530,33809,36184,36861,38181,38585,39571)then 'zhui'
             when asicchz in (20934,23663,31360,32939,35846)then 'zhun'
             when asicchz in (20524,21331,21828,25305,25417,25826,26027,26700,27978,27998,28095,28655,28796,29730,30528,31130,33537,35836,37196,38255)then 'zhuo'
             when asicchz in (20180,20857,21618,21672,22986,23039,23376,23383,23388,23411,23915,24675,26771,28100,28173,28363,28371,30502,31213,31531,31869,31906,32043,32513,32788,33258,33544,35390,35864,36160,36164,36241,36750,38193,39661,40123,40839)then 'zi'
             when asicchz in (20588,23447,24635,26837,31933,32437,32508,33113,36394,39683)then 'zong'
             when asicchz in (22863,25549,35833,36208,37049,37177,38508,39546,40112)then 'zou'
             when asicchz in (20430,21330,26063,31062,31199,32452,33785,35781,36275,38238,38459)then 'zu'
             when asicchz in (25893,32386,32565,36508,38075)then 'zuan'
             when asicchz in (22068,26368,32618,34142,35292,37257)then 'zui'
             when asicchz in (23562,25753,27197,36981,40159)then 'zun'
             when asicchz in (20304,20316,20570,21777,22060,22352,24038,24231,24589,26152,26590,31066,31534,32985,37218,38460)then 'zuo'
						 else hzstr
        end
        retval
        from asclb
           
    )
    select
      string_agg(retval,'') into sresult
      from hzpdlb;
 
  return sresult;
 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

测试

1
2
select zh_cn('101'),zh_cn('中国');
select name from test order by zh_cn(name) desc;

postgresql 中文排序_pgsql中文排序-CSDN博客

1.2.12 表空间及使用

授权

1
chown postgres:postgres /data

操作表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建表空间，数据放到/data
CREATE TABLESPACE new_tablespace LOCATION '/data';
-- 创建表，指定使用表空间
CREATE TABLE my_table (
    id serial PRIMARY KEY,
    data text
) TABLESPACE new_tablespace;
-- 创建索引，指定使用的表空间
create index my_table_test on my_table (data) tablespace new_tablespace;
-- 将现有表设置表空间
ALTER TABLE my_table SET TABLESPACE new_tablespace;
-- 使用表
select * from my_table;
-- 查询所有表空间
SELECT * FROM pg_tablespace;
-- 查询表所属表空间名称
SELECT tablespace  FROM pg_tables WHERE tablename = 'my_table';
-- 查询表存储的物理地址, 这是返回的$PG_DATA下的一个相对路径。该路径会软链接到实际的表空间地址
select pg_relation_filepath('my_table');

1.2.13 查询vacuum配置

1
2
3
4
5
6
-- 查询整个库的自动vacuum状态
show autovacuum;
-- 关闭某张表的自动vacuum
alter table test_data set (autovacuum_enabled = false);
-- 查询所有表的配置项,reloptions存储表的配置项
select relname,reloptions from pg_class

二、扩展

2.1 Postgis

2.1.1 安装

安装资料库

1
yum -y install epel-release

安装postgis

1
2
3
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装psql11所用postgis30版本
yum -y install postgis30_11

安装后检查

1
rpm -qi postgis30_11

2.1.2 开启扩展

登录你创建的数据库，开启postgis扩展，否则是不能使用geometry类型的

1
2
3
4
5
6
7
8
9
10
su - postgres

psql

-- 切换数据库
\connect pgrouting_test;
-- 开启扩展
create extension postgis;
-- 指定schema
create extension postgis with schema testschmea

执行结果如下

1
2
postgres=# \connect pgrouting_test
您现在已经连接到数据库 "pgrouting_test",用户 "postgres".

新建的用户，没有超级权限是不能进行扩展开启的。除非使用postgres。

所以，如果我们想用创建的用户进行扩展开启，可以手动授予超级权限，再撤销。

1
2
3
4
-- 给超级权限
alter role pgrouting superuser;
-- 取消超级权限
alter role pgrouting nosuperuser;

2.1.3 查看扩展版本信息

需要先开启扩展才能查看

1
select postgis_full_version();

如果是使用yum安装的扩展，只需要执行命令

1
yum info postgis

2.2 Pgrouting

pgrouting的安装和使用教程，pgRouting Manuals都有。

1
2
3
4
5
6
# 先搜索有没有，有则找到相应版本安装即可。没有只能自己编译
yum search pgrouting
# 安装pgrouting
yum -y install pgrouting_11
# 安装openStreetMap导入工具
yum -y install osm2pgrouting_11

开启扩展，不太清楚操作，就看上一节Postgis，操作是相同的。

1
create extension pgrouting

使用sql查看版本信息

1
SELECT  * FROM pgr_version();

在Linux终端查看osm2pgrouting版本

1
2
[root@rollback ~]# osm2pgrouting --version
This is osm2pgrouting Version 2.3.8
发布：2022-01-04 20:08:49
修改：2024-12-21 20:14:09
链接：https://meethigher.top/blog/2022/postgresql/
标签：devops sql 
付款码 打赏 分享
访客数calculating人
Shift+Ctrl+1 可控制工具栏