おやじプログラマー日記

前回、PostgreSQLにおいて、複数行の整数を返すことができる関数「generate_series」を紹介しましたが、 Oracleでは関数ではなく階層問合せ用の「LEVEL疑似列」を使うことで可能になります。

以下のSQLの実行を見てください。（SQLコマンドラインから実行しています）
SQL> SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5; LEVEL ---------- 1 2 3 4 5
階層問合せにおいて「CONNECT BY」句は親子関係の条件を指定するのですが、 この場合は 「LEVEL疑似列」自身がそのまま、それ自体として条件づけられるので、 「LEVEL <= 5」により5個以下のレベル値が返るようです。 私自身もよくわかっていないのですが．．．

さらにこのSQL文をテーブルとして扱えば、以下の様にもできます。
SQL> SELECT CURRENT_DATE + X.LVL FROM 2 (SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 5) X; CURRENT_ -------- 16-03-13 16-03-14 16-03-15 16-03-16 16-03-17 ■関連ページ
⇒SELECT文に関する基礎的なこと
⇒SELECT文に関する基礎的なこと２
⇒副問い合わせを含むSELECT文について
⇒集合演算子を使ったSELECT文について
⇒CASE式を使ったSELECT文について

PostgreSQLでシステム日付を取得する場合には、以下の様なFROMの無いSQL文で行います。 PostgreSQLではFROMが必要なければ記述することはありません。

PostgreSQLの「SQL Shell(psql)」で実行します。
postgres=# SELECT NOW(); now ---------------------------- 2016-03-10 14:44:20.369+09 (1 行)

定数を同時にSELECT取得することもできます。
postgres=# SELECT NOW() AS DATETIME, 1 AS NumberTest, 'TEST' AS StringTest; datetime | numbertest | stringtest ----------------------------+------------+------------ 2016-03-10 14:47:16.987+09 | 1 | TEST (1 行)

ここで、PostgreSQLの面白い関数について紹介します。 それが「generate_series」で複数行の整数を返すことができます。

関数	動作内容
generate_series(start, stop)	グラフィックstartからstopまでの刻み1で連続する値を生成
generate_series(start, stop, step)	startからstopまでの刻みstepで連続する値を生成

以下にその例を示します。

postgres=# SELECT generate_series(1, 3); generate_series ----------------- 1 2 3 (3 行) postgres=# SELECT * FROM generate_series(1, 3); generate_series ----------------- 1 2 3 (3 行) postgres=# SELECT NumTbl.NumVal FROM generate_series(1, 3) AS NumTbl(NumVal); numval -------- 1 2 3 (3 行)
1番目の様にSELECT文のなかのカラムを記述するところにいれてもいいですし、 また、2番目の様にFROM句でテーブル扱いにしてもOKです。 さらに、3番目ではテーブルの別名として「NumTbl(NumVal)」（仮にここでの命名）とすると 生成される数値を「NumTbl」テーブルのカラム名「NumVal」で参照ができます。

この3番目の方法を使うことでいろいろ応用が利くと思います。 以下は、今日からの3日間を羅列するSQL文になります。

postgres=# SELECT current_date + N.V FROM generate_series(1, 3) AS N(V); ?column? ------------ 2016-03-11 2016-03-12 2016-03-13 (3 行)

「CSE」を使ってPostgreSQLのデータベースのテーブルに、CSVデータ入力（インポート）を行ってみます。

まず最初に、前回のCSVデータ出力で出力された「dt_data1.csv」ファイルをCSEで入力してみます。 「DBｴｸｽﾌﾟﾛｰﾗ」に表示されているテーブル「dt_data1」の上で右クリックします。 メニューの中から、真ん中下あたりの「ﾃﾞｰﾀをｲﾝﾎﾟｰﾄ」を選びます。



「ﾃﾞｰﾀをｲﾝﾎﾟｰﾄ」を選択した後に以下の図の様に、「インポートするファイルを指定して下さい」ダイアログが表示されます。 「dt_data1.csv」を選択し「開く」ボタンをクリックします。



インポート処理のメッセージがコンソールに表示されます。 「dt_data1.csv」のデータはテーブルの中に既に存在しますので、3行のエラーが表示されます。 当然インポート処理は失敗となります。



そこで、新しいCSVファイルをテキストエディタで作成してみます。 「dt_data1-inp1.csv」として以下の図の様に3件のデータを作成します。



再度「DBｴｸｽﾌﾟﾛｰﾗ」に表示されているテーブル「dt_data1」の上で右クリックし、 「ﾃﾞｰﾀをｲﾝﾎﾟｰﾄ」を選択します。 今回は「dt_data1-inp1.csv」のファイルを選択します。



インポート処理の様子が下図の様に、コンソールウインドウに表示されます。



