カテゴリー: DB/SQL

つづけて、1つのカラムの中にカンマ区切りでデータが入っていた場合に、
これを縦にする方法。

SELECT regexp_split_to_table(item_cd,’,’) item_cd FROM table_name

item_cdカラムに「a,b,c」と入力されているとすると、出力は

a
b
c
となる。

財布を落とした。
警察へ行った。
紛失届を作成するのにあれやこれや聞かれて、答えれば答えるほど（なくしたものの大きさに）堪える・・・
って、こんな時にもダジャレが出るぐらいに凹んでる。

さて、仕事しよう。

data1,data2,data3 というカラムがあるとして、それを

data1
data2
data3
と縦に並べたいときのやり方。

SELECT unnest(array[data1, data2, data3]) as data_col
FROM table_name;

PostgreSQLで現在の接続数をみたい場合のSQL文

詳細
SELECT * FROM pg_stat_activity
ORDER BY query_start;

サマリー
SELECT datname, usename, COUNT(*)
FROM pg_stat_activity
GROUP BY datname, usename;

文字型のフィールドを数値化して並び替えする場合はキャストするらしい。

ORDER BY cast( [文字型フィールド] as int)

って感じ。
（[文字型フィールド] をフィールド名に変える。）

このブログのPostgreSQL情報もどんどん増えてきていい感じだ。

PostgreSQLは配列を使えるので、簡単な書き方が何かあるはずだと思っていたら、やっぱりあった。

SELECT
TO_CHAR(current_timestamp,’YYYY年MM月DD日’) ||
(ARRAY[‘(日)’,'(月)’,'(火)’,'(水)’,'(木)’,'(金)’,'(土)’])
[extract(‘dow’ FROM current_timestamp) + 1] as now_date

“2013年04月18日(木)”

ってな感じで表示される。
便利だなぁ～～～

8桁で日付を表しているときの日付計算について

下の例では、8桁日付のところを文字列にしているが
　COALESCE(TO_CHAR(20120921,’99999999′),’99991231′)
とか置きかえてやれば、数値でも転用できる。

——————————————

SELECT to_char(to_timestamp(‘20120921′,’YYYYMMDD’) + ’20 days’,’YYYYMMDD’) as add_date;

とすると、結果は “20121011” と返ってくる。

SELECT to_char(to_timestamp(‘20120921′,’YYYYMMDD’) + ’20 days’,’YYYYMM01′) as add_date;

とすると、結果は “20121001” と返ってくるので月初めの日付を取得することができる。

要は、TO_TIMESTAMPメソッドでタイムスタンプ型にしてやることのようだ。
TO_DATEでは計算はできなかった。

[2013/05/09追記]
select to_date(20120921::text,’YYYYMMDD’)
::で型変換(キャスト)してしまえば、もっと楽ってわかった。

私はPostgreSQLが好きだ。
無料だし、機能も結構充実してる。
MySQLのGUI管理画面はいかにも重そうなのだけど、PostgreSQLのはそんなに重くない。
（よく強制終了しているけど）

この間、他の人の書いたソースコードにRETURNING句があった。
一目見ただけで、何のためのものかは分かったのだが、
「こんなことできるんだ・・・」
と衝撃的だった。
INSERT INTO ….. RETURNING A,B
とかにすると、挿入したデータのカラム値を配列で返してくれるそうな。

便利だ！
Nextval()とか、CurVal()とかしなくてもいいのね！
なんて素敵なんだ！

PHPのOracle接続について、なんとなくわかったことを書いておく。

WindowsServer2008にApacheとPHPを入れたところ、OCI8のライブラリ情報がphpinfo()で表示されなかった。

なんでじゃぁ～～～～!!

と、迷っていたのだが、詰まるところPHPが32bit対応の場合は、Oracle自体も32bitでないと駄目なようで、
Oracleが64bitの場合は、32bitのOracle Clientを入れるとか、32bitで仲介するツール群をインストールするとかしなければならないようである。

もう１つの手といえば、おそらくPHP自体を64bit用にコンパイルすることだろう。
コンパイルしたものも実際にあるようだが、商用で使うのであれば、自身でコンパイルしておいたほうがいいと思う。

