SQL
【環境】 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…
CentOS 6.x の PostgreSQL 8.4 と SQLite3 で動作確認。何らかのデータをソートして表示する際、特定の行だけは上位に表示したい事がある。例えば、国マスタをコード順でソートした時に、日本を一番上に表示し、他はコードでソートして表示したい、等々。 単…
SQLite でIPアドレスを意図した順にソートする話。複雑なわりには、使いどころが微妙（´・ω・｀） PostgreSQL では inet 型にCASTすると簡単なものの、SQlite には inet型は無い。0詰めしてソートしようとしても、PostgreSQL の「split_part」や「lpad」に相…
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_…
休日のフラグが、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日…
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…
IPアドレスが登録されているテーブルにSELECTしても上手くソートできない。IPアドレスを0埋め（0詰め）しながらSELECTすれば、上手くソートできるので、やってみる。【環境】 PostgreSQL 8.3 テーブル：NIC カラム ：ip_adr 以下のように、0埋めしながらSELE…
