PostgreSQL Extensions

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

Extension

Extensions를 설치하면 PostgreSQL만으로 할 수 없던 작업을 할 수 있다.

---

 

Extension 설치

CREATE EXTENSION hstore;

 

설치된 Extension 확인

SELECT * FROM pg_extension;

 

Extension 제거

DROP EXTENSION hstore;

---

Hstore

https://www.postgresql.org/docs/current/hstore.html

F.17. hstore — hstore key/value datatype

1. Extension 설치

CREATE EXTENSION hstore;

2. Table 생성

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    prefs HSTORE
);

3. 데이터 입력

JSON 타입

중첩된 Key = Value 쌍을 가질 수 있다.

{
	"name" : 'Nico',
	"city" : {
                 "name" : "Seoul",
                 "population" : 120000
             }
}

Hstore

• JSON과 다르게 중첩된 Key = Value 쌍을 가질 수 없다.
• 무조건 단일 Key = Value를 가져야 한다.

INSERT INTO users (prefs)
VALUES
    ('theme => dark, lang => kr, notify => off'),
    ('theme => light, lang => ja, notify => on, push_notify => on, email_notify => off'),
    ('theme => dark, lang => en, notify => on, home => dashboard, font_size => large');

4. Hstore 조회

SELECT
    user_id,
    -- theme의 value 조회
    prefs -> 'theme' AS theme,
    -- lang, notify value를 객체로 조회
    prefs -> ARRAY['lang', 'notify'] AS lang_notify,
    -- font_size key를 가지고 있는지 조회
    prefs ? 'font_size' AS hasFont,
    -- push_notify, home 두 key 중 1개라도 갖고 있는지 조회
    prefs ?| ARRAY['push_notify', 'home'] AS hasPush,
    -- 모든 key를 배열로 조회
    akeys(prefs),
    -- 모든 value를 배열로 조회
    avals(prefs)
FROM users;

5. Hstore Update

5 - 1. 특정 row의 value 변경

UPDATE users
SET
    prefs['notify'] = 'off'
WHERE
    user_id = 3;

5 - 2. 전체 row에 key = value 추가

-- 모든 row에 'city' : 'Seoul', 'gender' : 'male' 추가
UPDATE users
SET
    prefs = prefs || hstore(
            ARRAY['city', 'gender'], 
            ARRAY['Seoul', 'male']
            );

6. Hstore Delete

UPDATE users
SET prefs = delete(prefs, 'gender');  -- 삭제할 key 입력

---

PGCrypto

PostgreSQL에서 암호화 작업을 하게 해 준다.

1. Extension 설치

CREATE EXTENSION pgcrypto;

2. Table 생성

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    username VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL
);

3. 데이터 암호화 (해시)

crypt를 사용하여 패스워드를 암호화

INSERT INTO users (username, password)
VALUES
    -- gen_salt('bf') -> BlowFish 알고리즘을 사용하여 패스워드 해시화
    ('nico', crypt('123@abc', gen_salt('bf')));

4. 패스워드 입력

입력한 패스워드와 일치하는 계정을 조회

SELECT
    username
FROM users
WHERE
    username = 'nico' AND
    -- 입력한 패스워드를 해시화한 결과값과 일치하는 데이터 조회
    password = crypt('123@abc', password);

---

UUID-OSSP

PostgreSQL에서 UUID를 생성하게 해 준다.

BIGINT로 표기 가능한 숫자의 한계가 존재하여 매우 많은 데이터를 저장하기 위해 무작위 문자열을 생성해 주는 UUID를 사용

1. Extension 설치

반드시 “ “를 사용해야 한다.

CREATE EXTENSION "uuid-ossp";

2. Table 생성

UUID를 생성하는 함수는 v1 ~ v5까지 있으며 각각 생성하는 원리가 다르다.

버전	특징
v1	타임 스탬프와 MAC 주소를 사용 만들어진 UUID를 통해 컴퓨터의 식별 정보가 노출될 가능성이 존재
v2	컴퓨터의 실제 MAC 주소 대신 임의의 멀티 캐스트 MAC 주소를 사용
v3	특정 문자열을 입력하여 MD5로 해시화하고 해당 문자열을 통해 랜덤으로 생성
v4	난수를 통해 생성
v5	v3와 방식은 같으나 MD5 대신 SHA-1을 사용한다.

CREATE TABLE users (
    user_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    username varchar(255) NOT NULL
)

3. 데이터 입력

INSERT INTO users (username) VALUES ('Nico'), ('Lynn');

4. UUID 확인

아래의 표처럼 UUID로 저장됨을 확인할 수 있다.

user_id	username
888e28b6-232a-4d51-9473-aec12bf9f7ca	Nico
2ea2b4fa-9e84-4f14-840a-97b51f7e5de9	Lynn