postgresqlメモ

数値フォーマット

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でファイルを読み込んでクエリ実行

# 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版インストール

データのバックアップ

# su - postgres
$ /usr/local/pgsql/bin/pg_dumpall -o >
  /usr/local/pgsql/data/backup/db_backup.out
(以下1行で書く)

# crontab -e
(以下1行で書く)
0 5 * * * su - postgres -c 
'/usr/local/pgsql/bin/pg_dumpall -o >
/usr/local/pgsql/data/backup/db_backup.out'

簡単にテーブル作成

# mkdir /home/hoge/batch

# 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にポスグレをインストール

# adduser postgres

# 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

# 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 &

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\""

インポートエクスポート

COPY テーブル名 FROM 'CSVファイルの絶対パス' CSV;

例：hogeテーブルにhoge.csvをインポート
COPY hoge FROM '/home/postgres/data/hoge.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つのテーブルを比較して重複しているもの以外を抽出

+----+------+
| id | name |
+----+------+
| 1  |  aa  |
| 2  |  bb  |
| 3  |  cc  |
| 4  |  dd  |
| 5  |  ee  |
| 6  |  ff  |
+----+------+

+----+------+
| id | name |
+----+------+
| 1  |  aa  |
| 2  |  bb  |
+----+------+

SELECT name
FROM A
WHERE name NOT IN
 (
   SELECT name
   FROM B
 );