1. 기본 설치
$ sudo apt-get update
$ sudo apt-get install postgresql
/usr/share/postgresql/9.1/ (설치위치)
2. 접속 및 기능 확장
http://www.postgresql.org/docs/9.1/static/contrib.html
tablefunc, dict_xsyn, fuzzystrmatch, pg_trgm, cube 기능 확장
$ sudo apt-get install postgresql-contrib-9.1
$ sudo su - postgres
$ psql
postgres=# CREATE EXTENSION tablefunc;
postgres=# CREATE EXTENSION dict_xsyn;
postgres=# CREATE EXTENSION fuzzystrmatch;
postgres=# CREATE EXTENSION pg_trgm;
postgres=# CREATE EXTENSION cube;
postgres=# \q (종료)
3. 데이터베이스 생성 및 접속
$ createdb mydb
$ psql mydb
mydb=# \h CREATE INDEX (sql 명령어 help)
mydb=# \? (psql 명령어 help)
4. 테이블 생성 및 조회
CREATE TABLE countries (
country_code char(2) PRIMARY KEY,
country_name text UNIQUE
);
INSERT INTO countries (country_code, country_name)
VALUES ('us', 'United States'), ('mx', 'Mexico'), ('au', ' Australia'),
('gb', 'United Kingdom'), ('de', 'Germany');
CREATE TABLE cities (
name text NOT NULL, (NULL 허용 안함)
postal_code varchar(9) CHECK (postal_code <> ''), (empty string 허용 안함)
country_code char(2) REFERENCES countries NOT NULL,
PRIMARY KEY (country_code, postal_code)
);
INSERT INTO cities
VALUES ('portland', '97205', 'us');
SELECT cities.*, country_name
FROM cities INNER JOIN countries
ON cities.country_code = countries.country_code;
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 (두 열의 값이 모두 있거나 NULL)
);
INSERT INTO venues (name, postal_code, country_code)
VALUES ('Crystal Ballroom', '97205', 'us'), ('Voodoo Donuts', '97205', 'us');
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
title varchar(255),
starts timestamp,
ends timestamp,
venue_id int REFERENCES venues
);
INSERT INTO events (title, starts, ends, venue_id)
VALUES ('LARP Club', '2014-02-05 17:30', '2014-02-05 19:30', 2),
('April Fools Day', '2014-04-01 00:00', '2014-04-01 23:59', NULL),
('Christmas Day', '2014-12-25 00:00', '2014-12-25 23:59', NULL);
SELECT e.title, v.name
FROM events e LEFT JOIN venues v (좌측 테이블 events 가 기준)
ON e.venue_id = v.venue_id;
title | name
------------- --+---------------
LARP Club | Voodoo Donuts
April Fools Day |
Christmas Day |
SELECT e.title, v.name
FROM events e RIGHT JOIN venues v (우측 테이블 venues 가 기준)
ON e.venue_id = v.venue_id;
title | name
-----------+------------------
LARP Club | Voodoo Donuts
| Crystal Ballroom
SELECT e.title, v.name
FROM events e FULL JOIN venues v (두 테이블이 모두 기준 union 가 같음)
ON e.venue_id = v.venue_id;
title | name
---------------+------------------
LARP Club | Voodoo Donuts
April Fools Day |
Christmas Day |
| Crystal Ballroom
5. 인덱스 생성 및 조회
CREATE INDEX events_title
ON events USING hash (title); (HASH 는 값이 중복되는 것이 없을 때 사용)
CREATE INDEX events_starts
ON events USING btree (starts); (btree 는 크거나 작거나 같은 것을 찾을 때 사용)
mydb=# \di (데이터베이스 내의 모든 인덱스 목록 조회)
6. 집합 (윈도우 함수)
INSERT INTO venues (name, postal_code, country_code)
VALUES ('My Place', '97205', 'us');
INSERT INTO events (title, starts, ends, venue_id)
VALUES ('Moby', '2014-02-06 21:00', '2014-02-06 23:00', 1)
('Wedding', '2014-02-26 21:00', '2014-02-26 23:00', 2),
('Dinner with Mom', '2014-02-26 18:00', '2014-02-26 20:30', 3),
('Valentine''s Day', '2014-02-14 00:00', '2014-02-14 23:59', NULL);
SELECT venue_id, count(*)
FROM events
GROUP BY venue_id
ORDER BY venue_id;
venue_id | count
---------+-------
1 | 1
2 | 2
3 | 1
| 3
SELECT venue_id, count(*)
OVER (PARTITION BY venue_id)
FROM events
ORDER BY venue_id;
venue_id | count
---------+-------
1 | 1
2 | 2
2 | 2
3 | 1
| 3
| 3
| 3
SELECT title, count(*)
OVER (PARTITION BY venue_id)
FROM events;
7. Transaction & Plan
BEGIN TRANSACTION;
...
SQL 문
...
END;
EXPLAIN VERBOSE
...
SQL 문
