PostgreSQLメモ

PostgreSQLとは †

• PostgreSQL: The world's most advanced open source database*1
• NPO法人 日本PostgreSQLユーザ会*2

文字列置換をする方法 †

• replace関数を使う

replace(original, needle, replace)

• original の中から needle を見つけ出し， replace に置き換えてくれる．
• 何故か，こういうときは例がneedleなんだよな．

日付のフォーマットを変更 †

• to_char関数を使う

to_char(date, 'YYYY/MM/DD');
to_char(time, 'HH:MI:SS');

• to_charを使えばepoch timeを日付に戻せる

to_char('1970-01-01'::date + (unixtime || 's')::interval, 'YYYY-MM') from test;

• || 's')::intervalがミソ．1970-01-01 00:00がUNIXtime0秒なのだ．
• 参考：PostgreSQLでUNIXTIMEを使うときのTIPS*3

日付時刻関数のextract †

• 以下で，時刻，日付，タイムスタンプ型から特定の部分だけ引き抜ける

extract(''field'' from ''column'')

• 以下で「時」の部分だけ引き出せる

extract(hour from timestamp)

• 以下で年数だけ引き出せる

extract(year from date)

• 以下fieldに使えるフォーマット一覧
  • century (世紀)
  • day (日)
  • decade (十年)
  • dow (曜日)
  • doy (通算日数)
  • epoch
  • hour (時)
  • microseconds (マイクロ秒)
  • millennium (ミレニアム)
  • milliseconds (ミリ秒)
  • minute (分)
  • month (月)
  • quarter (四半期)
  • second (秒)
  • timezone_hour
  • timezone_minute
  • week (週)
  • year (年)
• あと，date_truncも便利
• 参照:日付/時刻関数と演算子*4

文字列関数 †

• length:文字列の文字数を返す
• char_length:文字列の文字数を返す(lengthと等価)
• octet_length:文字列のバイト数を返す
• bit_length:文字列のビット数を返す
• ただし，EUC文字列で半角カナは2バイト扱いっぽい．
• 参考：ポストグレス文字列メモ(Hishidama's PostgreSQL String Memo)*5

大文字小文字を区別せずに検索する †

• 以下のクエリ文では'Apple'にしかヒットしない

  select text from note where text ~ 'Apple';

• 'Apple'も'APPLE'も'ApPlE'にもヒットさせたい場合は，以下のクエリ文を使う

  select text from note where lower(text) ~ 'apple';

• ただ，PostgreSQLには ILIKE という句が存在するので，そっちがベター

  select text from note where text ILIKE 'apple';

• 参考:ILIKE - 条件検索で大文字小文字を区別させない*6
• 参考:大文字/小文字の区別しないで検索したい場合 - Oracle - 教えて！goo*7

ジャンル:PostgreSQL

PostgreSQLが容量超過した。 †

• 契約してるPostgreSQL容量を超過したので、データベースのサイズを縮小した。
• その時の記録

  SELECT relname, reltuples as rows, (relpages * 8192) as bytes FROM pg_class  order by bytes desc;

• pg_class という特殊テーブルから情報を抽出する
• relpagesが8192バイトごとの情報なので、バイト数に変換するには8192をかける

  => SELECT relname, reltuples as rows, (relpages * 8192) as bytes FROM pg_class  order by bytes desc;
               relname             |  rows  |   bytes
  ---------------------------------+--------+-----------
   hoge_history                    | 534201 | 157196288
   access_history                  |  44502 |  14344192
   user_access                     |  36037 |   9109504
   user_info                       |  11368 |   4456448
   (中略)

• 削除にはdeleteコマンドを使う

  => DELETE FROM hoge_history;
  DELETE 535056

• しかし、これだけでは見た目の情報が減らない

  => DELETE FROM hoge_history;
  DELETE 535056
  => SELECT relname, reltuples as rows, (relpages * 8192) as bytes FROM pg_class  order by bytes desc;
               relname             |  rows  |   bytes
  ---------------------------------+--------+-----------
   hoge_history                    | 534201 | 157196288
   (中略)

• 完全に削除するためにはVACUUMコマンドを使う*8

  => VACUUM FULL hoge_history ;
  VACUUM
  => SELECT relname, reltuples as rows, (relpages * 8192) as bytes FROM pg_class  where relname = 'hoge_history';
     relname    | rows | bytes
  --------------+------+-------
   hoge_history |    0 |     0
  (1 row)

• 参考:PostgreSQL :: テーブルのサイズ(物理容量)とレコード数を調べる [Tipsというかメモ]*9