holly's wiki

違いを書いておく。使用するシステムの特性に応じたアプリ/DB設定をしないといけない。ってこと

OLTP	更新メイン/indexを頻繁に使う検索
OLAP	seq scanな大量件数取り出し/join&集約が多い

一応用語はおぼえておかないといけない

テーブル全体の作成・変更・削除などを行う際に使用する。とりあえず以下。create/drop系とおぼえていればよい

• CREATE DATABASE
• DROP DATABASE
• CREATE TABLE
• DROP TABLE
• ALTER TABLE
• CREATE VIEW
• DROP VIEW
• CREATE TRIGGER
• DROP TRIGGER

レコード単位の操作。プログラムからは一番よく使うはず。CRUDのこと

• SELECT
• 内部結合(INNER JOIN)
• 外部結合(LEFT JOIN,RIGHT JOIN)
• INSERT
• UPDATE
• DELETE
• TRUNCATE

権限操作、データ更新の確定。とか

• GRANT
• REVOKE
• COMMIT
• ROLLBACK

type	size	その他
smallint	2byte
int	4byte
bigint	8byte
decimal	可変長	1000桁
numeric	可変長	1000桁
real	4byte
double precision	8byte

byteではなく文字数で制限がかかる

type	size	その他
char(n)		空白で埋められた固定長文字列
varchar(n)		上限指定された可変長文字列
text		可変長

varchar, textは実際のところは1Gが上限のようだ

type	size	その他
timestamp	8byte	timezoneはあるなしに関わらず。YYYY-MM-DD HH:MI:ss.000000[+09]
date	4byte	時刻なし
time	8byte	日時なし
interval	12byte	interval '1days' みたいな書き方

type	size	その他
boolean	1byte	TRUE or FALSE or NULL

以下で表現が可能

true	false
't'	'f'
'true'	'false'
'y'	'n'
'yes'	'no'
'on'	'off'
'1'	'0'
TRUE	FALSE

大文字のTRUE/FALSE以外はシングルクォートで囲むこと

type	size	その他
serial	4byte
bigserial	8byte

試験なんかでもよくでる言葉なので覚えておく

Atomicity	原始性。トランザクションは実行完了となるか、まったく実行されないか
Consistency	整合性。トランザクションの開始/終了時にデータベースの整合性を保った状態となっている
Isolation	分離性。トランザクションは別のトランザクションに影響を受けない
Durability	持続性。トランザクションにより変更されたデータは確実に保持される

これらはほとんど共通

  -U, --username=USERNAME     connection user(default $USER)
  -d, --dbname=DBNAME         connection database(default $USER)
  -h, --host=HOSTNAME         connection server or socket(default local socket)
  -p, --port=PORT             database server port

CentOS標準のposgresは8.xなので、最新に近いrpmをインストールできるようにする。pgdg repositoryの追加方法はここに参照。あとは

chkconfig postgresql-9.1 on

su - postgres
echo "PATH=\$PATH:/usr/pgsql-9.1/bin" >> ~/.bash_profile

とでもしておけばよい

本格的にやるのならこっちがお勧め

yum install gcc readline-devel zlib-devel openssl-devel libxslt libxslt-devel libxml2 libxml2-devel

libxsltで必ずエラーになるのでここをみて対処すること

http://www.postgresql.org/download/からソースを取得後

cd /usr/local/src
tar xvfz postgresql-9.1.1.tar.gz
cd postgresql-9.1.1
./configure --prefix=/usr/local/pgsql-$(basename $PWD | sed -e 's/postgresql-//') --with-openssl --with-libxml --with-libxslt 
make
make check
make install
make install-docs
# 複数バージョンをインストールできるようにしておく
ln -s /usr/local/pgsql-9.1.1  /usr/local/pgsql

とする。

echo "MANPATH=\$MANPATH:/usr/local/pgsql/share/man" >> /etc/profile.d/postgresql.sh
echo "PATH=\$PATH:/usr/local/pgsql/bin" >> /etc/profile.d/postgresql.sh

cp -p contrib/start-scripts/linux  /etc/rc.d/init.d/postgresql
chmod 755  /etc/rc.d/init.d/postgresql
chkconfig --add postgresql

直接ログインできないようにpasswordは設けない

useradd postgres

initdbで使う

mkdir /usr/local/pgsql/data
chown postgres:postgres /usr/local/pgsql/data
ln -s /usr/local/pgsql/data /home/postgres/data
chown -h postgres.postgres /home/postgres/data

.bash_profileにそれぞれの環境変数を設定する

echo "PATH=\$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile
cat <<EOL >>~postgres/.bash_profile
export PGHOME=/usr/local/pgsql
export PGDATA=\$PGHOME/data
export PGLIB=\$PGHOME/lib
EOL

database clusterを作成する。postgresアカウントで作成すること。昔はno-localeをつけないと検索などで不具合などがもろもろと言われてたが最近はどうなんだろう？

initdb -D $PGDATA --encoding=UTF-8 --no-locale

実行すると

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default text search configuration will be set to "english".

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    postgres -D /usr/local/pgsql/data
or
    pg_ctl -D /usr/local/pgsql/data -l logfile start

と表示される。これがでればinitdbは成功。superuserのパスワードを設定してしまいたい場合は

# --pwprompt でもよい
initdb -E UTF-8 --no-locale -W

とする

rootアカウントでやるなら

/etc/init.d/postgresql start

postgresアカウントでやるなら

postgresアカウントでやるならinitdbのメッセージにもでているが、以下を実行する