インポートされたか確認する為、「DBｴｸｽﾌﾟﾛｰﾗ」に表示されているテーブル「dt_data1」の上で右クリックし、 「全ﾃﾞｰﾀを開く」を選択します。



これで、CSEでのCSVファイルのインポートの説明を終わりますが、 上の例でも示しましたが、インポートするCSVファイルのデータが、 テーブルに既にあるデータと キーが重複する場合は注意が必要です。

■関連記事
⇒スッキリわかるSQL入門 ドリル215問付き！ スッキリわかるシリーズ
⇒CSEからPostgreSQLへの接続
⇒CSEの使い方、PostgreSQLテーブル作成とコンソールのグリッドからのデータ入力
⇒CSEを使用してPostgreSQLテーブルの内容をCSVデータ出力（エキスポート）
⇒CSEからORACLEへの接続

達人に学ぶDB設計 徹底指南書【電子書籍】[ ミック ] 価格： 2,808円 （2016/09/29 13:53時点 ） 感想：1件

「CSE」を使ってPostgreSQLのデータベースのテーブルからCSVデータ出力を行ってみます。

「DBｴｸｽﾌﾟﾛｰﾗ」に表示されているテーブル「dt_data1」の上で右クリックします。 メニューの中から、真ん中あたりの「全ﾃﾞｰﾀをｴｸｽﾎﾟｰﾄ」を選びます。



「全ﾃﾞｰﾀをｴｸｽﾎﾟｰﾄ」を選択した後の表示が以下の図の様に、 「ファイルの保存先」を指定するダイアログが表示されます。 初めてこの処理を行う時のデフォルトのフォルダは「CSE.EXE」が存在するフォルダです。 ファイル名のデフォルトは「テーブル名.csv」ですので、今回は「dt_data1.csv」となります。



エキスポート処理のメッセージがコンソールに表示されます。



エキスポートされたCSVファイルをテキストエディタで開いてみます。 各データは全て値の前後に「"」（ダブルコーテーション）で囲まれていて、文字列として出力されています。



■さらに、データの条件付けを行ってのCSV出力を行ってみます。

「DBｴｸｽﾌﾟﾛｰﾗ」に表示されているテーブル「dt_data1」の上で右クリックします。 メニューの中から、「条件を指定してﾃﾞｰﾀをｴｸｽﾎﾟｰﾄ」を選びます。



「条件を指定してﾃﾞｰﾀをｴｸｽﾎﾟｰﾄ」を選択した後の表示が以下の図の様に、「検索条件 - dt_data1」が表示されます。


「出力列を指定」及び「ｵｰﾀﾞｰ列を指定」を指定して、エキスポートを行います。 両方にチェックを入れると以下の表示になります。 画面は最初のカラムの「datano」に検索条件として「in (2,3)」として「datano」が２または３のデータを抽出します。 さらに「ｵｰﾀﾞｰ」にもチェックを入れて「datano」順とします。


上図で「OK」ボタンをクリックすると、以下の「ファイルの保存先」を指定するダイアログが表示されます。 先ほどとは異なるファイル名（dt_data1-1.csv）を指定してみます。


エキスポートされたCSVファイルをテキストエディタで開いてみます。 「datano」が２および３のみのデータのみであり、「datano」順になっています。



以上の様に比較的簡単にテーブルの内容をCSVファイルに出力することができます。
この機能だけでも「CSE」を使ってみる価値はあると思います。

■関連記事
⇒CSEからPostgreSQLへの接続
⇒CSEの使い方、PostgreSQLテーブル作成とコンソールのグリッドからのデータ入力
⇒CSEを使用したPostgreSQLのテーブルにCSVデータ入力（インポート）
⇒CSEからORACLEへの接続



⇒スッキリわかるSQL入門 ドリル215問付き！ スッキリわかるシリーズ

達人に学ぶDB設計 徹底指南書【電子書籍】[ ミック ] 価格： 2,808円 （2016/09/29 13:53時点 ） 感想：1件

「CSE」を使ってPostgreSQLのデータベースにテーブルを作成し、データを簡単に入力してみます。

最初にテーブルの作成を行います。 CSEのメイン画面で以下の図の様にCREATE文をキー入力し、 実行ボタン（ツールバーの「！」もしくは、「メニュー」⇒「ﾃﾞｰﾀﾍﾞｰｽ」⇒「実行」）をクリックします。
コンソールには実行結果が表示されます。

作成されたテーブルが「DBｴｸｽﾌﾟﾛｰﾗ」に表示されますので、 テーブル「dt_data1」の上で右クリックします。 メニューの中から、一番上の「全ﾃﾞｰﾀを開く」を選びます。
「pg_hba.conf」はPostgreSQLがインストールされているフォルダ内の「data」フォルダ内に存在します。


「全ﾃﾞｰﾀを開く」を選択した後の表示が以下の図の様に、コンソールに「dt_data1」の内容を表示されます。 このグリッドにデータを入力していきます。（図は3件のデータを入力した様子）


