2018年11月12日

ロケールのテスト

ロケール設定の確認方法
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
\l
---------------------------------------------
データベース作成
CREATE DATABASE t1db;

CREATE DATABASE t2db --encoding = UTF-8 --locale = ja_JP.UTF-8 --template = template0;
CREATE DATABASE t3db --encoding = UTF-8 --locale = C --template = template0;

CREATE DATABASE t5db  WITH TEMPLATE="template1" ENCODING='EUC_JP'

CREATE DATABASE t6db encoding 'SJIS';
CREATE DATABASE t7db encoding 'EUC_JP';
CREATE DATABASE t8db encoding 'UTF8';
CREATE DATABASE t9db encoding 'UTF-8';

CREATE DATABASE test10 ENCODING 'UTF-8' LC_COLLATE 'ja-x-icu' LC_CTYPE 'ja-x-icu';

---------------------------------------------
データベース変更
\c t1db;
---------------------------------------------
テーブル作成
CREATE TABLE ltbl
(
  r1   VarChar(4),
  r2   text,
  r3   text
#####  r4   text COLLATE "ja-x-icu"
---------------------------------------------

ltb.csv (utf8で保存)
---------------------------------------------

PostgreSQLのCOPYコマンドで、CSVファイルからのテーブルへのインポート
COPY ltbl FROM '/usr/local/src/ltb.csv' WITH CSV;


COPY テーブル名 FROM /フルパス/CSVファイル' WITH CSV;
ヘッダ付き
COPY テーブル名 FROM /フルパス/CSVファイル' WITH CSV header;
---------------------------------------------

#####  UPDATE ltbl
#####  set  r4  = r2;

---------------------------------------------


ltb.csv (utf8保存)
---------------------------------------------
"あ","あ","(清音)"
"い","い","(清音)"
"は","は","(清音)"
"ば","ば","(濁音)"
"ぱ","ぱ","(半濁音)"
"ハ","ハ","(清音)"
"バ","バ","(濁音)"
"パ","パ","(半濁音)"
"ア","ア",""
"イ","イ",""
"ハ","ハ",""
"バ","バ",""
"パ","パ",""
"1","1",""
"9","9",""
"1","1",""
"9","9",""
"A","A",""
"B","B",""
"a","a",""
"a","a",""
"b","b",""
"A","A",""
"B","B",""
"a","a",""
"b","b",""
"AbC","AbC",""
"aBc","aBc",""
"葉","葉",""
"波","波",""
"","",""
"","",""
"@","@",""
"A","A",""
"T","T",""
"U","U",""
"","",""
"~","~",""
---------------------------------------------

SELECT * FROM ltbl ORDER BY r1;

SELECT * FROM ltbl ORDER BY r2;

#####  SELECT * FROM ltbl ORDER BY r4;




---------------------------------------------




---------------------------------------------
#####  は理解してからにする
PostgreSQL 10のICUコレーション


posted by a23 at 17:53| Comment(0) | PostgreSQL