# -l はなくてもいいよな。と思う
pg_ctl -D $PGDATA -l $PGDATA/serverlog start

設定内容をオプションで一時的に変更したい場合は以下のようにNAMEにpostgresql.confの設定項目、VALUEには再設定しなおしたい値を渡す。ちなみに-oはpg_ctlからpostgresコマンドに対して渡すオプションを指定する

pg_ctl -o "--NAME=VALUE" start

たとえば

pg_ctl  -o "--log_autovacuum_min_duration=0" start

とか

# -m fastを指定しないと接続中ユーザがいると切断できない
pg_ctl stop -m fast

とする

psql

  -U                 connection user(default $USER)
  -d                 connection database(default $USER)
  -h                 connection server or socket(default local socket)
  -E                 バックエンドに送信したSQLを表示。メタキャラクタ系コマンド実行時にシステムテーブルに
                     どのように問い合わせしているかがわかる
  --set=NAME=VALUE   internal variable set. セットした値は\echo :foo などで参照可能
  -c <query>         sql実行
  -f <file>          fileに書かれたsqlを実行
  -t, --tuples-only  tuple only
  -x, --expanded     \xと同じ
  -o <file>          ファイルに出力にする
  -l                 DB一覧表示

DB superuserのパスワード設定を行う（当然ながらサーバのsuperuserではないことに注意）

psql -U postgres -c "ALTER USER postgres PASSWORD '********'"

アクセス制限など。$PGDATA配下にある。デフォルトでは

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

となっているが、localでもpassword制限はしといたほうがいいので以下のようにした。追加で1.0.0.1/24からも接続はできるようにした。ipv6はとりあえず不要なので削除

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             1.0.0.1/24              md5

たまにある。localを一時的にtrustにすればいい

local   all             all                                     trust

再起動

pg_ctl restart

local socket(unix domain socket)経由ではパスワードは聞かれないので、ALTER USERなどで変更してからまた設定を戻せばよい

$HOMEに.pgpassをおいておけばパスワード無しで接続することが可能

$hostname:$port:$database:$user:$password

という書式で設置して、パーミッションは自分だけがread/writeできるようにすればよい。

su - postgres
echo "localhost:*:*:postgres:$password" > ~/.pgpass
chmod 600 ~/.pgpass

とでもしておけばpostgresはとりあえずどのDBにでもパスワードさえあわしていればログイン可能

パラメータなど詳細な設定ファイル。とりあえずこうする（ログ関連はsyslogにした）

  ### memory関連
  # 共有bufferのサイズ調整。物理メモリの10 - 20%（OLTP）
  shared_buffer = 102M

  # selectのsort/join の時に使用される。参照系が多いのであれば効果あり（OLAP）
  # max_connections * work_memだけメモリが消費されることが発生するので注意も必要
  work_mem = 4MB

  # vacuum, create index, alter tableなどの処理速度向上。128 - 256M
  maintenance_work_mem = 128M

  # kernel/postgresの共有バッファなど, 使用するバッファ領域の大きさの推定値
  # 物理メモリの 25 - 50%ほど指定してよい
  effective_cache_size = 128MB

  # 常時書き出すダーティページ数の調節
  # OLAP：- 40  OLTP 100 - 
  bgwriter_lru_maxpages = 60
  # OLAP：- 5  OLTP 5 - 10
  bgwriter_lru_multiplier = 5.0

  ### WAL関連
  # transaction log buffer。それぞれのtransaction commit or この値よりbufferが大きくなったときにディスクに書き出しする
  # 32KB - 64KBとするか-1にした場合はshared_buffer baseの値となる
  # OLTP系では数値が高いほうが効果がある。
  wal_buffers = -1

  # transactionがある場合、通常はcommitで同期書き込みが行われるが、指定したmsec秒待つようになる
  # 書き込みを待っている間に他のtransactionがあり、そのトランザクションがcommitすれば、同期書き込みを
  # まとめて行う。書き込み効率があがるためOLTP系では有効。
  # ただし有効なtransactionがcommit_siblingsより少ない場合は即時書き込みとなる 
  # ※CPUが1個の場合はそのままでOK。それ以上の場合は10 - 15で設定
  commit_delay = 15 
  #commit_siblings = 5   

  # 自動的WALチェックポイント間の最大間隔をログファイルセグメント（それぞれのセグメントは通常16メガバイト）で指定
  # チェックポイントの実施間隔調整(WAL数) OLAP:16   OLTP:32 - 64
  checkpoint_segments = 32

  # チェックポイント処理の速度調整 OLAP：default  OLTP:0.6 - 0.9
  checkpoint_completion_target = 0.6

  ### query tuning関連
  # index経由で1ページアクセスするときのコスト。2.0 - 3.0あたりが妥当。
  # index scanがseq scanの$random_page_cost倍のコストがかかることを意味する
  random_page_cost = 3.0

  # deadlock検出チェック処理。比較的重い処理のため、1000msec * max_connectionsくらいでよい # 1000 * 100 = 100000msec = 100sec
  deadlock_timeout = 100s



  ### log関連
  # default. syslogとした場合はsyslogに書き込む
  log_destination = 'syslog'

  # 出力結果をファイルに書き込む（syslogの場合は不要）
  logging_collector = off

  # log出力場所 $PGDATA/$log_directory/（syslogの場合は不要）
  log_directory = 'pg_log' 

  # strftimeで使用できるフォーマット（syslogの場合は不要）
  log_filename = 'postgresql-%Y%m%d.log'

  # 遅いqueryを記録（millsec）
  log_min_duration_statement = 3000

  # 接続/切断を記録
  log_connections = on
  log_disconnections = on

  # <2011-04-19 01:54:32 JST 2101 postgres:template0> のように残る（syslogの場合は不要）
  log_line_prefix = '<%t pid:%p %u:%d>'

  # deadlock_timeoutよりセッションが長く待機した場合にログに記録する）
  log_lock_wait = on

