-- 배열 컬럼 선언 및 삽입
CREATE TABLE articles (
  id       bigserial PRIMARY KEY,
  title    text,
  tags     text[],
  scores   integer[]
);

INSERT INTO articles (title, tags, scores)
VALUES ('PostgreSQL 배열', ARRAY['db', 'sql', 'postgres'], ARRAY[90, 85, 92]);

-- 포함 검색 + GIN 인덱스
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

SELECT id, title FROM articles
WHERE tags @> ARRAY['postgres'];  -- 'postgres' 태그를 포함하는 행

-- 요소 접근 (1-based)
SELECT tags[1] FROM articles;  -- 'db'

-- 배열 선언 방법
CREATE TABLE products (
  id          bigserial PRIMARY KEY,
  name        text,
  tags        text[],          -- 1차원 배열
  matrix      integer[][],     -- 2차원 배열
  dims        float[3]         -- 고정 길이 (선언만 제한, 실제 강제 안 됨)
);

-- 리터럴 문법
INSERT INTO products (name, tags)
VALUES
  ('Widget', '{red, blue, green}'),           -- 중괄호 리터럴
  ('Gadget', ARRAY['small', 'portable']);     -- ARRAY 생성자

-- 요소 접근: 1-based 인덱스
SELECT tags[1], tags[2] FROM products;

-- 슬라이싱 (시작:끝 포함)
SELECT tags[1:2] FROM products;  -- {red,blue}

-- 배열 길이
SELECT array_length(tags, 1) FROM products;  -- 1차원 길이

-- 배열 이어붙이기
UPDATE products SET tags = tags || ARRAY['sale'] WHERE id = 1;

-- 요소 제거 (array_remove)
UPDATE products SET tags = array_remove(tags, 'blue') WHERE id = 1;

-- = ANY(array): 배열 안에 해당 값이 있는지 (GIN 인덱스 비사용)
SELECT * FROM products WHERE 'red' = ANY(tags);

-- @> (포함, contains): 왼쪽 배열이 오른쪽 배열의 모든 요소를 포함 (GIN 사용 가능)
SELECT * FROM products WHERE tags @> ARRAY['red', 'blue'];

-- <@ (피포함, contained by): 왼쪽 배열이 오른쪽 배열에 완전히 포함됨
SELECT * FROM products WHERE tags <@ ARRAY['red', 'blue', 'green'];

-- && (겹침, overlap): 공통 요소가 하나라도 있으면 true (GIN 사용 가능)
SELECT * FROM products WHERE tags && ARRAY['red', 'yellow'];

-- ALL: 배열의 모든 요소가 조건을 만족
SELECT * FROM products WHERE 5 > ALL(scores);  -- 모든 점수가 5 미만

-- 배열 위치 검색
SELECT array_position(ARRAY['a','b','c'], 'b');  -- 2

-- 배열 포함 여부를 bool로 반환
SELECT array_position(tags, 'red') IS NOT NULL AS has_red
FROM products;

-- GIN 인덱스 생성
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- @>는 GIN 인덱스 사용 → 빠름
EXPLAIN SELECT * FROM products WHERE tags @> ARRAY['red'];
-- Bitmap Index Scan on idx_products_tags

-- = ANY()는 GIN 인덱스 미사용 → 순차 스캔
EXPLAIN SELECT * FROM products WHERE 'red' = ANY(tags);
-- Seq Scan on products

-- 기본 unnest
SELECT unnest(ARRAY['a', 'b', 'c']);
-- a
-- b
-- c

-- 배열 컬럼 전개: 태그별 게시글 수 집계
SELECT tag, count(*) AS article_count
FROM articles, unnest(tags) AS tag
GROUP BY tag
ORDER BY article_count DESC;

-- WITH ORDINALITY: 배열 인덱스(위치) 함께 반환
SELECT elem, idx
FROM unnest(ARRAY['x', 'y', 'z']) WITH ORDINALITY AS t(elem, idx);
-- x | 1
-- y | 2
-- z | 3

-- 여러 배열 동시 전개 (같은 위치 요소가 같은 행)
SELECT unnest(ARRAY[1,2,3]) AS id,
       unnest(ARRAY['a','b','c']) AS label;
-- 1 | a
-- 2 | b
-- 3 | c

-- 배열 → 집계 역방향: array_agg로 다시 배열로 묶기
SELECT author_id, array_agg(tag ORDER BY tag) AS all_tags
FROM articles, unnest(tags) AS tag
GROUP BY author_id;

-- 중복 제거 후 배열 재구성
SELECT array(
  SELECT DISTINCT unnest(tags) FROM articles WHERE author_id = 1
  ORDER BY 1
) AS unique_tags;

-- 인덱스 없이: 순차 스캔 (100만 행에서 수 초)
EXPLAIN ANALYZE SELECT id FROM articles WHERE tags @> ARRAY['postgres'];
-- Seq Scan on articles  Execution Time: 2800 ms

-- GIN 인덱스 생성
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 인덱스 사용: Bitmap Index Scan
EXPLAIN ANALYZE SELECT id FROM articles WHERE tags @> ARRAY['postgres'];
-- Bitmap Index Scan on idx_articles_tags  Execution Time: 12 ms

-- GIN vs GiST: 배열에는 GIN이 더 빠른 읽기 성능
-- GiST는 쓰기 비용이 낮고, GIN은 읽기 성능이 높음
-- 검색 빈도가 높은 프로덕션 환경 → GIN 권장

-- 인덱스 크기 확인
SELECT pg_size_pretty(pg_relation_size('idx_articles_tags')) AS index_size;

-- 부분 인덱스: 특정 조건 행만 인덱싱 (크기 절감)
CREATE INDEX idx_active_tags ON articles USING GIN (tags)
  WHERE status = 'published';