目的
仕事でpostgresqlとpl/pgsql使うことになったので勉強。
oracleのもそうだったけど、公式文書がどうにも読みにくいと感じて仕方がない…
Javadoc的なものが欲しいけど、無いものねだりしても仕方ないので
チュートリアルから自分が欲しいところだけ抜粋。
DB
・対話環境からではなく、コマンドラインから直接実施
・ユーザー省略時はOSのユーザー名が参照される
作成
createdb mydb
# ユーザー指定の例
createdb -U postgres mydb
削除
dropdb mydb
対話環境
・末尾に;がなければ無視される
接続
psql mydb
psql -U postgress mydb
ヘルプ
psql接続後
\?
一覧系
# テーブル、ビュー、シーケンス一覧
\d
# DB一覧
\l
切断
\q
テーブル関連
テーブル作成
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- 最低気温
temp_hi int, -- 最高気温
prcp real, -- 降水量
date date
);
テーブル削除
DROP TABLE weather;
レコード操作
INSERT
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
※COPYを使って平文テキストファイルから一括ロードできるらしい
SELECT
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
-- DISTINCTとかももちろん使える
SELECT DISTINCT city
FROM weather
ORDER BY city;
結合
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
集約
-- 集計
SELECT max(temp_lo) FROM weather;
-- 副問合せ
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
-- GROUP BY
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
-- フィルタ
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
UPDATE
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
DELETE
DELETE FROM weather WHERE city = 'Hayward';
-- 確認？ 慈悲はない
DELETE FROM tablename;
高度な機能
ビュー
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
外部キー
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
-- エラーになる
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
トランザクション
・BEGINを省略するとオートコミット
・SAVEPOINTを使う事でROLLBACK先を細かく制御できる
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
COMMIT;
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- ぼうけんのしょ1にキロク
SAVEPOINT my_savepoint;
-- 名義間違い
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- 何も起きなかった、いいね
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
ウィンドウ関数
・グループ化はしないが、任意の項目で集約できる
select city, sum(temp_lo) OVER (PARTITION BY temp_hi) from weather;
select * from weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 43 | 57 | 0 | 1994-11-29
Kishiwada | 43 | 57 | 0 | 1994-11-29
Izumi | 2345 | 53 | 0 | 1994-11-28
Izumi | 9817 | 53 | 0 | 1994-11-28
Izumisano | 123 | 53 | 0 | 1994-11-28
Sennan | 1321 | 53 | 0 | 1994-11-28
Izumisano | 12313 | 53 | 0 | 1994-11-28
Sennan | 9786 | 53 | 0 | 1994-11-28
select city, sum(temp_lo) OVER (PARTITION BY city) from weather;
city | sum
---------------+-------
Izumi | 12162
Izumi | 12162
Izumisano | 12436
Izumisano | 12436
Kishiwada | 43
San Francisco | 43
Sennan | 11107
Sennan | 11107
(8 行)
select city, sum(temp_lo) OVER (PARTITION BY temp_hi) from weather;
city | sum
---------------+-------
Sennan | 35705
Sennan | 35705
Izumisano | 35705
Izumi | 35705
Izumi | 35705
Izumisano | 35705
Kishiwada | 86
San Francisco | 86
(8 行)
継承
・親TBL、子TBLを一緒くたに扱える
・SELECT,UPDATE,DELETEとかも対応してる
CREATE TABLE cities (
name text,
population real,
altitude int -- （フィート単位）
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
-- 継承元のテーブルもまとめて検索してくれる
SELECT name, altitude
FROM cities
WHERE altitude > 500;
-- ONLYつけたら指定テーブルのみ検索
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;