Skip to content

Instantly share code, notes, and snippets.

@upinetree

upinetree/pg_collation.md

Last active January 20, 2021 11:12
Show Gist options
  • Save upinetree/9f643d02bdb8d245b66c to your computer and use it in GitHub Desktop.
Save upinetree/9f643d02bdb8d245b66c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Takuya Matsumoto revised this gist Jul 2, 2015. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions pg_collation.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,14 @@ ja_JP.UTF-8
    > 目的のlocaleを確認
    $ be bin/rails db
    =# SHOW LC_COLLATE;
    lc_collate
    -------------
    ja_JP.UTF-8
    (1 row)
    > データベースのcollationにも入っているのに…
    =# \l
    > templateのCollateにja_JP.UTF-8が設定されているのに…
  2. Takuya Matsumoto revised this gist Jul 2, 2015. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions pg_collation.md
    View file
    Original file line number Diff line number Diff line change
    @@ -25,6 +25,9 @@ $ be bin/rails db
    =# \l
    > templateのCollateにja_JP.UTF-8が設定されているのに…
    =# ALTER TABLE users ALTER COLUMN kana TYPE varchar COLLATE "ja_JP.utf8";
    ERROR: collation "ja_JP.utf8" for encoding "UTF8" does not exist
    =# SELECT * from pg_collation;
    > ja_JP.UTF-8に対応するcollationがない…
  3. Takuya Matsumoto revised this gist Jul 2, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion pg_collation.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    ## 特定のテーブルのカラムにだけCOLLATIONを設定する方法

    ```bash
    ```
    $ be bin/rails db
    =# ALTER TABLE users ALTER COLUMN kana TYPE varchar COLLATE "ja_JP.utf8";
    > 特定のカラムにだけ付与
  4. Takuya Matsumoto revised this gist Jul 2, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion pg_collation.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    ## 特定のテーブルのカラムにだけCOLLATIONを設定する方法

    ```
    ```bash
    $ be bin/rails db
    =# ALTER TABLE users ALTER COLUMN kana TYPE varchar COLLATE "ja_JP.utf8";
    > 特定のカラムにだけ付与
  5. Takuya Matsumoto renamed this gist Jul 2, 2015. 1 changed file with 0 additions and 0 deletions.
    0 pg_collation → pg_collation.md
    View file
    File renamed without changes.
  6. Takuya Matsumoto revised this gist Jul 2, 2015. No changes.
  7. Takuya Matsumoto created this gist Jul 2, 2015.
    33 changes: 33 additions & 0 deletions pg_collation
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    ## 特定のテーブルのカラムにだけCOLLATIONを設定する方法

    ```
    $ be bin/rails db
    =# ALTER TABLE users ALTER COLUMN kana TYPE varchar COLLATE "ja_JP.utf8";
    > 特定のカラムにだけ付与

    =# \d staffs
    > 付与したことを確認
    ```

    ## お目当てのCOLLATIONが存在しないときに作る方法

    macにbrewで入れたpostgresqlに`ja_JP.UTF-8`がなかったので作りました。

    ```
    $ locale -a | grep ja
    ja_JP
    ja_JP.eucJP
    ja_JP.SJIS
    ja_JP.UTF-8
    > 目的のlocaleを確認

    $ be bin/rails db
    =# \l
    > templateのCollateにja_JP.UTF-8が設定されているのに…

    =# SELECT * from pg_collation;
    > ja_JP.UTF-8に対応するcollationがない…

    =# CREATE COLLATION "ja_JP.utf8" (LOCALE = 'ja_JP.UTF-8');
    > つくられた
    ```