ロケール設定の確認方法
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",""
"１","１",""
"９","９",""
"A","A",""
"B","B",""
"a","a",""
"a","a",""
"b","b",""
"Ａ","Ａ",""
"Ｂ","Ｂ",""
"ａ","ａ",""
"ｂ","ｂ",""
"AbC","AbC",""
"aBc","aBc",""
"葉","葉",""
"波","波",""
"㈱","㈱",""
"㈲","㈲",""
"①","①",""
"②","②",""
"Ⅰ","Ⅰ",""
"Ⅱ","Ⅱ",""
"㍼","㍼",""
"㍻","㍻",""
---------------------------------------------
SELECT * FROM ltbl ORDER BY r1;
SELECT * FROM ltbl ORDER BY r2;
##### SELECT * FROM ltbl ORDER BY r4;
---------------------------------------------
---------------------------------------------
##### は理解してからにする
PostgreSQL 10のICUコレーション
