GassDB(DWS)功能 -- 中文排序

中文字段按照拼音排序

postgres=# CREATE TABLE public.dim_stu_pinyin_info (id bigint, name text) DISTRIBUTE BY REPLICATION;
CREATE TABLE
postgres=# INSERT INTO public.dim_stu_pinyin_info VALUES (1, '雷锋');
INSERT 0 1
postgres=# INSERT INTO public.dim_stu_pinyin_info VALUES (2, '石传祥');
INSERT 0 1
postgres=# SELECT * FROM public.dim_stu_pinyin_info
postgres-# ORDER BY NLSSORT (name, 'NLS_SORT = SCHINESE_PINYIN_M' );
 id |  name
----+--------
  1 | 雷锋
  2 | 石传祥
(2 rows)

postgres=# SELECT * FROM public.dim_stu_pinyin_info
postgres-# ORDER BY NLSSORT (name, 'NLS_SORT = SCHINESE_PINYIN_M' ) DESC;
 id |  name
----+--------
  2 | 石传祥
  1 | 雷锋
(2 rows)

大小写不敏感排序

postgres=# CREATE TABLE public.dim_stu_icase_info (id bigint, name text) DISTRIBUTE BY REPLICATION;
CREATE TABLE
postgres=# INSERT INTO public.dim_stu_icase_info VALUES (1, 'aaaa');
INSERT 0 1
postgres=# INSERT INTO public.dim_stu_icase_info VALUES (2, 'AAAA');
INSERT 0 1
postgres=# SELECT * FROM public.dim_stu_icase_info
postgres-# ORDER BY NLSSORT (name, 'NLS_SORT = generic_m_ci ' );
 id | name
----+------
  1 | aaaa
  2 | AAAA
(2 rows)

postgres=# SELECT * FROM public.dim_stu_icase_info
postgres-# ORDER BY NLSSORT (name, 'NLS_SORT = generic_m_ci ' ) DESC;
 id | name
----+------
  1 | aaaa
  2 | AAAA
(2 rows)

postgres=# SELECT * FROM public.dim_stu_icase_info
postgres-# ORDER BY name;
 id | name
----+------
  1 | aaaa
  2 | AAAA
(2 rows)

postgres=# SELECT * FROM public.dim_stu_icase_info
postgres-# ORDER BY name DESC;
 id | name
----+------
  2 | AAAA
  1 | aaaa
(2 rows)

postgres=#