[SQL] PostgreSQL Extensions

Extension(확장 기능) 

SELECT * FROM pg_available_extensions;
-- 가능한 확장 기능 확인


SELECT * FROM pg_extension;
-- 활성화된 extension 확인

---

HStore

- Key-Value 저장을 위한 확장 기능 

- 비구조화된(스키마 없는) 데이터 저장 가능 

- JSON과 비슷한 Key-Value 데이터 구조를 제공하나, 더 빠른 처리 가능 

 

JSON: nested 데이터 구조 가능(중첩 가능) 

HStore: key-Value only 

CREATE EXTENSION hstore;
-- hstore 활성화 


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


-- 구조화되지 않은 데이터 입력
INSERT INTO users (prefs) VALUES
('theme => dark, lang => kr, notifications => off'),
('theme => light, lang => es, notifications => on, push_notifications => on, email_notifications => off'),
('theme => dark, lang => it, start_page => dashboard, font_size => large');


-- 칼럼 형태로 조회하기 
SELECT 
  user_id, 
  prefs -> 'theme',
  prefs -> ARRAY['lang', 'notifications'],  -- 두개의 key를 한번에 받기 
  prefs ? 'font_size' AS has_font_size      -- 'font_size' 값 존재 여부 
FROM users;

 

더 많은 연산자 확인👇

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

---

PGCrypto 

데이터베이스에서 암호화 작업이 필요한 경우 

CREATE EXTENSION pgcrypto;
-- 익스텐션 활성화 


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


INSERT INTO users (username, password)
  VALUES ('dodo', crypt('user_password', gen_salt('bf')));
-- 비밀번호 해싱(고유값으로 변환)

---

UUID

데이터베이스에서 uuid를 생성

UUID(Universally Unique Identifier): 고유한 식별자를 생성하는 방법 중 하나 

데이터가 너무 많아 user_id가 BIGINT로 표현할 수 있는 범위를 넘어서는 경우 이것을 uuid로 변경하여 사용할 수 있다 

CREATE EXTENSION "uuid-ossp";
-- 익스텐션 활성화

CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT(uuid_generate_v4()),
  username VARCHAR(100),
  password VARCHAR(100)
  );


INSERT INTO users (username, password)
  VALUES ('dodo', '1234');