PostgreSQLメモ

PostgreSQLとは†

• PostgreSQL: The world's most advanced open source database&note{postgresql-official:postgresql-official: The world's most advanced open source database, 2013-03-27閲覧};
• NPO法人 日本PostgreSQLユーザ会&note{postgresql-jp-official:NPO法人 日本PostgreSQLユーザ会, 2013-03-27閲覧};

文字列置換をする方法†

• 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&note{postgresql-unixtime-zuzra:PostgreSQLでUNIXTIMEを使うときのTIPS, 2006-11-28発表, 2013-03-27閲覧};

日付時刻関数の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も便利
• 参照:日付/時刻関数と演算子&note{datetime-function-postgresql:日付/時刻関数と演算子, PostgreSQL 9.2.0準拠, 2013-03-27閲覧};

文字列関数†

• length:文字列の文字数を返す
• char_length:文字列の文字数を返す(lengthと等価)
• octet_length:文字列のバイト数を返す
• bit_length:文字列のビット数を返す
• ただし，EUC文字列で半角カナは2バイト扱いっぽい．
• 参考：ポストグレス文字列メモ(Hishidama's PostgreSQL String Memo)&note{string-function-hishidama:ポストグレス文字列メモ(Hishidama's PostgreSQL String Memo), 2003-07-19発表, 2003-07-2更新, 2013-03-27閲覧};

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

• 以下のクエリ文では'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 - 条件検索で大文字小文字を区別させない&note{ksknet-postgresql-ilike:ILIKE - 条件検索で大文字小文字を区別させない, 2013-03-25閲覧};
• 参考:大文字/小文字の区別しないで検索したい場合 - Oracle - 教えて！goo&note{oracle-ilinke-okwave:大文字/小文字の区別しないで検索したい場合 - Oracle - 教えて！goo, 2005-05-17発表, 2013-03-25閲覧};

ジャンル: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コマンドを使う&note{vacuum-command-postgresql-7.3.4:VACUUM, PostgreSQL 7.3.4相当, 2013-09-15閲覧};

  => 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というかメモ]&note{check-physical-size-and-record-number-of-table:PostgreSQL :: テーブルのサイズ(物理容量)とレコード数を調べる [Tipsというかメモ], 2013-09-15閲覧};

最新の20件