SQL - PostgreSQL, Extensions

PostgreSQL

 

 

✅ 사용 가능한 PostgreSQL의 Extensions 목록 보는 법

---

SELECT
	*
FROM
	pg_available_extensions;

 

 

✅ Extension 활성화 방법

---

CREATE EXTENSION hstore;

 

 

✅ hstore

---

Key, Value 데이터를 저장할 수 있게 해준다.(현재는 JSONB를 많이 사용한다.)

 

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'],
	prefs ? 'font-size' AS has_font_size,
	prefs ?| ARRAY['push_notifications', 'start_page']
FROM
	users;

key와 value를 theme => dark 이런 식으로 줄 수 있다.

 

SELECT
	user_id,
	akeys (prefs),
	avals (prefs),
	EACH (prefs)
FROM
	users;

akeys: all keys, avals: all values, each: prefs의 모든 key와 value를 한 쌍씩 순회하며 보여준다.

 

UPDATE users
SET
	prefs = prefs || hstore (ARRAY['currency', 'cookies_ok'], ARRAY['krw', 'yes']);

SELECT
	*
FROM
	users;

새로운 key, value값을 넣어 기존 값 수정하기

 

UPDATE users
SET
	prefs = DELETE (prefs, 'cookies_ok');
SELECT
	*
FROM
	users;

key값으로 삭제하기

 

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

F.17. hstore — hstore key/value datatype

 

 

✅ 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
	('nico', crypt ('user_password', gen_salt ('bf'))); -- bf -> BlowFish algorithm

 

SELECT
	username
FROM
	users
WHERE
	PASSWORD = crypt ('user_password', PASSWORD);

해싱된 비밀번호 일치 확인

 

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

F.26. pgcrypto — cryptographic functions

 

 

✅ uuid-ossp

---

데이터베이스에서 uuid를 생성할 수 있도록 해주는 아주 유용한 extension

BIGINT도 꽤 큰 숫자이긴 하지만 한계가 있다.(PostgreSQL에는 unsigned number도 없음)

 

데이터가 너무 많아서 BIGINT로 표현할 수 있는 범위를 넘어간다면 id를 uuid로 변경한다.

(무작위로 생성된 문자열)

 

활성화

CREATE EXTENSION "uuid-ossp";

얘는 무조건 ""로 적어줘야 한다.

 

사용

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

 

 

https://www.postgresql.org/docs/current/uuid-ossp.html

F.49. uuid-ossp — a UUID generator