Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
4
Help us understand the problem. What is going on with this article?
@aoi_erimiya

postgresqlチュートリアル抜粋(v10.4)

More than 1 year has passed since last update.

目的

仕事で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;
4
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
aoi_erimiya
No problem. Everything's fine.

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
4
Help us understand the problem. What is going on with this article?