　# 通常のSQL文を出力する場合はall
  log_statement = "none"

  # syslogの場合は必須。facilityは必要に応じてsyslogと合わせて設定すること
  #syslog_facility = 'LOCAL0'
  #syslog_ident = 'postgres'


　### autovacuum
  # autovacuum
  autovacuum = on

  # 指定ミリ秒数かかったvacuumをログに記録
  log_autovacuum_min_duration = 20000

postgresql.confに

ssl = on

と設定し、$PGDATAに

perm	owner:group	file
600	postgres:postgres	server.key
600	postgres:postgres	server.crt

として設置する。これ以外はだめ。symlinkもだめだった。pg_hba.confには

hostssl all             all             10.0.0.1/24             md5

と設定し、再起動すればよい。ssl接続するかはクライアントまかせとなる

rsyslogで行う。とりあえずlocal0をpostgresql用として設定する

# 先頭あたりに追記
$FileCreateMode 0600

設定ファイルの最後に

$FileCreateMode 0644
local0.*                                                /var/log/postgres.log

を追加する

echo "SYSLOG_UMASK=022" >> /etc/sysconfig/rsyslog

/var/log/postgres.log {
    daily
    rotate 180
    missingok
    ifempty
    compress
    dateext
    create 0644 postgres postgres
    sharedscripts
    postrotate
        /bin/kill -HUP `cat /var/run/rsyslogd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

最近はデフォルトでplpgsqlがセットされているので、あまりすることがないかもだが。createlang/droplangを使う

createlang plpgsql template1;

言語一覧を見る場合は

createlang -l
Procedural Languages
  Name   | Trusted?
---------+----------
 plpgsql | yes

とする

普段はpostgresアカウントは使わないので、普段用に一般アカウントを作成する

postgresで接続して、アカウント作成でもいいが、普通はこっちを使うと思う

-e   実行したSQLを表示
-d   DBを作成する権限を与える
-D   -dの反対
-E   パスワードは暗号化する（password_encryption=onなら指定しなくても問題なし。普通はなってると思うが）
-l   ログイン許可（default）
-L   -lの反対
-P   パスワード設定
-r   role作成
-R   -rの反対
-s   superuser
-S   -s の反対

というわけでよくありがちなアカウント作成は以下のオプションになるはず

# サーバ上でpostgresになってるといろいろと楽。それ以外は-Uなどで明示的に指定すること。ちなみにkurtアカウントを作成する場合
createuser -e -dRSEP kurt
Enter it again:
CREATE ROLE kurt ENCRYPTED PASSWORD 'md55a2e6b5ea18a5c9d0e3db68a3be4e3e5' NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;

削除

dropuser -e kurt
DROP ROLE kurt;

既に作成済みのアカウントに対しての権限変更などができる

alter role <ROLE> [
[WITH]
SUPERUSER | NOSUPERUSER
CREATEDB | NOCREATEDB
CREATEROLE | NOCREATEROLE
REPLICATION | NOREPLICATION
LOGIN | NOLOGIN
PASSWORD '<PASSWORD>'
VALID UNTIL 'YYYY-MM-DD'
]

他にもあるようだが、これだけ覚えておけばなんとかなる

createuserで作成したアカウントで作成する。ちなみにアカウントもDB名もDBアカウント名にしとくと便利

# いないのであればDBアカウントと同じ名前のunixアカウントを作成しておくこと（これは好みの話やし、普通は順序が逆だと思うがまあいい）
su - kurt
createdb -e -E UTF-8 <DBNAME>
# passwordを聞かれるはずなのでcreateuserで設定したパスワードを入力
CREATE DATABASE <DBNAME> ENCODING 'UTF-8';

デフォルトではtemplate1からDBがコピーされる形になるが、-T or --template=DBNAMEで指定可能なのであらかじめテーブルなどを作成済みにした状態のDBをコピーすることも可能

initdbをした時の設定で基本的に引き継がれるが、どうしても変更したい場合は-E(--encoding)と-l(--locale)を指定して、作成元データベースはtemplate0を明示的に指定すること

DB削除

dropdb -e <DBNAME>
DROP DATABASE <DBNAME>;

tab区切りでファイルに書き出し。SQLのCOPYと違いroot権限は不要。ただしclient<->serverをネットワーク越しにデータが流れるので、大容量の場合はSQLのCOPYのほうがよい

\copy $table to /path/to/file 

tab区切りのリストを読み込み（高速）。with csvをつけると,区切りで改行コードがCRLFでも対応可能（文字コードまでは対応不可）

\copy $table from /path/to/file [with csv]

指定したファイルに記載されているSQLを実行

\i /path/to/file

結果を指定したファイルに全て書き出す

\o /path/to/file

テーブル一覧/情報表示。よく使う

\d <TABLE>
  Column  |            Type             |                       Modifiers
----------+-----------------------------+-------------------------------------------------------
 id       | integer                     | not null default nextval('accounts_id_seq'::regclass)
 name     | character varying(64)       | not null
 email    | character varying(64)       | not null
 password | character varying(42)       | not null
 salt     | character varying(8)        | not null
 valid    | boolean                     | not null default true
 created  | timestamp without time zone | not null default now()
 updated  | timestamp without time zone | not null default now()
Indexes:
    "accounts_email_key" UNIQUE CONSTRAINT, btree (email)
    "accounts_name_key" UNIQUE CONSTRAINT, btree (name)
Triggers:
    t_auto_updated BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE PROCEDURE auto_updated()

TABLESPACE. pg_defaultは気にしなければここが使われる。pg_globalはシステムが使用するものなので指定不可

           List of tablespaces
      Name    |  Owner   |   Location
  ------------+----------+--------------
   disk1      | postgres | /data/pgdata
   pg_default | postgres |
   pg_global  | postgres |
  (3 rows)

定義関数一覧

 Schema |     Name     | Result data type | Argument data types |  Type
--------+--------------+------------------+---------------------+---------
 public | auto_updated | trigger          |                     | trigger

list sequence

              List of relations
 Schema |      Name       |   Type   | Owner
--------+-----------------+----------+-------
 public | accounts_id_seq | sequence | kurt
(1 row)

list foreign table

list table

       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | holly
(1 row)

テーブル権限

\dp mytable
                                Access privileges
   Schema |  Name   | Type  |   Access privileges   | Column access privileges 
  --------+---------+-------+-----------------------+--------------------------
   public | mytable | table | miriam=arwdDxt/miriam | col1:
                            : =r/miriam             :   miriam_rw=rw/miriam
                            : admin=arw/miriam        

見方はこんな感じ

  rolename=xxxx -- ロールに与えられた権限
          =xxxx -- PUBLICに与えられた権限
  
              r -- SELECT（読み取り（read））
              w -- UPDATE（書き込み（write））
              a -- INSERT（追加（append））
              d -- DELETE
              D -- TRUNCATE
              x -- REFERENCES
              t -- TRIGGER
              X -- EXECUTE
              U -- USAGE
              C -- CREATE
              c -- CONNECT
              T -- TEMPORARY
        arwdDxt -- すべての権限 (テーブル用。他のオブジェクトでは異なります。)
              * -- 直前の権限に関するグラントオプション
  
          /yyyy -- この権限を付与したロール

システムテーブル一覧表示

ユーザ一覧. \dg（role一覧）も同じっぽい

                         List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 kurt      | Create DB, Replication                         | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

view一覧

            List of relations
 Schema |       Name       | Type | Owner
--------+------------------+------+-------
 public | v_posts_accounts | view | kurt
(1 row)

DB一覧

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 kurt      | kurt     | UTF8     | C       | C     |
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

\dpと同じ

-[ RECORD 1 ]------------------------------
id       | 2
name     | kingbuzzo
email    | kingbuzzo@melvins.com
password | efe8663fb075ad68beb3ae5ec236c390
salt     | Y7WvSPxZ
valid    | t
created  | 2011-04-26 01:34:15.077498
updated  | 2011-04-26 01:41:14.188045

と表示される。実行するたびにon/offが切り替わる

DB切り替え

\c $db

パスワード変更。superuser権限以外利用不可

\password [$user]

current directory変更

\cd $dir

実行時間表示

外部コマンド実行

内部変数の表示/設定

内部変数の削除

問い合わせ結果のテーブル出力に影響するオプション。いろいろあるようだがあまり使わない

検証用にとりあえず以下のテーブルを作成する

create.sql

  CREATE TABLE accounts (
   id        SERIAL PRIMARY KEY,
   name      VARCHAR(64) NOT NULL,
   email     VARCHAR(64) NOT NULL,
   password  VARCHAR(32) NOT NULL,
   salt      VARCHAR(8)  NOT NULL,
   valid     BOOL DEFAULT true NOT NULL,
   created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   UNIQUE(name),
   UNIQUE(email)
  );

  CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    account_id INT NOT NULL,
    title      VARCHAR(128) NOT NULL,
    content    TEXT NOT NULL,
    created    TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE ON UPDATE CASCADE
  );

  CREATE TABLE operation_logs (
    id         SERIAL PRIMARY KEY,
    account_id INT,
    content    TEXT NOT NULL,
    created    TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE SET NULL ON UPDATE CASCADE
  );

  CREATE TABLE login_logs (
    id         SERIAL PRIMARY KEY,
    account_id INT,
    content    TEXT NOT NULL,
    created    TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE SET NULL ON UPDATE CASCADE
  );

  // データ登録
  INSERT INTO accounts(name, email, password, salt) VALUES('kingbuzzo', 'kingbuzzo@melvins.com', md5('melvins' || 'Y7WvSPxZ'), 'Y7WvSPxZ');

作成する

psql -f create.sql

覚えれらない

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE parent_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

auto incrementなfield primary keyとしても使うことが多いはずなので

id SERIAL PRIMARY KEY

としておけばよい。実際は

CREATE SEQUENSE id_seq;
CREATE TABLE t1(id INTEGER DEFAULT nextval('id_seq'));

をしているが、この場合はテーブル削除をした時に自動でsequenceは削除されない。こんな方法はいまどきしないと思うが。

覚える必要もあまりないと思うが一応

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]

覚えておくことは

INCREMENT	増加値。普通は1だがマイナスを指定も可能
START	これも開始値を変更したい場合
[NO]CYCLE	最大値に達した場合の振る舞い。デフォルトはNOCYCLEで最大値を払い出そうとするとエラーになる。CYCLEを指定するとエラーにならず開始値に戻る

直接使うことはあまりないがそれでもたまに使うことがある

currval(seqname)	指定したseqenceの現在の値。ちなみにこれをlast_insert_id的に使うべきではないというのは有名な話
nextval(seqname)	値をincrementし、その値を返す
setval(seqname, value)	値を設定する
lastval()	currvalと同じだが、sequence名などがいらない
last_value	currvalはtransactionが始まらないと取得できないが、これを使えばselect last_value from foo_id_seq; みたいな使い方で取得できる

NULL/文字/数値/関数など指定可

firld定義時に指定可能

NOT NULL	NULLは不可
UNIQUE	indexが作成される
PRIMARY	主キー。NULLは不可 indexが作成される
CHECK	入力時の制約を定義できることが可能

postgresはCHECKにsub queryで記述することは不可

age INT CHECK(age >= 0 AND age <150) や
sex varchar(32) CHECK(sex IN ('woman', 'man')) 

など

テーブル間の整合性を保つために重要。NoSQLじゃこういうのないような。たぶん

CASCADE	参照される側の関係変数（テーブル）の組（タプル、行） が削除された場合、参照する側の関係変数の対応する すべての組は削除される。 同様に、参照される側の関係変数の組が更新された場合、参照する側の関係変数の外部キーの値は 同じ値に更新される
RESTRICT	参照する側の関係変数の組が残っている場合は、参照される側の組は更新することも削除することもできない。 この場合、データの変更は全く行われない。確かdefault
NO ACTION	参照される側の関係変数において UPDATE あるいは DELETE SQL 文が実行される。 DBMS は SQL 文の実行の終了時に参照整合性が満たされているかどうかを検査する。 RESTRICT との大きな違いは、 トリガもしくはSQL文のセマンティクス自体が外部キーの制約を満たすであろうということである。 このとき、 SQL 文の実行は成功する。 外部キーの制約が満たされない場合は SQL 文の実行は失敗する。あんまつかわんと思うけど
SET NULL	参照される側の関係変数において組が更新もしくは削除された場合、参照する側の関係変数の組の外部キーの値には NULLが設定される。 このオプションは参照する側の関係変数の外部キーにNULLを設定できる場合にのみ、定義可能である。 NULLのセマンティクスにより、参照する側の関係変数においてNULLのある組は、参照される側の関係変数の組を必要としない
SET DEFAULT	SET NULL と似ているが、参照される側の関係変数の組が更新あるいは削除された場合、参照する側の関係変数の 外部キーの値は属性の既定値（デフォルト値）が設定される

table/fieldに対しての変更操作。

field追加

ALTER TABLE <table> ADD [COLUMN] <name> <type> [constraint] [default]

field削除

ALTER TABLE <table> DROP [COLUMN] <name> [RESTRICT | CASCADE]

型変更
expressionはdefault値として適用するものがあれば指定

ALTER TABLE <table> ALTER [COLUMN] <name> type <type> [USING expression]

ちょっとした例でいうと

-- int型のxxx_idをbigintに変更
alter table tbname alter column xxx_id type bigint 

usingを使う例は以下

-- 文字列が入っているフィールドを数値型になんとかして変更。castでもよい
ALTER TABLE foo ALTER COLUMN bar TYPE INTEGER USING to_number(bar, '9999999999');

ここにいい例が載っている。

名前変更

ALTER TABLE <table> RENAME [COLUMN] <name> to <newname>

default set/drop

ALTER TABLE <table> ALTER [COLUMN] <name> SET DEFAULT $default;
ALTER TABLE <table> ALTER [COLUMN] <name> DROP DEFAULT;

notnull set/drop

ALTER TABLE <table> ALTER [COLUMN] <name> SET NOT NULL
ALTER TABLE <table> ALTER [COLUMN] <name> DROP NOT NULL;

名前変更

ALTER TABLE <table> RENAME to <new table>;

PRIMARY KEY

ALTER TABLE <table> PRIMARY KEY [idx_name] (field)

制約追加

ALTER TABLE <table> ADD CONSTRAINT <contraint name> <constraint>;
-- 以下はいろいろな例
-- check制約追加
alter table t1 add constraint age_check check(age >=0 and age <= 150)
-- unique index追加
alter table t1 add constraint unique [idx_name] (<field,...>)

-- 外部キー追加 foreign keyはposts_account_id_fkeyのように「${table}_${field_fkey}」という名前にすればよい
alter table t1 add constraint <fk_name> foreign key(<field>) references <reference table>(<reference field>) [ON DELETE -- [ON UPDATE --]]

制約削除

ALTER TABLE <table> DROP CONSTRAINT <contraint name> [RESTRICT | CASCADE];

-- unique/primary key/foreign keyはindexが作成されるがconstraintで指定する
alter table t1 drop constraint <idx_name>

owner変更

ALTER TABLE <table> OWNER TO <newowner>;

http://lets.postgresql.jp/documents/technical/text... tipsはここ

 -- ACCESS METHODはBtree/Hash/Rtree（Rtreeって？）などがあるがBtreeだけを考えておけばよいので、意識する必要はなし
  CREATE [UNIQUE] INDEX <INDEX NAME> ON <TABLE NAME>
         [ USING ACCESS METHOD ] (field [, fieldx...]);

indexを作成しただけでは有効にならないので、vacuum（analyzeをつけるといいっぽい）を実行すること。

VACUUM ANALYZE <TABLE>

何も考えなければBtreeが適用される

Btree	一般的に用いられる。マルチカラムindex可能
GiST	2次元の四角形、多角形、整数間隔、テキスト用の空間的 インデックス。なんだとか。マルチカラムindex可能
GIN	全文検索で仕様
Hash	値の一致検索のみサポート。使うことないだろう。きっと

 select * from t1 where upper(value) = 'H4NM3YPU';

このようなSQLを発行する場合はvalueにindexをはっていてもindex scanはされないので

CREATE INDEX value_upper_idx ON t1(upper(value));

のようにする

あんまり使ったこと無い

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

こんなSQLでindexを有効にしたい場合は

CREATE INDEX people_names_idx ON people ((first_name || ' ' || last_name));

とする

SELECT * FROM t1 WHERE id = xxx AND value = xxx

この順番で検索されることが重要。indexで定義している順番と違う場合はindexは参照されない

CREATE INDEX value_multi_idx ON t1(id, value);

1 - 10000の間で5000 - 6000あたりのアクセスが多い場合など。つかったことない

CREATE INDEX id_range_idx ON t1(id) WHERE id > 5000 AND id <= 6000 ;

IS NULLで検索すればわりかし普通にいけてるようだ。昔（8.3以前）ではだめだが

indexには検索する対象レコードが含まれていないため、かならずテーブルアクセスする必要がある。
通常はランダムに並んだ行データの中から対象レコードを読み出すため、ディスクI/Oが発生→負荷が高くなる
indexのデータの並び順にテーブルの中の行も並んでいれば、最初に読み出したページの中に次の行も含まれている
ことになり、最小限のindexへのアクセスで対象レコードを読み出せることになり、ディスクI/Oの負荷を減らすことができる。

ということらしい。こういうことをしてくれるようだ
※詳細はhttp://www.postgresql.jp/document/pg732doc/referen... など。バージョンは古いけど読めばわかる

CLUSTER <IDX NAME> ON <TABLE NAME>;

2回目以降は

CLUSTER <TABLE NAME>

でもいいが、UPDATE/DELETEなどの更新処理を行うとCLUSTERコマンドの並び順は崩れるので、定期的に実行すればよい

table space を指定することもできる（table spaceのことはあとで説明）

CREATE INDEX value_idx ON t1(id) TABLESPACE <TABLESPACE NAME> ;

既存のindexのtable spaceを変更にするには

ALTER INDEX <IDX NAME> SET TABLESPACE <TABLESPACE NAME>;

とする

といっても主にselect。よくありそうなの

以下のテーブルがあるということにする

create table section (
  id int primary key,
  name varchar(64) not null,
  created timestamp default current_timestamp,
  updated timestamp default current_timestamp
);
insert into section(id, name) values(1, 'develop');
insert into section(id, name) values(2, 'operation');
insert into section(id, name) values(3, 'customer');
insert into section(id, name) values(99, 'special') ;

create table member (
  id serial primary key,
  name varchar(128) not null,
  age int not null check(age >= 0 AND age < 150),
  section_id int,
  created timestamp default current_timestamp,
  updated timestamp default current_timestamp
);
insert into member(name, age, section_id) values('kurt', 27, 1);
insert into member(name, age, section_id) values('kingbuzzo', 43, 2);
insert into member(name, age, section_id) values('akiko', 32, 1);
insert into member(name, age, section_id) values('muse', 90, NULL);

表にするとこういう感じ

id	name
1	develop
2	operation
3	customer
99	special

id	name	age	section_id
1	kurt	27	1
2	kingbuzzo	43	2
3	kingbuzzo	32	1
4	muse	90	NULL

よく使うのでとりあえずはじめに書いておく

普通のjoinのこと。結合対象テーブルの結合キーに合致する行だけ結合する

select m.id, s.name as section_name, m.name as member_name, m.age from section as s inner join member as m on s.id = m.section_id;
 id | section_name | member_name | age
----+--------------+-------------+-----
  1 | develop      | kurt        |  27
  2 | operation    | kingbuzzo   |  43
  3 | develop      | akiko       |  32
(3 rows)

結合テーブルの全ての行を結合する（全ての組み合わせ行が表示）。というわけでm * nな結果となる

select m.id, s.name as section_name, m.name as member_name, m.age from section as s cross join member as m ;
 id | section_name | member_name | age
----+--------------+-------------+-----
  1 | develop      | kurt        |  27
  2 | develop      | kingbuzzo   |  43
  3 | develop      | akiko       |  32
  4 | develop      | muse        |  90
  1 | operation    | kurt        |  27
  2 | operation    | kingbuzzo   |  43
  3 | operation    | akiko       |  32
  4 | operation    | muse        |  90
  1 | customer     | kurt        |  27
  2 | customer     | kingbuzzo   |  43
  3 | customer     | akiko       |  32
  4 | customer     | muse        |  90
  1 | special      | kurt        |  27
  2 | special      | kingbuzzo   |  43
  3 | special      | akiko       |  32
  4 | special      | muse        |  90
(16 rows)

joinの左の結合キーが一致なかった行をnullとして左側の行を出力する。普段直感的に使っているとこういうの書くとよけいにわかりにくくなる

select m.id as member_id, m.name as member_name, s.name as section_name,  m.age from member as m left join section as s on s.id = m.section_id;
 member_id | member_name | section_name | age
-----------+-------------+--------------+-----
         1 | kurt        | develop      |  27
         2 | kingbuzzo   | operation    |  43
         3 | akiko       | develop      |  32
         4 | muse        |              |  90
(4 rows)

joinの右の結合キーが一致なかった行をnullとして右側の行を出力する。普段使わない

select m.id as member_id, m.name as member_name, s.name as section_name,  m.age from member as m right join section as s on s.id = m.section_id;
s on s.id = m.section_id;
 member_id | member_name | section_name | age
-----------+-------------+--------------+-----
         1 | kurt        | develop      |  27
         2 | kingbuzzo   | operation    |  43
         3 | akiko       | develop      |  32
           |             | special      |
           |             | customer     |
(5 rows)

leftとrightと合体した結果

select m.id as member_id, m.name as member_name, s.name as section_name,  m.age from member as m full join section as s on s.id = m.section_id;
 member_id | member_name | section_name | age
-----------+-------------+--------------+-----
         1 | kurt        | develop      |  27
         2 | kingbuzzo   | operation    |  43
         3 | akiko       | develop      |  32
         4 | muse        |              |  90
           |             | special      |
           |             | customer     |
(6 rows)

重複を取り除く

select distinct section_id from member;
 section_id
------------

          1
          2
(3 rows)

distinctしつつ、他の列も取り出したい場合はdistinct on

select distinct on(section_id) id, section_id, name from member;
 id | section_id |   name
----+------------+-----------
  1 |          1 | kurt
  2 |          2 | kingbuzzo
  4 |            | muse
(3 rows)

ここで重要なのはdistinct onをすると指定したフィールドに対してsortがかかる。ということでnullが最後で数値で昇順ソートした結果となる。order byを指定している場合はそっちが優先される

よく使う。重複を取り除くのにdistinctの代わりにもつかえる。集約関数と一緒に使うことが多い

-- 各部署の平均年齢
select section_id, round(avg(age)) from member group by section_id order by section_id;
 section_id | round
------------+-------
          1 |    30
          2 |    43
            |    90
(3 rows)

集約した結果で結果を絞るときはhavingを使う

 select section_id, round(avg(age)) as age from member group by section_id  having avg(age) < 40;
 section_id | age
------------+-----
          1 |  30
(1 row)

実行したサブクエリが結果を返せばtrue。みたいな使いかた。あんまりいい使い方が思い浮かばなかった

select m.id, m.name, m.section_id  from member as m where exists (select s.id from section as s where s.id = m.section_id);
 id |   name    | section_id
----+-----------+------------
  1 | kurt      |          1
  2 | kingbuzzo |          2
(2 rows)
||
この場合、member全行に対してサブクエリで指定されているwhere条件文に一致するか検索する。まあこんな例ならinner joinでやれとかなりそうだが。
=|BOX|
select m.id, m.name, m.section_id  from member as m where not exists (select s.id from section as s where s.id = m.section_id);
 id | name | section_id
----+------+------------
  3 | muse |
(1 row)

not existsとすれば、お互いに紐付かないレコードを検索することができる。余談だが、inでサブクエリを使う場合はexistsで代替できないか検討するといい。そのほうがSQL的には早いと言う話だが、今はどうなんだろう

同じ定義のテーブルをmergeできる。みたいな感じ。仮に

create temp table section_temp (
    id int primary key,
    name varchar(64) not null,
    created timestamp default current_timestamp,
    updated timestamp default current_timestamp    
);
insert into section_temp(id, name) values (1, 'develop', '2011-11-24 20:21:58.12555', '2011-11-24 20:21:58.12555');
insert into section_temp(id, name) values (100, 'new section');

として状態でunionを実行する

select * from section union select * from section_temp order by id;
 id  |    name     |          created           |          updated
-----+-------------+----------------------------+----------------------------
   1 | develop     | 2011-11-24 20:21:58.12555  | 2011-11-24 20:21:58.12555
   2 | operation   | 2011-11-24 20:21:58.129988 | 2011-11-24 20:21:58.129988
   3 | customer    | 2011-11-24 20:21:58.132815 | 2011-11-24 20:21:58.132815
  99 | special     | 2011-11-24 20:21:58.943998 | 2011-11-24 20:21:58.943998
 100 | new section | 2011-11-25 20:41:25.095793 | 2011-11-25 20:41:25.095793
(5 rows)

unionは自動的に重複は取り除いてしまうので、そうさせたくない場合はallを付ける

select * from section union all select * from section_temp order by id;

このほうがunionを使う場合はパフォーマンスがよい

あんまり使わないから、いい例題が思いつかない

select * from member where section_id = any(select id from section where id > 1);

まあサブクエリが使えて比較演算子が使えるということ

間の値を取りたいとき

select id, name from section where id between 1 and 3;
 id |   name
----+-----------
  1 | develop
  2 | operation
  3 | customer
(3 rows)

where文は以下と同じ

id >= 1 and id <= 3

selectの結果からテーブルを作る。selectとcreate tableの構文さえ知っていればなんとかなる

-- memberテーブルの結果からmember_tempという一時テーブルを作成する
select * into temp member_temp from member;
SELECT 3

into 〜 がテーブル作成でfrom以降はselect文と同じ

といっても大体はわかるので、試験対策として、使いそうなのを書いとく

xより小さくない最大の整数。切り上げではないことに注意

select ceil(10.1);
 ceil
------
   11
(1 row)

マイナスの数値の場合はこうなる

select ceil(-10.1);
 ceil
------
   -10
(1 row)

xより大きくない最大の整数。切り捨てではないことに注意

select flor(10.1);
 floor
------
   10
(1 row)

マイナスの場合

select floor(-10.1);
 floor
-------
   -11
(1 row)

小数点は四捨五入

select round(10.5);
 round
-------
    11
(1 row)

マイナスの場合

select round(-10.1);
 round
-------
   -10
(1 row)

小数点切捨て

select trunc(10.9);
 trunc
-------
    10
(1 row)

マイナスの場合

select trunc(-10.9);
 trunc
-------
   -10
(1 row)

切り捨てる小数点の位を指定することも可能

select trunc(1.12345, 12);
 trunc
-------
  1.12
(1 row)

整数商

select div(10, 3);
 div
-----
   3

ちなみに演算子の/と違うのは型がintegerになること

-- 演算子で少数の割り算をするとこうなる
select 10.0 / 3.0;
      ?column?
--------------------
 3.3333333333333333
(1 row)

剰余

select mod(3, 1);
 mod
-----
   0
(1 row)

逆は割り切れない扱いなのでそのまま

select mod(1, 3);
 mod
-----
   1
(1 row)

0以上1未満の値を返す。よく出るのはこういうやつ

-- 0 - 99の値をランダムに出力
select (random() * 100)::integer % 100;

とか

select ceil(random() * 5.0);

で可能性のある出力があるやつは？みたいな。この場合は0〜5のどれか

mysqlで使われるあれ。9.xからだったような気がする

 select concat('abc', 'def', 'hij');
  concat
-----------
 abcdefhij
(1 row)

文字数の取得。マルチバイトでも1文字として扱う

-- 5
select length('あいうえお');

byteで返す

-- UTF-8であれば15
select octet_length('あいうえお');

$fromは何文字目から、$forは指定があればその分だけ返す

substring(string, from $from[, for $for]);

ABCDEFGで2文字目から3文字という場合は

-- BCD
select substring('ABCDEFG', 2, 3);

検索でよく使う。ちなみに前方一致はSQL発行時に対象フィールドにindexを貼っていれば、index scanされる

-- 前方一致
select * from <TABLE> where <FIELD> like 'aaa%';
-- 後方一致
select * from <TABLE> where <FIELD> like '%aaa';
-- 部分一致
select * from <TABLE> where <FIELD> like '%aaa%';

_はあまり使わないと思うが。

%	任意の文字列
_	任意の一文字

likeに加えて、正規表現っぽいのが使える。だいたいわかるけど、以下のサンプルは要注意

select * from <TABLE> where <FIELD> similar to '[a-z]*';

この場合、FIELDがaiueo12345の場合は一致しない。この場合の意味は小文字アルファベットだけで構成されたフィールドの値ということになるようだ。^（先頭）、$（末尾）に該当するようなのは無いからか？

本当の正規表現。使うのならsimilar toより使いやすいと思う

~	正規表現に一致すればtrueを返す。大文字小文字は区別される
~*	正規表現に一致すればtrueを返す。大文字小文字は区別されない
!~	正規表現に一致しなければtrueを返す。大文字小文字は区別されない
!~*	正規表現に一致しなければtrueを返す。大文字小文字は区別されない

例えば電話番号の簡単な正規表現だとこうなる

select '0120999999' ~ '^[0-9]{10,11}$';

current_timestamp（これは変数）と同じ

select now();
   now
-------------------------------
 2011-11-17 21:13:02.049124+09
(1 row)

timestamp型のデータから指定したフィールドを取り出す
年だけ

select extract(year from '2011-09-17 12:00:00'::timestamp);

時間だけ

select extract(hour from '2011-09-17 12:00:00'::timestamp);

extractと同じだが引数の指定のしかたが違う。日時フィールドはシングルクォートで囲うこと

select date_part('year', '2011-09-17 12:00:00'::timestamp);

指定した日時フィールドより小さい単位を切り捨てる。truncと概念的には同じと考えればよい

select date_trunc('minute', '2011-09-17 12:45:42'::timestamp);
    date_trunc
---------------------
 2011-09-17 12:45:00

日付データを文字列（text型？）に直す

 select to_char(current_timestamp, 'YYYY/MM/DD HH24:MI:SS');
       to_char
---------------------
 2011/11/21 18:57:53
(1 row)

時間はHH, HH12, HH24の3種類、分はMIとなる。数値型からも変換できるが、メインで使うのは日付データ型からの変換と思うので省略

ちょっとだけ書いておく

example	result	description
'2011-11-11'::date + '1'::integer	2011-11-12	integerはdate型に対して計算可能で1日の扱いになる
'2011-11-11 15:32:10'::timestamp + '10 days'::interval	'2011-11-21 15:32:10'::timestamp
'2011-11-11 15:32:10'::timestamp - '2011-11-10 14:31:10'::timestamp;	1 day 01:01:00
'2011-11-11'::date + '10:00:30'::time	'2011-11-11 10:00:30'::timestamp	ということになるらしい

権限の付与、略奪

権限を与える。基本はto $account

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] objectname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...]

とあるテーブルに参照のみ許可したいなどの場合

GRANT SELECT ON $table TO holly;

などとする

\z or \dpで参照が可能。詳細はhttp://www.postgresql.org/docs/9.2/static/sql-gran...

 Schema |            Name            |   Type   |  Access privileges  | Column access privileges
--------+----------------------------+----------+---------------------+--------------------------
 public | acos                       | table    | holly=arwdDxt/holly |

privilege	意味
a	append, insertのこと。copy toも可能
r	retrive, selectのこと。 copy fromも可能
w	write, updateのこと。nextval, currvalやselect 〜 for updateもこの権限が必要
d	delete
D	truncate
x	references
t	trigger

arwdDxtと出ているか、何も表示されていない場合は実質全ての操作権限が与えられている。

以下のようにする。schemaはほとんどがpublicになる

grant select on all tables in schema $schemaname to $role;