PostgreSQL: 다중 행을 하나의 문자열 또는 JSON 타입으로 만들기

PGSQL에서 다중 행 혹은 다중 열 데이터를 한 문자열로 만드는 쿼리 예제입니다.
더 다양한 방법과 상세한 설명은 하단의 "함께보기"에 있는 링크를 통해서 확인 가능합니다.

참고로 문자열 작업 시 가장 많은 실수는 데이터 타입 오류가 많습니다.
서로 다른 데이터 타입이라면 :: 이용하여 데이터 타입을 지정해서 확인해보시고 PGSQL 버전에 따라 지원하지 않는 기능도 있습니다.

테스트 데이터 만들기

CREATE TABLE t (name TEXT, age INT);
INSERT INTO t VALUES ('홍길동', 20);
INSERT INTO t VALUES ('영이', 22);
INSERT INTO t VALUES ('철이', 30);
INSERT INTO t VALUES ('김태균', 25);
INSERT INTO t VALUES ('김태균', 32);

문자열 합치기

SQL Fiddle: 쿼리문 테스트 페이지

-- Query
SELECT name||' / '||age AS name_age FROM t;

-- Result
  name_age
-------------
 홍길동 / 20
 영이 / 22
 철이 / 30
 김태균 / 25
 김태균 / 32
(5 rows)

테이블 전체를 JSON 만들기

SQL Fiddle: 쿼리문 테스트 페이지

-- Query
SELECT array_to_json(array_agg(t)) FROM t;

-- Result: array_to_json
-------------------------
 [{"name":"홍길동","age":20},{"name":"영이","age":22},{"name":"철이","age":30},{"name":"김태균","age":25},{"name":"김태균","age":32}]
(1 row)

다중 행을 JSON 만들기

SQL Fiddle: 쿼리문 테스트 페이지

-- Query
SELECT array_to_json(array(
    SELECT row_to_json(tmp)
    FROM (
        SELECT name FROM t GROUP BY name
    ) tmp
));

-- Result: array_to_json
-------------------------
 [{"name":"홍길동"},{"name":"영이"},{"name":"김태균"},{"name":"철이"}]
(1 row)

전체 열을 행 단위로 JSON 만들기

SQL Fiddle: 쿼리문 테스트 페이지

-- Query
SELECT row_to_json(t) FROM t;

-- Result: row_to_json
-------------------------
 {"name":"홍길동","age":20}
 {"name":"영이","age":22}
 {"name":"철이","age":30}
 {"name":"김태균","age":25}
 {"name":"김태균","age":32}
(5 rows)

특정 열만 행 단위로 JSON 만들기

SQL Fiddle: 쿼리문1 확인하기
SQL Fiddle: 쿼리문2 확인하기

-- Query 1
SELECT row_to_json(row(name, age)) FROM t;

-- Result: row_to_json
-------------------------
 {"f1":"홍길동","f2":20}
 {"f1":"영이","f2":22}
 {"f1":"철이","f2":30}
 {"f1":"김태균","f2":25}
 {"f1":"김태균","f2":32}
(5 rows)

-- Query 2
SELECT row_to_json(tmp)
FROM (
    SELECT
        name, age
    FROM t
) tmp;

-- Result: row_to_json
----------------------------
 {"name":"홍길동","age":20}
 {"name":"영이","age":22}
 {"name":"철이","age":30}
 {"name":"김태균","age":25}
 {"name":"김태균","age":32}
(5 rows)

특정 열만 열 단위로 JSON 만들기

SQL Fiddle: 쿼리문 테스트 페이지

-- Query
SELECT row_to_json(tmp)
FROM (
    SELECT
        array_agg(t.name) AS name,
        array_agg(t.age) AS age
    FROM t
) tmp;

-- Result: row_to_json
-------------------------
 {"name":["홍길동","영이","철이","김태균","김태균"],"age":[20,22,30,25,32]}

특정 열을 기준, 지정한 구분자로 문자열 만들기

SQL Fiddle: 쿼리문 테스트 페이지

-- Query 1 (PostgreSQL 9.0)
SELECT name, string_agg(age::text, ',') FROM t GROUP BY name;

-- Result
  name  | string_agg
--------+------------
 홍길동 | 20
 영이   | 22
 김태균 | 25,32
 철이   | 30
(4 rows)

-- Query 2 (PostgreSQL 8.4)
SELECT name, array_to_string(array_agg(age), ',') FROM t GROUP BY name;

-- Result
  name  | array_to_string
--------+-----------------
 홍길동 | 20
 영이   | 22
 김태균 | 25,32
 철이   | 30
(4 rows)

마무리하며

위 예제는 맛보기 일뿐 PGSQL은 더 많은 문자열 관련 함수를 지원하고 있으니 "함께보기" 링크 사이트를 방문을 권장합니다.