7つのデータベース 7つの世界 2章 PostgreSQL 1日目

テーブル作成

CREATE TABLE countries (
  country_code char(2) PRIMARY KEY,
  country_name text UNIQUE
);

データ保存

INSERT INTO countries (country_code, country_name)
VALUES ('us', 'Unitate States'), ('mx', 'Mexico'), ('au', 'Australia'), ('gb', 'United Kingdom'), ('de', 'Germany'),('ll', 'Loompaland');

データ削除

DELETE FROM countries WHERE country_code='ll';

テーブル作成（外部キー制約）

CREATE TABLE cities (
  name text NOT NULL,
  postal_code varchar(9) CHECK (postal_code <> ''),
  country_code char(2) REFERENCES countries,
  PRIMARY KEY (country_code, postal_code)
);

保存と更新

INSERT INTO cities VALUES ('Portland', '87200', 'us');
UPDATE cities SET postal_code='97205' WHERE name = 'Portland';

結合参照

SELECT cities.*, country_name
 FROM cities INNER JOIN countries
 ON cities.country_code = countries.country_code;

   name   | postal_code | country_code |  country_name  
----------+-------------+--------------+----------------
 Portland | 97205       | us           | Unitate States
(1 row)

複数の外部キーがある場合

CREATE TABLE venues (
  venue_id SERIAL PRIMARY KEY,
  name varchar(255),
  street_address text,
  type char(7) CHECK (type in ('public', 'private')) DEFAULT 'public',
  postal_code varchar(9),
  country_code char(2),
  FOREIGN KEY(country_code, postal_code)
    REFERENCES cities (country_code, postal_code) MATCH FULL
);
INSERT INTO venues (name, postal_code, country_code)
 VALUES ('Crystal Ballroom', '97205', 'us');

SELECT v.venue_id, v.name, c.name 
FROM venues v INNER JOIN cities c
  ON v.postal_code = c.postal_code AND v.country_code = c.country_code;

 venue_id |       name       |   name   
----------+------------------+----------
        1 | Crystal Ballroom | Portland
(1 row)

RETURNING で主キー確認

INSERT INTO venues (name, postal_code, country_code) VALUES ('Voodoo Donuts', '97205', 'us') RETURNING venue_id;

 venue_id 
----------
        2
(1 row)

INSERT 0 1

外部結合

CREATE TABLE events (
  event_id SERIAL PRIMARY KEY,
  title varchar(255),
  starts timestamp,
  ends timestamp,
  venue_id integer REFERENCES venues 
);

INSERT INTO events (title, starts, ends, venue_id)
 VALUES ('LARP Club', '2012-02-15 17:30:00', '2012-02-15 19:30:00', 2),
  ('April Fools Day', '2012-04-01 00:00:00', '2012-04-01 23:59:00', NULL),
  ('Christmas Day', '2012-12-25 00:00:00', '2012-12-25 23:59:00', NULL);

join

SELECT e.title, v.name 
FROM events e JOIN venues v
  ON e.venue_id = v.venue_id;

   title   |     name      
-----------+---------------
 LARP Club | Voodoo Donuts
(1 row)

left outer join

SELECT e.title, v.name 
FROM events e LEFT JOIN venues v
  ON e.venue_id = v.venue_id;

      title      |     name      
-----------------+---------------
 LARP Club       | Voodoo Donuts
 April Fools Day | 
 Christmas Day   | 
(3 rows)

インデックスで高速化

CREATE INDEX events_title ON events USING hash (title);
CREATE INDEX events_start ON events USING btree(starts);

スキーマのインデックス確認

\di

                         List of relations
 Schema |            Name            | Type  |  Owner   |   Table   
--------+----------------------------+-------+----------+-----------
 public | cities_pkey                | index | postgres | cities
 public | countries_country_name_key | index | postgres | countries
 public | countries_pkey             | index | postgres | countries
 public | events_pkey                | index | postgres | events
 public | events_start               | index | postgres | events
 public | events_title               | index | postgres | events
 public | venues_pkey                | index | postgres | venues
(7 rows)

1日目の宿題

1. 作成したテーブルをpg_class から選択

• 参考: 43.9. pg_class

これまでに作ったevents を選んでみる．

SELECT * FROM pg_class WHERE relname = 'events';

2. LARP Club というイベントが開催される国を探す

events -> venues -> countries
と外部キーを辿って結合する．

SELECT c.country_name, e.title 
FROM events e
INNER JOIN venues v ON e.venue_id = v.venue_id
INNER JOIN countries c ON v.country_code = c.country_code;

3. venues テーブルにavtive というboolean型の列を追加

ALTER TABLE table_name ADD COLUMN を使う

ALTER TABLE venues ADD COLUMN active boolean DEFAULT TRUE;
SELECT * FROM venues;