LoginSignup
3

More than 5 years have passed since last update.

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

Posted at

テーブル作成

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 から選択

これまでに作った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;

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3