PostgreSQL 설치하기

PostgreSQL 2014. 8. 10. 14:43
반응형

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 문









반응형
Posted by seungkyua@gmail.com
,