[SQL] PostgreSQL

PostgreSQL의 데이터 타입 

기본 타입 

TEXT, VARCHAR... 

 

사용자 정의 타입 

ex) gender_type 

 

TOAST (The Oversized-Attribute Storage Technique)

- 긴 데이터(Large Object)를 효율적으로 저장하기 위한 기술 

- 기본적으로 압축을 사용하여 공간을 절약 

- 긴 내용이 들어가는 경우 'TEXT' 타입으로 선언 -> 데이터베이스가 알아서 압축 or 별도의 테이블로 이동 

 

CREATE TYPE gender_type AS ENUM ('male', 'female');  -- 타입을 먼저 선언 

CREATE TABLE users (
  username CHAR(10) NOT NULL UNIQUE, -- ex) 'dodo'를 입력하면 나머지 6칸은 blank로 저장 
  email VARCHAR(50) NOT NULL UNIQUE, -- 최대 50글자 
  gender gender_type NOT NULL, 
  interests TEXT[] NOT NULL, 
  bio TEXT, 
  profile_photo BYTEA,  -- BYTEA: 대량 데이터 저장(바이너리; 이미지 등) 
  age SMALLINT NOT NULL CHECK (age >= 0), 
  is_admin BOOLEAN NOT NULL DEFAULT FALSE,
  joined_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  birth_date DATE NOT NULL,
  bed_time TIME NOT NULL, 
  graduation_year INTEGER NOT NULL CHECK (graduation_year BETWEEN 1901 AND 2115),
  intership_period INTERVAL  -- 기간 
  );
  

INSERT INTO users (username, email, gender, interests, bio, profile_photo, age, is_admin, joined_at, updated_at, birth_date, bed_time, graduation_year, intership_period) 
VALUES 
  ('dodo', 'dodo@example.com', 'male', ARRAY['coding', 'gaming'], 'I love coding and gaming.', NULL, 25, FALSE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1999-05-15', '23:30:00', 2021, INTERVAL '6 months'),
  ('mimi', 'mimi@example.com', 'female', ARRAY['reading', 'traveling'], 'A bookworm and an adventurer.', NULL, 30, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1994-08-20', '22:00:00', 2015, INTERVAL '1 year');


SELECT joined_at FROM users; 
-- output: 2025-03-06 17:58:41.714378+09

SELECT EXTRACT(YEAR FROM joined_at::DATE) FROM users;  
-- output: 2025

 

 

---

UNNEST 

array를 row 형태로 풀어준다(개별요소로 반환) 

각 배열 요소가 개별 행으로 출력 

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  interests TEXT[]
);

INSERT INTO users (username, interests) 
  VALUES 
    ('dodo', ARRAY['coding', 'gaming', 'music']),
    ('mimi', ARRAY['reading', 'traveling']);
  
SELECT username, unnest(interests) AS interest FROM users;

-- --------------------------------------
 username |  interest  
----------+-----------
 dodo     | coding
 dodo     | gaming
 dodo     | music
 mimi     | reading
 mimi     | traveling

SELECT string_to_array(genres, ',') FROM movies GROUP BY genres;
SELECT UNNEST(string_to_array(genres, ',')) FROM movies GROUP BY genres;
SELECT DISTINCT UNNEST(string_to_array(genres, ',')) FROM movies GROUP BY genres;
-- 중복값 제거 

INSERT INTO genres (name)
  SELECT DISTINCT UNNEST(string_to_array(genres, ',')) FROM movies GROUP BY genres;
-- genres 테이블의 name 칼럼에 값 넣기

 

CREATE TABLE movies_genres (
  movie_id BIGINT NOT NULL,
  genre_id BIGINT NOT NULL,
  createa_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (movie_id, genre_id),
  FOREIGN KEY (movie_id) REFERENCES movies (movie_id),
  FOREIGN KEY (genre_id) REFERENCES genres (genre_id)
  );
-- movies와 genres 간 bridge table 만들기 
  
SELECT movies.title, movies.movie_id, genres.genre_id, genres.name
  FROM movies
  JOIN genres ON movies.genres LIKE '%' || genres.name || '%' ;
  -- movies 테이블의 genres 칼럼이 genres 테이블의 name을 포함하는 조건으로 조인 
  
  
INSERT INTO movies_genres ( movie_id, genre_id) 
  SELECT movies.movie_id, genres.genre_id 
  FROM movies
  JOIN genres ON movies.genres LIKE '%' || genres.name || '%' ;
  -- 위에서 추출한 값을 movies_genres 테이블의 값으로 넣기 


ALTER TABLE movies DROP COLUMN genres;