세븐 데이터 베이스를 읽고(3) Postgres 설치 및 CRUD, Join

세븐 데이터 베이스를 읽고(3) Postgres 설치 및 CRUD, Join

mac 설치

# 설치
brew install postgresql

# 서비스 시작 
pg_ctl -D /usr/local/var/postgres start

export PGDATA='/usr/local/var/postgres'
# 실행 확인
pg_ctl status 

# 버전확인
postgres -V

기능 확장

#스키마 생성 
createdb book

# postgres에 접속
psql book

# 접속 후 기능확장하기
CREATE EXTENSION tablefunc;
CREATE EXTENSION dict_xsyn;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION cube;

 

설치가 끝났으면 이제부터 계속 사용할 스키마를 만들어 보겠습니다.

다시 ctrl + d를 통해서 나온 다음 아래 명령어를 실행
# 확장 프로그램 확인
psql book -c "SELECT '1'::cube;"

위와 같은 결과가 출력됩니다.
psql book -c "SELECT '1'::cube;" 해당 명령어를 사용하고 에러가 발생하면 기능확장 부분이 잘 안된 것이니 확인 부탁드립니다. 

 

릴레이션, CRUD, 조인

postgres도 당연히 실행중인 서버에 연결하기 위해 명령형 쉘을 갖는 백엔드 서버를 제공한다. 섭버는 5432번 포트를 통해서 통신한다. 우리는 psql 쉘을 통해 통신할 수 있다. 

 

psql book

위 명령어로 접속 후 #마크를 볼 수 있는데 관리자로 실행할 때는 해시 마크를, 일반 사용자의 경우는 달러 기호($) 다음에 데이터베이스 이름을 기호로 보여준다.

 

또한 특별히 검색 없이 쉘에서 명령어 등을 설명하는 잘 작성된 도움말 문서를 가지고 있다. 

 

 

SQL 시작하기

PosgreSQL에서는 SQL 협약에 따라 릴레이션(Relation)을 테이블로 속성을 열(Column)로, 튜플을 행(row)으로 부른다. 

 

테이블 사용하기 

관계형 데이터베이스인 PostgreSQL은 설계가 우선되어야 하는 데이터 스토어이다. 즉 스키마를 먼저 설계 및 생성한 다음, 그 스키마에 따르는 데이터를 입력해야한다. 

 

테이블 생성시에는 테이블 이름과 데이터 타입을 갖는 열 목록 및 생략 가능한 제약조건 정보를 정의한다. 또한 각 테이블에는 특정 행을 찾기 위한 고유 식별자를 지정해야하는데 그것을 기본 키(primarykey)라고 한다. 또한 외부 키를 통해서 테이블 사이에 관계를 짓는다.           

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

이 테이블은 2바이트의 문자 코드로 식별되고, 국가명이 중복되지 않는 고유한 행들이 저장될 것입니다. 여기서는 두개의 칼럼 모두 제약을 두고 있습니다.

 

PRIMARY KEY, UNIQUE를 통해 동일한 국가 코드가 안들어오게 막습니다. 

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

해당 데이터들을 입력한 후 UNIQUE로 제약이된 country_name에 동일하게 입력하려고 하면 에러가 발생할 것입니다. 

 

SELECT * FROM countries;

그러나 데이터 중 존재하지 않는 국가가 존재한다. Loompaland라고 이데이터를 삭제해보겠다. 

 

DELETE FROM countries WHERE country_code = 'll';

실제 존재하는 국가들의 데이터만 countries 테이블에 남았으므로, 이제는 cities 테이블을 생성해보겠습니다. 

 

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)
);

이 경우 추가되는 countries_code 값이 citeis 테이블에도 있는 것이 되게 하려면 REFERENCES 키워드에 넣어야한다. 이때 country_code 열은 다른 테이블의 키를 참조하므로 그것을 외부 키(forien key) 제약 조건이라고한다. 

 

우선 도시명을 NULL이 안되게 설정하고 postal_code는 값이 없는 빈 문자열이 들어오지 않도록 검사하는 제약 조건이 지정되었습니다.(<>는 같지 않음을 의미한다.) 기본 키는 각 행을 고유하게 실별하는 것이므로 중복 값이 생기면 안된다. 여기서는 복합 키(country_code + postal_code)를 기본 키로 지정하였습니다. 

 

여기서 데이트 타입을 보면 text(어떤 크기도 가능한 문자열), varchar(9)(9개의 문자까지 가능, 가변형), char(2)(정확히 두 개의 문자로 된문자열)

 INSERT INTO cities VALUES ('Toronto', 'M4C1B5', 'ca');

이전 테이블을 만들 때 countries를 참조하여 country_code는 반드시 countries 테이블에 존재해야한다. 이것을 참조 무결성을 유지한다고 한다. References 키워드는 외부 키 열의 값을 다른 테이블의 기본 키 값으로 제한한다. 서로의 테이블에서 country_code에 동일한 코드가 있게 참조할 수 있어야한다. 

INSERT INTO cities VALUES ('Portland', '87200', 'us');

이번에는 당연히 값이 잘들어 가집니다. 왜냐하면 이전과 달리 us인 country_code가 countries에 존재하기 때문입니다. 

 

이번에는 업데이트를 해보겠습니다. 

 UPDATE cities SET postal_code = '97205' WHERE name = 'Portland';

postal_code를 수정했으며 WHERE을 통해 동일한 값을 찾아서 Potrland에 해당하는 postal_code를 수정했습니다. 

 

 

