postgresqlについてのメモ1

シーケンス操作

setvalの第三引数

is_calledに注意。
初期化の際にはfalseにする。

インデックスについて

まだほとんどわかってないので少しずつ学習。必要に応じて。

一意インデックスは、プライマリーキーやユニーク指定すると勝手にはられる

PostgreSQLで自動的にインデックスが生成される条件 - sternhellerの日記
http://d.hatena.ne.jp/sternheller/20090717/1247853538

一意インデックス
https://www.postgresql.jp/document/8.3/html/indexes-unique.html

インデックスの種類

４種類あるっぽい。
デフォルトはバランスツリーインデックスで、キャッシュに使われる。
このb-treeは、順序の指定ができるらしい。

» PostgreSQLでインデックスを効率良く利用しよう TECHSCORE BLOG
http://www.techscore.com/blog/2014/12/04/postgresql%E3%81%A7%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%82%92%E5%8A%B9%E7%8E%87%E9%AB%98%E3%81%8F%E5%88%A9%E7%94%A8%E3%81%97%E3%82%88%E3%81%86/

アンチパターン

取り合えず三つだけなんとなく頭に入れておく

• whereで関数を使う時には注意
• indexは必要悪とも考えられる
• 2キーインデックスを有効活用するのがよい

PostgreSQLアンチパターン：インデックス関連の三本立て - Qiita
http://qiita.com/masudakz/items/6a9cfa1db7ab59ea75aa

serial型とsequence

基本はserial型はsequenceを定義するのが楽になるが、実装は一緒。
細かい制御が必要ならsequenceかな。

PostgreSQL - PostgreSQLのserial型を使うデメリット(63038)｜teratail
https://teratail.com/questions/63038

技術/PostgreSQL/後付け"serial"設定方法メモ - Glamenv-Septzen.net
https://www.glamenv-septzen.net/view/725

つまり"serial"にすると、シーケンスの生成や破棄がテーブルと連動するよう裏側で調整されます。

in句とexist句

SQLを速くするぞ―お手軽パフォーマンス・チューニング
http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink-exists

他のテーブルに存在しない行を探す

他のテーブルに存在しない行を探すには - PostgreSQL 雑記 - postgresqlグループ
https://postgresql.g.hatena.ne.jp/pgsql/20110302

mysqlからpostgresqlへきたときの概要メモ

あれ、元記事が見当たらなくなった。。

• テーブル操作のトランザクション
• join の代わりに非相関サブクエリを積極的に使う
• with 句を使ってサブクエリに名前を付けられます
• カラムにも別名を付けられます
• insert 文でレコードを挿入したときに、id や created_at の値を返すことができます。
• 生成したシーケンスに対し、以下の関数が使用できます。
  • currval() … 現在の値を取得する
  • nextval() … 次の値を取得する (現在値が 1 増える)
  • setval() … 値を設定し直す
• auto_increment は serial 型で、カラムオプションではなくデータ型
• 'infinity' '-infinity' が使えます。
• generate_series() とは、連続した整数を生成する関数です
• 再帰 SQL とは、1 回のクエリで、1 つのテーブルに何度も select 文を実行するような機能です。
• インデックスを作るとき、全部の行ではなく、条件に一致した行にだけインデックスをつけることができます。
• インデックス作成時に、カラム名ではなく任意の式を指定できます。 これを「式インデックス」といいます (昔は「関数インデックス」と呼ばれてたようです)。
• 集約関数のひとつである array_agg() を使えば、1 : N のまま取り出せます。
• json_agg() を使うと、より複雑なデータ構造で取り出せます。
• 1カラムに複数の値を設定できる。Relational 的にはどうなの？と言われそうだが、実際便利なので仕方ない。
• JSON を表すデータ型。Relational 的にはどうなの？と言われそうだが、超絶に便利なので仕方ない。
• Listen/Notify Observer パターンとか、PubSub とか。
• 別システムの DB や CSV ファイルが、あたかもテーブルかのようにアクセスできる。
• PostGIS 位置情報を扱うときの定番。近距離を扱うだけなら必要ないかもしれないけど、たとえば東京-北京間の距離を計算するときは地球が楕円体であることを考慮する必要がある。そういうのをやってくれるライブラリ。