MDB2でOracle11gに接続しているのだが、なんだか実行速度が遅い。
これはひょっとすると、Oracle10gのクライアントをちゃんとアンインストールしなかったからなのでは？
と思い、Oracle関係のファイルをすべてアンインストール後、再度11gを入れなおしたのだが、これが災いして、11gに接続できなくなった。

php_oci8_11g.dllはモジュールが見つからないとかアラームが出て、Apacheもまともに起動できなくなった。

ウゲェ～

と思い、2,3日を悶々と過ごし、ようやく再接続に成功した。

原因がよくわからないのだが、
結局は、アンインストールしても環境変数にゴミが残ってしまっててダメだったようだ。
環境変数のPathやTNS_ADMINが正しく設定されているか確認後、Windowsを再起動してApacheは起動できた。
phpinfoで確認して、oci8が動作していることも確認。

で、実行速度は・・・と言えば、変わらなかった。
タイムアウトでシステムエラーになるので、php.iniのmax_execution_timeの値を変えてみて、とりあえずエラーにはならないようにしたのだが。

結局SQL文が悪いのか？

ようやくOracle。
Silver Fellowという資格は持っているが、とったのが10年ほど前なのでもう忘れてしまった。

仕事で使わないと忘れてしまうのですよ。

もうDBはできているという前提で。
(DBは管理ツールで作成できるんで、まぁいいとしても、SYSTEM権限のPWって、どうやって決めるのだ・・・と思いつつ、後日に持越し）

CREATE USER [USER_NAME] IDENTIFIED BY [PASSWORD];
GRANT CREATE SESSION TO [USER_NAME];

で、とりあえずユーザをつくって権限を与えるところまで完了。
テーブルとかは、Object Browzerで作るからまぁいいや。

日付の差分を日数で取得する方法
first_date,last_dateがTimestamp型の場合、差分は1day 10:20:30 みたいに、日数と時間で表示される。

だけど、日数だけで取得したいときは、

SELECT first_date,last_date, cast(last_date as DATE) – cast(first_date as DATE) term FROM [table_name]

と、キャストして実行するそうだ。
勉強になった。

ネットでいろいろ調べてようやく参照できるようになったので、メモ。
PostgreSQLのODBC Driverはすでにインストール済みとして書いてるよ。

まずは、コンパネ→管理ツール→Microsoft ODBC アドミニストレータで、システムDNSにODBCの接続設定を追加する。
（ユーザーDNSじゃないところがポイント）

次に、SQLServerを開いて、新しいリンクサーバーを作成する。
「全般」ページで以下を設定。
　サーバの種類はその他のデータベース。
　プロバイダは Microsoft OLE DB Provider ODBC Drivers。
　製品名はPostgreSQL。
　データソースは、最初に作成したODBC接続設定の名称。

「セキュリティ」ページで以下を設定。
　このセキュリティコンテキストを使用するを選択して、
　リモートログインとパスワードにPostgreSQLのDBのユーザー名とパスワードを入力。

これで、OKボタンを押せば、リンクサーバが出来上がり。

あとの注意点は、Select文は、OPENQUERYを使うというところ。

SELECT * FROM OPENQUERY([リンクサーバ名],’SELECT * FROM [スキーマ名(public)].[テーブル名]’)

といった感じ。

丸一日かかって、OracleとPostgreSQL両方のDBを参照するやり方がわかった。
やっぱり、勉強が足りないな。

[追記：64bit版OS利用時(WindowsServerとか)]
64bit版OSの場合は、PostgreSQLのODBC Driver(64bit版)をインストールすれば、あとは同様に処理ができるようである。

他のサイトでもこの手の記事は多いんだけど、設定のテキストボックスに何を入れていいかまで書いてないのだ。
ここまで書いてもらえないと、応用力がないのでわからんのよ、私は。

psqlでテーブルの内容をドカーンとファイルに落とす。

psql
DB名 #= COPY [テーブル名] TO [ファイル名] (FORMAT csv);

ファイル名のところは、
E’D¥:¥¥xxx.csv’

というように、エスケープ処理が必要になることを忘れない。

逆に、CSVファイルのデータをテーブルに保存する場合は、

psql
DB名 #= COPY [テーブル名] TO [ファイル名] with csv;

psqlのコマンドのエラーが文字化け・・・

エンコードがUTF8になっているのが原因なので、都度エンコードをWindows用にSJISにしてあげる。

psql
DB名#= ¥encoding;

で現在の文字コードが確認できる。

psql
DB名#= ¥encoding SJIS;

で文字コードをSJISにしてくれる。

そもそもの発端は、私がPostgreSQLのpostgreユーザのパスワードを忘れてしまったことにある。

忘れてしまったので、コマンドプロンプトを起動させて再設定をしたその後、pgAdminでローカルのDBにアクセスできなくなってしまった。

あぁ～、またパスワード忘れたかなぁ・・・
あぁ～、再インストールかなぁ・・・

などと思ってコマンドプロンプトからpsqlを起動させてみた。
「つながらないよ」「ホントにDBあんの？」みたいな返答が返ってきたので、「あるよ？」と思ったが、「サービス開始されてないんじゃない？」って思って、Windowsの管理画面を見る。

案の定、サービス起動されてませんがな・・・

パスワードを変えちゃったせいで、起動できなくなっていたようで、さっそく新しいパスワードを設定してサービスを開始。
pgAdminで接続したら、ちゃんと見えるようになった。

なんか、自分で原因作って、自分で悩んで、自分で解決って・・・
一人で遊んでしまった。

普段はpgAminを使ってSQLを実行しているのだけど、SQLファイルがデカすぎて、ファイルを開けない場合は、PSQLをつかうか、ファイルの中身を分散してSQLを流していくしかない。

ということで、PSQLを使ったSQLファイル実行。

コマンドプロンプトを開いて、cd等でpostgreSQLのpsql.exeのあるフォルダ（bin)まで遷移し、以下のコマンドを実行

