【環境】 CentOS 6/PostgreSQL 8.4 CentOS 7/PostgreSQL 9.2 例えば、文字列型のカラムにて、文字列と数値が混じったデータの場合にソートすると、次のようになる。 db=> SELECT * FROM sample_data ORDER BY os; os ----------------- Mac OS X 10.12 Mac O…

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…

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

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

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 するだ…

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

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

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

休日のフラグが、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（FULL）」が挙げられる。運用上は「REINDEX」も大事なので、REINDEXによりDBのサイズが変化するのを試してみた。それぞれの動作はドキュメントを参照（バージョンは実験環境に合わせてあるため8.4…

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

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…

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

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…