PostgreSQL에서 배열 다루기

PostgreSQL에서 배열 다루기

이제 기본은 알았으니까, SQL 쿼리에서 배열을 실제로 만들어보자. 여기서부터 진짜 재밌어져!

ARRAY[] 생성자 SELECT에서 쓰기

ARRAY[] 생성자는 SELECT 쿼리에서 배열을 명시적으로 만들 때 특히 편해. PostgreSQL한테 "야, 이거 배열이야!"라고 말하는 거랑 똑같지.

-- 숫자 배열 만들기
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- 문자열 배열 만들기
SELECT ARRAY['월요일', '화요일', '수요일'] AS weekdays;

ARRAY[]가 {} 문법보다 좋은 점

1. 타입을 명확하게 지정할 수 있음:

-- ARRAY[]로 타입을 명확하게 지정 가능
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- {} 쓸 땐 좀 더 조심해야 함
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. 복잡한 쿼리에서 더 읽기 쉬움:

SELECT
product_name,
ARRAY[category, subcategory, brand] AS product_hierarchy
FROM products;

예시: 숫자 배열 만들기

클래식부터 시작하자. 숫자 배열을 만들어야 한다고 해보자:

SELECT ARRAY[1, 2, 3, 4, 5] AS my_array;

결과는 이렇게 나와:

참고로 PostgreSQL은 배열을 {} 형식으로 보여줘 — 그냥 배열이라는 걸 보여주는 PostgreSQL만의 스타일이야. 금방 익숙해질 거야.

예시: 문자열 배열 만들기

숫자 대신 문자열이 필요하면, 그냥 따옴표만 붙이면 돼:

SELECT ARRAY['사과', '바나나', '오렌지'] AS fruits;

결과:

참고로, PostgreSQL은 진짜 편해. 한글이든 다른 알파벳이든 배열은 문제없이 잘 돌아가.

예시: 다른 타입(예: 날짜) 데이터로 배열 만들기

날짜 배열을 만들고 싶으면? 완전 쉬워:

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS important_dates;

결과:

::DATE에 주목! PostgreSQL한테 이게 DATE 타입이라고 명확하게 알려준 거야. 이거 없으면 그냥 문자열로 받아들일 수도 있어서, 날짜엔 꼭 타입을 지정해 주는 게 좋아.

array_agg()로 데이터 배열로 집계하기

이제 좀 더 복잡하고 재밌는 부분으로 가보자. 이미 데이터가 있는 테이블에서, 그걸 배열로 묶고 싶으면? 바로 array_agg() 함수가 필요해.

array_agg()로 여러 행을 배열로 바꿀 수 있다는 게 진짜 강력한 기능이야.

기본 사용법:

-- 테스트용 테이블 만들기
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, '안나'), (1, '오토'), (1, '마리아'),
(2, '알렉스'), (2, '키라'),
(3, '엘레나');

-- 그룹별로 학생 묶기
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

배열 안의 요소 정렬하기:

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS students_sorted
FROM students
GROUP BY group_id;

집계할 때 필터링하기:

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE '아%') AS students_a
FROM students
GROUP BY group_id;

실전 예제

배열은 실생활에서 진짜 많이 써: 태그 저장, 권한 관리, 하루 동안의 사용자 행동 모으기 등등. 아래 예제들을 보면 PostgreSQL에서 배열을 어디에 어떻게 쓰는지 감이 올 거야.

예시 1: 블로그 태그 시스템

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- 여러 문법으로 삽입하기
INSERT INTO blog_posts (title, content, tags) VALUES
    ('PostgreSQL 배우기', '글 내용...',
     ARRAY['PostgreSQL', 'SQL', '데이터베이스']),
    ('2024년 웹 개발', '글 내용...',
     '{"JavaScript", "React", "Node.js"}'),
    ('머신러닝', '글 내용...',
     ARRAY['ML', 'Python', 'Data Science']);

-- 태그로 글 찾기
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

예시 2: 사용자 권한 시스템

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

INSERT INTO users (username, permissions) VALUES
('admin', ARRAY['읽기', '쓰기', '삭제', '사용자_관리']),
('editor', ARRAY['읽기', '쓰기']),
('viewer', ARRAY['읽기']);

-- 시스템에 있는 모든 고유 권한 집계
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

예시 3: 사용자 행동 이력

CREATE TABLE user_actions (
user_id INTEGER,
action TEXT,
action_date DATE
);

INSERT INTO user_actions VALUES
(1, '로그인', '2024-01-01'),
(1, '프로필_보기', '2024-01-01'),
(1, '설정_수정', '2024-01-01'),
(2, '로그인', '2024-01-01'),
(2, '로그아웃', '2024-01-01');

-- 사용자별로 하루 행동 묶기
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS daily_actions
FROM user_actions
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. 배열 쿼리: 조회와 필터링

배열이 있으면, 그걸 뽑아내고 분석할 줄도 알아야지. 그냥 SELECT로 배열을 가져올 수 있어:

SELECT tags FROM articles WHERE id = 1;

이렇게 나와:

근데 만약 PostgreSQL이라는 특정 태그가 있는 글을 찾고 싶으면? 이건 다음 강의에서 자세히 다룰 거지만, 핵심은 배열 덕분에 내부 값을 쉽게 찾을 수 있다는 거야.