グリッドにデータを入力後、「メニュー」⇒「ﾃﾞｰﾀﾍﾞｰｽ」⇒「編集の反映」を選択します。 以下の確認メッセージが表示されますので、「OK」をクリックします。


テーブルへの登録のメッセージがコンソールに表示されます。


再度「DBｴｸｽﾌﾟﾛｰﾗ」のテーブル「dt_data1」上で「全ﾃﾞｰﾀを開く」を選択すれば、 入力されたデータがコンソールの部分に表示されるはずです。
■関連記事
⇒CSEからPostgreSQLへの接続
⇒CSEを使用してPostgreSQLテーブルの内容をCSVデータ出力（エキスポート）
⇒CSEを使用したPostgreSQLのテーブルにCSVデータ入力（インポート）
⇒CSEからORACLEへの接続

達人に学ぶDB設計 徹底指南書【電子書籍】[ ミック ] 価格： 2,808円 （2016/09/29 13:53時点 ） 感想：1件

久々にCSE「Common SQL Environment」を使ったPostgreSQLへの接続を行う必要がありましたので、 その時のやり方をまとめてみました。
PostgreSQLと言えば「pgAdminⅢ」がありますが、CSEも使い勝手がいいので利用しています。

まず最初にPostgreSQLのクライアント認証の設定を変更する為に「pg_hba.conf」の編集を行います。
「pg_hba.conf」はPostgreSQLがインストールされているフォルダ内の「data」フォルダ内に存在します。
（私の環境では　C:\Program Files\PostgreSQL\9.2\data　に存在します）
「localhost」からアクセスをする場合は、「pg_hba.conf」をテキストエディタで開いて、 以下の様に設定して下さい。（「＃」はコメント行です。）

# IPv4 local connections: #host all all 127.0.0.1/32 md5 host all all 127.0.0.1/32 trust

尚、全てのアクセスを許可する場合は、以下の様に設定して下さい。

# IPv4 local connections: #host all all 127.0.0.1/32 md5 host all all 0.0.0.0/32 trust

上記の修正が終わった後で、「pg_hba.conf」を上書き保存し、PostgreSQLのサービスを再起動します。

次に、「libpq.dll」をCSEが存在するフォルダに入れます。
このDLLですが、現在のCSEの紹介されているページのリンクは切れている様ですので、 以下のURLのところで該当するファイル （「日本語Windows版 libpq」..... libpq-6.5jp.zip）をダウンロードし 回答して、「libpq.dll」をコピーします。 （URLは直接リンクではありませんので、文字列をコピペして下さい）
「http://www.psn.ne.jp/support/database/psql.html」

CSEの起動を行い。「メニュー」の「ﾃﾞｰﾀﾍﾞｰｽ」⇒「接続」を選択します。

コンソール画面に「接続されました」の表示がされたら、接続が完了したことになります。

■関連記事
⇒スッキリわかるSQL入門 ドリル215問付き！ スッキリわかるシリーズ
⇒CSEの使い方、PostgreSQLテーブル作成とコンソールのグリッドからのデータ入力
⇒CSEを使用してPostgreSQLテーブルの内容をCSVデータ出力（エキスポート）
⇒CSEを使用したPostgreSQLのテーブルにCSVデータ入力（インポート）
⇒CSEからORACLEへの接続

データベースは日々データに変化が発生しますので、毎日ある時点で定期的にバックアップする必要が出てきます。

バックアップ・リストアは pgAdmin を使えば簡単にできます。 しかし、定期的なバックアップ作業はバッチ処理とし、サーバのスケジューラで起動することが一般的だと思います。

Windows上のPostgreSQLのバックアップについて、コマンドプロンプトから行う方法を説明します。 尚、このコマンドはPostgreSQLサーバーの稼動しているマシンで実行することが前提になります。

■全データベースのバックアップ

pg_dump --format=t --file=bakup.dmp --username=postgres test

対象となるデータベースは test で、出力されるファイル名を bakup.dmp とし、ファイル形式はテキストで生成します。
データベースへのユーザ名は postgres で、これはPostgreSQLを最初に導入したときのAdminのユーザです。 このコマンドにはパスワードの指定がありませんが、pgAdminでログインし、パスワードの保存をしてあれば指定が必要ありません。
（エディタで %APPDATA%\postgresql\pgpass.conf にパスワードを登録してもOKです）

pg_dumpのコマンドですが、「pg_dump --HELP」と指定すれば、コマンドの一覧が表示されます。 多くのコマンドが表示されますが、中で使用すると言えば、テーブルの指定ぐらいでしょうか。

■データベースの内のあるテーブルのバックアップ

pg_dump --format=t --file=bakup.dmp --username=postgres --table=table1 test

全データベースのバックアップに --table=table1 のコマンドを付加し table1 のテーブルをバックアップします。