[PostgreSQL] 배열 타입 + ANY / unnest 로 태그 컬럼 다루기

설치·접속: PostgreSQL 설치와 접속

부제: 게시글 태그를 별도 테이블+조인 대신 text[] 한 컬럼에 담았는데, "이 태그 달린 글"을 찾거나 태그별로 개수를 세야 할 때

-- '값' = ANY(배열) : 배열 안에 그 값이 있는가
SELECT id, title FROM m4_posts WHERE 'PostgreSQL' = ANY(tags);
-- 배열 겹침 && : 두 배열이 하나라도 공통 원소를 가지면 참
SELECT id, title FROM m4_posts WHERE tags && '{Python,Docker}';

 id |         title          
----+------------------------
  1 | PostgreSQL 인덱스 정리
  3 | JSONB 실전 활용

 id |        title        
----+---------------------
  2 | 도커 컴포즈 입문
  5 | FastAPI 비동기 패턴

배열 검색의 세 연산자를 구분하면 끝이다. 값 = ANY(tags)는 "특정 원소 하나가 있나", tags && '{...}'는 "하나라도 겹치나"(OR), tags @> '{...}'는 "전부 포함하나"(AND). 태그별 통계처럼 원소 단위로 다뤄야 하면 unnest(tags)로 배열을 여러 행으로 펼친 뒤 일반 SQL처럼 GROUP BY 한다. 함정: = ANY는 인덱스를 잘 못 타지만, &&·@>는 배열 컬럼에 GIN 인덱스를 걸면 그대로 가속된다.

이렇게도 쓴다

모든 태그를 다 포함하는 글만 (AND) — @>.

SELECT id, title FROM m4_posts WHERE tags @> '{DB,PostgreSQL}';

 

태그별 사용 횟수 집계 — unnest로 펼치고 GROUP BY. (조합: unnest)

SELECT tag, count(*) FROM m4_posts, unnest(tags) AS tag
GROUP BY tag ORDER BY 2 DESC, 1;

 

배열 길이 재기와 원소 추가 — ||로 이어붙이기.

SELECT title, array_length(tags,1) AS n, tags || '{추천}' AS added
FROM m4_posts WHERE id=1;

 

여러 행을 배열 하나로 되접기 — unnest의 반대, array_agg.

SELECT array_agg(name ORDER BY id) FROM m4_products WHERE id <= 3;

 

배열 검색을 GIN 인덱스로 가속 — &&/@>가 Bitmap Index Scan을 탄다.

CREATE INDEX idx_posts_tags ON m4_posts_big USING gin (tags);
EXPLAIN ANALYZE SELECT count(*) FROM m4_posts_big WHERE tags @> '{보안}';

 ->  Bitmap Heap Scan on m4_posts_big  (... rows=1250 ...)
       Recheck Cond: (tags @> '{보안}'::text[])
       ->  Bitmap Index Scan on idx_posts_tags
             Index Cond: (tags @> '{보안}'::text[])