PostgreSQL

createuser 時にロールの属性設定をする方法

CnetOS のベースリポジトリにあるPostgreSQLのバージョンによって、createuser の動作が異なる件。知人に何回も聞かれるのでメモ。 CnetOS 6.x(PostgreSQL 8.4)の場合 $ createuser -U postgres test1 Shall the new role be a superuser? (y/n) n Shall t…

CentOS 7 の psql で tab 補完が大文字にならない件

今更だけど、人に聞かれたついでにメモ。psql で tab を押してSQLキーワードを補完できるが、バージョンによって大文字だったり小文字だったり違いがある。CentOS 6 / psql (PostgreSQL) 8.4.20 db=> sel ↓ tab db=> SELECT →SQLキーワードが大文字で補完さ…

ORDER BY で CASE文を使って特定の行を上位に表示する

CentOS 6.x の PostgreSQL 8.4 と SQLite3 で動作確認。何らかのデータをソートして表示する際、特定の行だけは上位に表示したい事がある。例えば、国マスタをコード順でソートした時に、日本を一番上に表示し、他はコードでソートして表示したい、等々。 単…

PostgreSQLでIPアドレスをソートする

PostgreSQL にて、IPアドレスをtext型に保存している場合、そのままソートしても意図したようにソートされない。 SELECT ip FROM nic ORDER BY ip; ip -------------- 192.168.1.1 192.168.10.1 192.168.2.1 192.168.20.1 192.168.3.1 inet 型にCAST するだ…

UserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する

PostgreSQLにて、DBに保存されたUserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する。 ロボットの事は考慮していない。 ChromeのUserAgentには、「Safari」という文字列が含まれるので、Safariより先に判定する。 「MSIE」は「IE 10」まで、「IE 11…

PostgreSQLで現在接続中のDBのサイズを取得する方法

他にもっと簡単な方法があるかもしれないけど、必要な度に調べるのが面倒なのでメモ。 接続中のDB名を取得 test_db=> SELECT current_database(); current_database ------------------ test_db 接続中のDBサイズの取得 test_db=> SELECT pg_size_pretty(pg_…

rsyslog-pgsql で $template にSQLを定義する時は STDSQL を使う

CentOS 6.7 の rsyslog で他のサーバ等から syslog を TCP/UDP で受け取り、ログを PostgreSQL へ登録しようとした時のこと。環境は以下の通り。全てのサーバは同一セグメント内にある。【syslog を受け取るサーバ】 CentOS 6.7 2.6.32-573.8.1.el6.x86_64 r…

PostgreSQLでカンマ区切りのデータを行に変換する

休日のフラグが、text型のカラムに365日カンマ区切りで保存されているデータがある。例えば、次のようなSELECT結果になるもの。 SELECT hol_flg FROM holiday_tbl WHERE nendo=2013 <結果> 0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,・・・省略(365日…

PostgreSQLのVACUUM/REINDEXでDBのサイズが減るか実験

PostgreSQLで定常的に必要なメンテナンスと言えば、「VACUUM(FULL)」が挙げられる。運用上は「REINDEX」も大事なので、REINDEXによりDBのサイズが変化するのを試してみた。それぞれの動作はドキュメントを参照(バージョンは実験環境に合わせてあるため8.4…

テーブル名やカラム名にSQLの予約語を使う方法

SQLの可読性が下がる上にバグになりやすいけど、こんな事もできるという程度のメモ。 PostgreSQLの場合、テーブル名やカラム名で予約語を使うには、ダブルクォートで囲う。 文字列にダブルクォートが入っている場合は、重ねてエスケープする。 psql=> CREATE…

SQLのIN句で複数の条件を指定する

SQLのIN句で複数の条件を指定する方法のメモ。PostgreSQL 8.4 で確認。適当なテーブルを作成 $ createdb in_test_db $ psql in_test_db in_test_db=> CREATE TABLE sample(id serial primary key, name text default '', age integer default 0); NOTICE: CR…

psqlの結果表示がずれる場合の対処

環境CentOS 6.5 PostgreSQL 8.4(yum版) psqlの実行結果の表示がずれて見難い。 test_db=> SELECT user_id, name, cnt FROM test_tbl; user_id | name | cnt ---------+--------------------+----- 145 | 伊達 正宗 | 2 253 | 織田 信長 | 2 389 | 武田 信…

IPアドレスを0埋め/0埋め除去してSELECTするSQL

IPアドレスが登録されているテーブルにSELECTしても上手くソートできない。IPアドレスを0埋め(0詰め)しながらSELECTすれば、上手くソートできるので、やってみる。【環境】 PostgreSQL 8.3 テーブル:NIC カラム :ip_adr 以下のように、0埋めしながらSELE…

DB/tableサイズを取得するSQL

PostgreSQL8.3~8.4にて。7.x、8.0~8.2、9.xは未確認。 全てのデータベースのサイズを取得するSQL SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database; 特定のデータベースのサイズを取得するSQL SELECT pg_size_pretty(pg_datab…