테이블의 조인과 조회

다른 데이터 베이스들과 다르게 PostgreSQL과 같은 관계형 데이터베이스는 데이터를 조회할 때 테이블을 조인하는 능력이 있어서 다른 데이터 베이스와 차별화된다. 본질적으로 조인은 두 개의 테이블을 결합하여 하나의 테이블로 반환하는 연산이다. 

1. 내부 조인

가장 간단한 형태로 다음과 같이 일치시킬 두 개의 열(각 테이블당 하나)을 ON 키워드를 사용해서 지정한다. 

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

이 조인에서는 cities 테이블의 모든 열들의 값과 countires 테이블의 country_name 값 country_code 값을 갖는 하나의 테이블을 반환한다.(country_code가 일치하는 값을 가져온다.) 

 

이전 cities의 테이블에서 primary key를 country_code와 postal_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
 );

(여기서 MATCH FULL은 한 칼럼안에 많은 데이터가 담겨있어야 해서 효율적으로 데이터를 찾기 위해서 자주 사용한다.)venue_id는 고유 키면서 데이터가 입력될때 마다 SERIAL 키로 인해 자동으로 증가 입력된다.(NTSQL에서는 Auto Increment)이다. 

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;

동일한 postal_code와 country_code를 참조하며 확인할 수 있다.

2. 외부 조인

외부 조인도 내부 조인처럼 두 개의 테이블을 결합하지만, 일치하는 열의 값이 다른 테이블에 없어도 기준이 되는 테이블의 결과가 항상 반환되어야 할 때 사용한다. 

 

이번에는 새로운 events 테이블을 추가할 것이다.

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

 

추가 데이터 삽입 

INSERT INTO events (title, starts, ends, venue_id) 
VALUES ('LARP Club', '2012-02-15 17:30:00', '2012-02-15 19:30:00', 4);

INSERT INTO events (title, starts, ends) 
VALUES ('April Fools Day', '2012-04-01 00:00:00', '2012-04-01 23:59:00');

INSERT INTO events (title, starts, ends) 
VALUES ('Christmas Day', '2012-12-25 00:00:00', '2012-12-25 23:59:00');

 

내부 조인

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

내부 조인에서는 venue_id와 동일한 행들만 반환한다. 다른 event 들은 다 null이었기 때문에 venues의 테이블에는 nulll인 venue_id가 존재하지 않기 때문에 따라서 나머지는 아무 결과도 나오지 않습니다. 이외의 모든 이벤트를 참조하기 위해서는 외부 조인을 사용해야한다. 기준을 왼쪽 두냐 오른쪽에 두냐에 따라 LEFT, RIGHT를 사용한다. 아니면 모든 부분을 조인하고 싶다면 FULL JOIN을 사용해주면 된다.

 

인덱스를 사용한 빠른 검색

PostgreSQL의 성능은 데이터 블록의 효율적인 관리, 디스크 읽기 횟수 줄이기, 쿼리의 최적화 등등이다. 그러나 이런 것은 결과를 빨리 가져오는 데만 도움이 된다. 만일 events 테이블에서 Cristmas Day라는 title 열의 값을 쿼리한다면, 차례대로 읽어서 그것과 일치하는 데이터를 찾아야한다. 이는 일치하는 데이터를 빨리 찾을 수 있는 데이터를 빨리 찾을 수 있는 인덱스가 없기 때문이다. 

 

인덱스는 쿼리를 수행할 때 테이블 전체 스캔을 피하기 위해 만든 특별한 데이터 구조이다. 

 

PostgreSQL의 경우 기본 키의 인덱스를 자동으로 생성하며, 인덱스의 키는 기본 키열의 값이 되고, 인덱스의 값은 디스크의 행을 가리킨다. 

CREATE INDEX events_title ON events USING hash (title);

이럴 때 인덱스의 키가 중복되는 것이 없어야 한다. 크거나 작거나 같은 것을 찾는 경우는 B-tree와 같은 인덱스를 사용하는 것이 간단한 해시보다 유연성이 좋다. 

CREATE INDEX events_starts
ON events USING btree(starts);

이후로 어떤 날짜의 범위에 해당되는 이벤트 데이터를 쿼리하면, 테이블 스캔을 사용 하지 않고 인덱스를 사용하게 될 것이다. 성능적으로 엄청 빨라진다.

 

\di 명령어를 통해 index 목록을 모두 확인할 수 있다. 내가 만든 것 뿐만 아니라 몇가지 더 추가된 것을 확인할 수 있다.

FOREIGN KEY 제약조건을 지정하면 PostgreSQL에서 참조 목표가 되는 열의 인덱스들을 자동으로 생성한다. 

실습 1. 예제에 추가

실습 2. 1에서 생성된 모든 테이블을 조인하되 LARP Club 이벤트가 개최되는 국가를 찾는 Select 쿼리를 작성한다.
SELECT * FROM venues v FULL JOIN events e ON v.venue_id = e.venue_id;
SELECT * FROM venues v JOIN cities c ON c.country_code = v.country_code;
SELECT * FROM cities c JOIN countries ON c.country_code = countries.country_code;
SELECT countries.country_name FROM cities c JOIN countries ON c.country_code = countries.country_code;

실습 3. venues 테이블에 active라는 이름의 boolean 타입 열을 추가하고 디폴트 값을 TRUE로 갖도록 하라

ALTER TABLE venues ADD active boolean DEFAULT 'TRUE';