tomimemo

postgresqlメモ

最終更新:

匿名ユーザー

- view
管理者のみ編集可

数値フォーマット

0で埋める場合は以下
select to_char(12345, '00000000');

  to_char  
-----------
  00012345

現在日付からの加算と減算

以下の書式で書けた。便利。
■現在時刻から1年2ヶ月3日4時間5分6秒後を取得
SELECT
 now() 
,now() + '1year 2month 3day 4hour 5minute 6second';

              now              |           ?column?            
-------------------------------+-------------------------------
 2006-01-10 16:20:42.203232+09 | 2007-03-13 20:25:48.203232+09
(1 row)


■現在時刻から1年2ヶ月3日4時間5分6秒前 を取得
SELECT
 now() 
,now() + '1year ago 2month ago 3day ago 4hour ago 5minute ago 6second ago';

              now              |           ?column?            
-------------------------------+-------------------------------
 2006-01-10 16:21:31.433893+09 | 2004-11-07 12:16:25.433893+09
(1 row)

psqlでファイルを読み込んでクエリ実行

psqlが実行できるユーザになる
# su postgres
ユーザのカレントディレクトリにてファイル作成
# vi /home/postgres/select.sql
select id,name from member;
以下で実行
# psql DB名 < /home/postgres/select.sql
実行結果をファイル出力したい場合は以下
# psql DB名 < /home/postgres/select.sql > 出力ファイル名

postgresql8.1.0windows版インストール

最初は8.1.1をインストールしようとしたが日本語版がまだでていなかったようなので8.1.0で妥協。http://www.postgresql.org/ftp/binary/v8.1.0/win32/ からpostgresql-8.1.0-1-ja.zipをダウンロード。解凍して作成されたディレクトリ内の postgresql-8.1-ja.msi を実行。以下インストール時の様子。画像が切れてるな...






















データのバックアップ

データベース全体をバックアップしてdb_backup.outというファイル名で出力する場合。
# su - postgres
$ /usr/local/pgsql/bin/pg_dumpall -o >
  /usr/local/pgsql/data/backup/db_backup.out
(以下1行で書く)
cronとかに登録しておけば自動バックアップできる。例えば毎日午前5時にバックアップする場合。
# crontab -e
(以下1行で書く)
0 5 * * * su - postgres -c 
'/usr/local/pgsql/bin/pg_dumpall -o >
/usr/local/pgsql/data/backup/db_backup.out'

簡単にテーブル作成

psqlでテーブルを作成する場合、ひとつひとつ手入力でテーブル作成するのは面倒。その場合は、SQL文を記述したファイルを用意してバッチで一発作成。

ファイル格納ディレクトリ作成。
# mkdir /home/hoge/batch
ファイル作成。例えばhoge1とhoge2というテーブルを二個作成する場合は以下。
# vi /home/hoge/batch/create.sql
--
-- hoge1
--
CREATE TABLE hoge1
(
  id     int4 NOT NULL DEFAULT 0
 ,name   varchar(255)
 ,PRIMARY KEY (id)
);

--
-- hoge2
--
CREATE TABLE hoge2
(
  id     int4 NOT NULL DEFAULT 0
 ,email   varchar(255)
 ,PRIMARY KEY (id)
);
以下コマンドでテーブル作成される。
# su - postgres
$ psql DB名 < /home/hoge/batch/create.sql 

linuxにポスグレをインストール

ソースファイルを以下からダウンロード。
ftp://ftp.jp.postgresql.org/source/v8.0.2/postgresql-8.0.2.tar.gz

ポスグレ用ユーザを追加
# adduser postgres

ダウンロードしてきたファイルをwinscpなどで/usr/local/srcに移動して、インストール開始。

解凍
# cd /usr/local/src
# tar xfvz postgresql-8.0.2.tar.gz
# rm postgresql-8.0.2.tar.gz  ←要らないので削除
インストール
# cd /usr/local/src/postgresql-8.0.2
# ./configure
# gmake
# gmake install
データ格納ディレクトリ作成
# mkdir /usr/local/pgsql/data
ポスグレユーザに権限を与える
# chown postgres /usr/local/pgsql/data
DBの初期化
# su - postgres
$ /usr/local/pgsql/bin/initdb --no-locale
  -E EUC_JP -D /usr/local/pgsql/data
(1行で書く)
起動
# /usr/local/pgsql/bin/postmaster \
  -D /usr/local/pgsql/data >logfile 2>&1 &
自動起動設定は /etc/rc.d/rc.local の最初に、以下の記述を追加。(1行で書く)
su - postgres -c "/usr/local/pgsql/bin/pg_ctl start
-D /usr/local/pgsql/data
-l /usr/local/pgsql/data/logfile
-o \"-i -p 5432\""

インポートエクスポート

CSVデータをテーブルにインポートするには
COPY テーブル名 FROM 'CSVファイルの絶対パス' CSV;

例:hogeテーブルにhoge.csvをインポート
COPY hoge FROM '/home/postgres/data/hoge.csv' CSV;

テーブルをCSVデータとしてエクスポートするには
COPY テーブル名 TO 'CSVファイルの絶対パス' CSV;
COPY テーブル名(カラム名1, カラム名2, ...) TO 'CSVファイルの絶対パス' CSV;

例1:hogeテーブルをhoge.csvにエクスポート(全カラム)
COPY hoge TO '/home/postgres/data/hoge.csv' CSV;

例2:hogeテーブルをhoge.csvにエクスポート(idとnameカラムのみ)
COPY hoge(id,name) TO '/home/postgres/data/hoge.csv' CSV;

注意事項
  • インポート先テーブルとインポート対象CSVデータのカラム内容は同じでなければならない。
  • CSVデータに改行のみの行が存在する場合インポートに失敗する。改行のみの行は削除しておく必要あり。
  • インポート時に何度もエラーを出した場合は最後にバキューム処理を忘れずに。(メモリを喰うため)
  • CSVファイルがSJISの場合、インポート時にエラーが出るので以下のようにクライアントエンコーディングをセットしてからインポートを行うこと。
SET client_encoding TO 'SJIS';
COPY テーブル名 FROM 'CSVファイルの絶対パス' CSV;

関数

時間型を文字列型に変換
select to_char(now(), 'yyyymmdd');

結果
20050829

2つのテーブルを比較して重複しているもの以外を抽出

テーブルA
+----+------+
| id | name |
+----+------+
| 1  |  aa  |
| 2  |  bb  |
| 3  |  cc  |
| 4  |  dd  |
| 5  |  ee  |
| 6  |  ff  |
+----+------+
テーブルB
+----+------+
| id | name |
+----+------+
| 1  |  aa  |
| 2  |  bb  |
+----+------+
テーブルAにおいて、テーブルBに含まれるname以外の結果(ここではcc、dd、ee、ff)を取得したい場合は以下。
SELECT name
FROM A
WHERE name NOT IN
 (
   SELECT name
   FROM B
 );
目安箱バナー