Created
March 26, 2013 15:17
-
-
Save hrstt/5246173 to your computer and use it in GitHub Desktop.
7つのデータベース 7つの世界 2章 PostgreSQL 1日目 ref: http://qiita.com/items/18847b18646a1965c398
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE countries ( | |
country_code char(2) PRIMARY KEY, | |
country_name text UNIQUE | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO countries (country_code, country_name) | |
VALUES ('us', 'Unitate States'), ('mx', 'Mexico'), ('au', 'Australia'), ('gb', 'United Kingdom'), ('de', 'Germany'),('ll', 'Loompaland'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO venues (name, postal_code, country_code) VALUES ('Voodoo Donuts', '97205', 'us') RETURNING venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
venue_id | |
---------- | |
2 | |
(1 row) | |
INSERT 0 1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE events ( | |
event_id SERIAL PRIMARY KEY, | |
title varchar(255), | |
starts timestamp, | |
ends timestamp, | |
venue_id integer REFERENCES venues | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT e.title, v.name | |
FROM events e JOIN venues v | |
ON e.venue_id = v.venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
title | name | |
-----------+--------------- | |
LARP Club | Voodoo Donuts | |
(1 row) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT e.title, v.name | |
FROM events e LEFT JOIN venues v | |
ON e.venue_id = v.venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
title | name | |
-----------------+--------------- | |
LARP Club | Voodoo Donuts | |
April Fools Day | | |
Christmas Day | | |
(3 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE INDEX events_title ON events USING hash (title); | |
CREATE INDEX events_start ON events USING btree(starts); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\di |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELETE FROM countries WHERE country_code='ll'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * FROM pg_class WHERE relname = 'events'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ALTER TABLE venues ADD COLUMN active boolean DEFAULT TRUE; | |
SELECT * FROM venues; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO cities VALUES ('Portland', '87200', 'us'); | |
UPDATE cities SET postal_code='97205' WHERE name = 'Portland'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT cities.*, country_name | |
FROM cities INNER JOIN countries | |
ON cities.country_code = countries.country_code; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name | postal_code | country_code | country_name | |
----------+-------------+--------------+---------------- | |
Portland | 97205 | us | Unitate States | |
(1 row) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
venue_id | name | name | |
----------+------------------+---------- | |
1 | Crystal Ballroom | Portland | |
(1 row) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment