SQL

PostgreSQL で文字列と数値があるデータを(ちょっと良く)ソートする

【環境】 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…

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

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

SQLiteでIPソート

SQLite でIPアドレスを意図した順にソートする話。複雑なわりには、使いどころが微妙(´・ω・`) PostgreSQL では inet 型にCASTすると簡単なものの、SQlite には inet型は無い。0詰めしてソートしようとしても、PostgreSQL の「split_part」や「lpad」に相…

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_…

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日…

テーブル名やカラム名に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…

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

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