psql -U yyy -h localhost xxx
ユーザ yyyのパスワード：（ここにパスワードを入力してEnter）

xxx => ￥i dump.sql

（xxx：DB名、yyy：ユーザID、dump.sql：SQLファイル名）

DBがローカルにない場合は、localhostの箇所をDBサーバーのIPアドレスに変えればよい。

この例の場合はdump.sqlをpsql.exeと同じフォルダに入れている。

終了するときは、

xxx => ￥q

でpsqlを終了する。

以下のようなテーブルがあったとして
【t_sample】
id,element,rank,…
1,’A’,1,…
1,’B’,2,…
1,’C’,3,…
2,’D’,1,…

これを
1,’A,B,C’
2,’D’
ってな感じで取得する方法

SELECT id
 , ARRAY_TO_STRING(ARRAY(SELECT element
                         FROM t_sample a
                         WHERE a.id = b.id ORDER BY a.rank),',') elements
 FROM (SELECT id FROM t_sample GROUP BY id ORDER BY id) b

ARRAY_TO_STRING：取得した配列をARRAY_TO_STRINGでカンマ区切りにする。

SQL文が少々複雑で面倒だけど、使い勝手はよさげ。

[編集メモ] 2017/3/29 タイトルと見栄えを修正

alter table [table名] alter column [column名] type numeric(1,0) using 0 ;
alter table [table名] alter column [column名] SET NOT NULL;
alter table [table名] alter column [column名] SET DEFAULT 0;

1つめは型変更(数値型に変える、すべて0に置きかえる)
　using to_number([column名],0)として、カラムの値を数値に置き換える場合もあり。
2つめは、NOT NULL定義にする場合。NOT NULLを外す場合はDROP NOT NULLとする。
3つめは、Default値の定義。こちらもDROP DEFAULT とすれば外れる。

— オーナーの変更
ALTER TABLE [table] OWNER TO [user];

— テーブル削除
DROP TABLE [table];

OracleでいうところのNVLって、postgreにはないと思ってたけど、あった。

COALESCE関数:coalesce(paraA,paraB)

paraA is NULL なら、paraBに置きかえてくれる。