PostgreSQL 고급 데이터 타입

소개

이 실습에서는 PostgreSQL의 고급 데이터 타입인 JSON/JSONB, 배열(Array), UUID에 중점을 두고 살펴봅니다. 이러한 데이터 타입 내에서 데이터를 저장, 조회 및 조작하는 방법을 배웁니다.

실습은 JSON 및 JSONB 데이터를 저장하고 조회하는 방법으로 시작합니다. 여기에는 JSONB 컬럼이 포함된 테이블 생성, JSON 데이터 삽입, 그리고 -> 및 ->> 연산자를 사용하여 특정 값을 추출하는 방법이 포함됩니다. 그 후 배열 컬럼과 UUID에 대해 학습합니다.

JSON 및 JSONB 데이터 저장 및 조회

이 단계에서는 PostgreSQL에서 JSON 및 JSONB 데이터를 저장하고 조회하는 방법을 배웁니다. PostgreSQL은 JSON 데이터를 저장하기 위해 JSON과 JSONB라는 두 가지 데이터 타입을 제공합니다. JSON 데이터 타입은 JSON 입력 텍스트의 정확한 복사본을 저장하는 반면, JSONB 데이터 타입은 JSON 데이터를 분해된 바이너리 형식으로 저장합니다. JSONB는 조회 및 인덱싱 성능이 더 뛰어나기 때문에 일반적으로 더 선호됩니다.

먼저 PostgreSQL 셸을 열어보겠습니다. 우선 labex 데이터베이스에 접속하세요:

sudo -u postgres psql -d labex

PostgreSQL 프롬프트가 나타날 것입니다:

labex=#

이제 JSONB 데이터를 저장할 테이블을 생성해 보겠습니다:

CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);

이 SQL 명령은 products라는 테이블을 생성합니다. 이 테이블에는 id(자동 증가하는 정수 기본 키)와 data(JSON 데이터를 저장하기 위한 JSONB 컬럼)라는 두 개의 컬럼이 있습니다.

다음과 유사한 출력이 표시되어야 합니다:

CREATE TABLE

이제 products 테이블에 데이터를 삽입해 보겠습니다:

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');

이 명령들은 products 테이블에 두 개의 행을 삽입합니다. 각 행에는 제품 정보가 담긴 JSON 객체가 포함되어 있습니다.

각 삽입 작업에 대해 다음과 유사한 출력이 표시되어야 합니다:

INSERT 0 1

JSON 데이터를 조회하려면 -> 및 ->> 연산자를 사용할 수 있습니다. -> 연산자는 JSON 객체를 반환하고, ->> 연산자는 JSON 값을 텍스트로 반환합니다.

예를 들어, 첫 번째 제품의 이름을 가져오려면 다음 쿼리를 사용할 수 있습니다:

SELECT data ->> 'name' FROM products WHERE id = 1;

이 명령은 id가 1인 products 테이블의 data 컬럼에서 name 키와 관련된 값을 선택합니다. ->> 연산자는 결과가 텍스트로 반환되도록 보장합니다.

다음과 유사한 출력이 표시되어야 합니다:

  ?column?
----------
 Laptop
(1 row)

중첩된 JSON 객체도 조회할 수 있습니다. 예를 들어, 첫 번째 제품의 첫 번째 기능을 가져오려면 다음 쿼리를 사용할 수 있습니다:

SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;

이 명령은 먼저 data 컬럼에서 features 배열을 선택한 다음, 배열의 인덱스 0에 있는 요소를 선택합니다. ->> 연산자는 결과가 텍스트로 반환되도록 보장합니다.

다음과 유사한 출력이 표시되어야 합니다:

  ?column?
----------
 16GB RAM
(1 row)

또한 @> 연산자를 사용하여 JSON 객체에 특정 키-값 쌍이 포함되어 있는지 확인할 수 있습니다. 예를 들어, 가격이 75인 모든 제품을 찾으려면 다음 쿼리를 사용할 수 있습니다:

SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';

이 명령은 data 컬럼에 price 키와 75라는 값을 가진 JSON 객체가 포함된 products 테이블의 모든 행에서 이름을 선택합니다.

다음과 유사한 출력이 표시되어야 합니다:

  ?column?
----------
 Keyboard
(1 row)

좋습니다! JSONB 데이터가 포함된 첫 번째 테이블을 성공적으로 생성하고 조회하는 방법을 배웠습니다. 다음 단계에서는 이 테이블을 계속 사용하여 더 고급 기능을 추가해 보겠습니다.

기존 테이블에 배열 컬럼 추가

이 단계에서는 PostgreSQL에서 배열 컬럼을 추가하고 조작하는 방법을 배웁니다. 태그를 저장하기 위한 배열 컬럼을 추가하여 기존 products 테이블을 확장해 보겠습니다. 배열 컬럼을 사용하면 동일한 데이터 타입의 여러 값을 단일 컬럼에 저장할 수 있으며, 이는 태그, 카테고리 또는 기능과 같은 항목 목록을 저장하는 데 유용합니다.

이미 데이터베이스에 접속되어 있고 이전 단계에서 만든 products 테이블이 있으므로, 기존 테이블에 배열 컬럼을 추가해 보겠습니다:

ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];

이 명령들은 기존 products 테이블에 name(문자열)과 tags(문자열 배열)라는 두 개의 새로운 컬럼을 추가합니다. TEXT[] 데이터 타입은 tags 컬럼이 텍스트 값의 배열임을 지정합니다.

각 변경 명령에 대해 다음과 유사한 출력이 표시되어야 합니다:

ALTER TABLE

이제 기존 데이터를 업데이트하고 새로운 컬럼에 데이터를 삽입해 보겠습니다:

UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;

이 명령들은 JSONB 데이터에서 추출한 이름과 태그 정보, 그리고 새로운 배열 값을 사용하여 기존 제품을 업데이트합니다.

각 업데이트 작업에 대해 다음과 유사한 출력이 표시되어야 합니다:

UPDATE 1

배열 데이터를 조회하려면 배열 인덱싱을 사용할 수 있습니다. PostgreSQL의 배열 인덱스는 1부터 시작합니다.

예를 들어, 첫 번째 제품의 첫 번째 태그를 가져오려면 다음 쿼리를 사용할 수 있습니다:

SELECT tags[1] FROM products WHERE id = 1;

이 명령은 id가 1인 products 테이블의 tags 배열에서 인덱스 1에 있는 요소를 선택합니다.

다음과 유사한 출력이 표시되어야 합니다:

   tags
-----------
 electronics
(1 row)

UNNEST 함수를 사용하여 배열을 행 집합으로 확장할 수도 있습니다.

예를 들어, 모든 제품의 모든 태그를 가져오려면 다음 쿼리를 사용할 수 있습니다:

SELECT name, UNNEST(tags) AS tag FROM products;

이 명령은 name과 tags 배열의 각 개별 tag를 선택하여 각 태그마다 새로운 행을 생성합니다.

다음과 유사한 출력이 표시되어야 합니다:

  name   |     tag
---------+-------------
 Laptop  | electronics
 Laptop  | computers
 Laptop  | portable
 Keyboard| electronics
 Keyboard| accessories
 Keyboard| input
(6 rows)

@> 연산자를 사용하여 배열에 특정 값이 포함되어 있는지 확인할 수 있습니다.

예를 들어, 'electronics' 태그가 있는 모든 제품을 찾으려면 다음 쿼리를 사용할 수 있습니다:

SELECT name FROM products WHERE tags @> ARRAY['electronics'];

이 명령은 tags 배열에 'electronics' 값이 포함된 products 테이블의 모든 행에서 이름을 선택합니다.

다음과 유사한 출력이 표시되어야 합니다:

  name
----------
 Laptop
 Keyboard
(2 rows)

&& 연산자를 사용하여 두 배열에 공통 요소가 있는지 확인할 수도 있습니다.

예를 들어, 첫 번째 제품과 태그를 공유하는 모든 제품을 찾으려면 다음 쿼리를 사용할 수 있습니다:

SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;

이 명령은 첫 번째 제품(별칭 p1)의 태그와 최소한 하나 이상의 태그를 공유하는 products 테이블(별칭 p2)의 모든 행에서 이름을 선택하며, 첫 번째 제품 자체는 제외합니다.

다음과 유사한 출력이 표시되어야 합니다:

  name
----------
 Keyboard
(1 row)

완벽합니다! 기존 테이블에 배열 컬럼을 성공적으로 추가하고 배열을 다루는 방법을 배웠습니다. 이제 products 테이블에는 JSONB와 배열 데이터 타입이 모두 포함되어 다음 단계를 진행할 준비가 되었습니다.

UUID 컬럼 추가 및 UUID 생성 학습

이 단계에서는 PostgreSQL에서 UUID(Universally Unique Identifiers)를 생성하고 사용하는 방법을 배웁니다. UUID는 공간과 시간 전반에 걸쳐 고유하도록 설계된 128비트 숫자입니다. 데이터베이스 테이블에서 서로 다른 소스의 데이터를 병합할 때 충돌을 방지하기 위한 고유 식별자로 자주 사용됩니다.

기존 세션과 테이블을 계속 사용하므로, UUID 기능을 시연하기 위해 현재 products 테이블에 UUID 컬럼을 추가하겠습니다.

먼저 UUID 생성 함수를 제공하는 UUID 확장을 활성화합니다:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

이 명령은 uuid-ossp 확장이 아직 존재하지 않는 경우 생성합니다.

다음과 유사한 출력이 표시되어야 합니다:

CREATE EXTENSION

이제 기존 products 테이블에 UUID 컬럼을 추가합니다:

ALTER TABLE products ADD COLUMN uuid_id UUID;

이 명령은 기존 products 테이블에 새로운 UUID 컬럼을 추가합니다.

다음과 유사한 출력이 표시되어야 합니다:

ALTER TABLE

이제 uuid_generate_v4() 함수를 사용하여 기존 행을 UUID 값으로 업데이트할 수 있습니다:

UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;

이 명령들은 고유한 UUID 값으로 기존 제품을 업데이트합니다. uuid_generate_v4() 함수는 각 행에 대해 새로운 UUID를 생성합니다.

각 업데이트 작업에 대해 다음과 유사한 출력이 표시되어야 합니다:

UPDATE 1

UUID를 사용하여 데이터를 조회하려면 WHERE 절에 UUID 값을 사용할 수 있습니다. UUID는 무작위로 생성되므로, 먼저 현재 데이터를 확인해 보겠습니다:

SELECT id, name, uuid_id FROM products;

이 명령은 products 테이블의 모든 컬럼을 선택하여 생성된 UUID를 다른 데이터와 함께 확인합니다.

다음과 유사한 출력이 표시되어야 합니다(사용자의 UUID는 다를 수 있습니다):

 id |   name   |               uuid_id
----+----------+--------------------------------------
  1 | Laptop   | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
  2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)

이제 다음 명령에서 <YOUR_UUID_HERE>를 실제 조회한 UUID로 바꾸어 사용해 보세요:

SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';

이 명령은 uuid_id가 지정된 UUID와 일치하는 products 테이블에서 name을 선택합니다.

다음과 유사한 출력이 표시되어야 합니다(선택한 UUID에 따라 다름):

  name
----------
 Laptop
(1 row)

다음 단계에서도 products 테이블과 uuid-ossp 확장을 계속 사용하므로 그대로 유지하세요.

고급 타입에서 데이터 추출

마지막 단계에서는 이 실습 전반에 걸쳐 products 테이블에 추가한 모든 고급 데이터 타입에서 데이터를 추출하는 연습을 합니다. 이제 테이블에는 JSONB, 배열, UUID 컬럼이 포함되어 있어 PostgreSQL의 고급 데이터 타입을 포괄적으로 보여줍니다.

기존 세션과 테이블을 계속 사용하므로, 이전 단계에서 구축해 온 데이터를 즉시 활용할 수 있습니다.

먼저, 추출 예제를 더 포괄적으로 만들기 위해 추가적인 JSONB 데이터를 추가해 보겠습니다:

UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;

이 명령들은 중첩된 객체와 추가 필드를 포함하여 더 자세한 정보로 기존 JSONB 데이터를 업데이트합니다.

각 업데이트 작업에 대해 다음과 유사한 출력이 표시되어야 합니다:

UPDATE 1

이제 모든 고급 데이터 타입에서 데이터를 추출하는 연습을 해보겠습니다. JSONB data 컬럼에서 데이터를 추출하려면 -> 및 ->> 연산자를 사용할 수 있습니다. 예를 들어, 업데이트된 데이터에서 브랜드를 추출하려면 다음과 같이 합니다:

SELECT data ->> 'brand' FROM products WHERE id = 1;

이 명령은 data 컬럼에서 brand 키와 관련된 값을 가져옵니다.

다음과 유사한 출력이 표시되어야 합니다:

 ?column?
----------
 Dell
(1 row)

JSONB 컬럼에서 중첩된 데이터를 추출하려면 -> 및 ->> 연산자를 연결할 수 있습니다. 예를 들어, RAM 사양을 추출하려면 다음과 같이 합니다:

SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;

이 명령은 specs 객체 내의 ram 키와 관련된 값을 가져옵니다.

다음과 유사한 출력이 표시되어야 합니다:

 ?column?
----------
 16GB
(1 row)

tags(배열) 컬럼에서 데이터를 추출하려면 2단계에서 배운 배열 인덱싱을 사용할 수 있습니다. 예를 들어, 첫 번째 제품의 첫 번째 태그를 추출하려면 다음과 같이 합니다:

SELECT tags[1] FROM products WHERE id = 1;

이 명령은 id가 1인 products 테이블의 tags 배열에서 인덱스 1에 있는 요소를 가져옵니다.

다음과 유사한 출력이 표시되어야 합니다:

   tags
-----------
 electronics
(1 row)

이제 JSONB, 배열, UUID 등 모든 고급 데이터 타입에서 데이터를 추출하는 포괄적인 쿼리를 작성해 보겠습니다:

SELECT
    id,
    name,
    data ->> 'brand' AS brand,
    data -> 'specs' ->> 'ram' AS ram,
    tags[1] AS first_tag,
    uuid_id
FROM products;

이 명령은 우리가 작업한 모든 고급 데이터 타입에서 데이터를 가져옵니다: 정수 id, name, JSONB data 컬럼의 brand, 중첩된 ram 사양, tags 배열의 첫 번째 요소, 그리고 uuid_id입니다.

다음과 유사한 출력이 표시되어야 합니다:

   name   |  ?column?  |   tags
----------+------------+-----------
 Laptop   | Dell       | electronics
 Keyboard | Logitech   | electronics
(2 rows)

훌륭합니다! 단일 테이블에서 세 가지 고급 PostgreSQL 데이터 타입을 모두 성공적으로 다루었습니다. 이 포괄적인 예제는 JSONB, 배열, UUID를 함께 사용하여 유연하고 강력한 데이터베이스 스키마를 만드는 방법을 보여줍니다.

모든 단계를 완료했으면 다음을 입력하여 PostgreSQL 셸을 종료할 수 있습니다:

\q

PostgreSQL의 고급 데이터 타입을 추가로 실험하기 위해 테이블을 유지할 수도 있습니다.

요약

이 실습에서는 PostgreSQL의 고급 데이터 타입을 실제로 보여주는 포괄적인 products 테이블을 단계별로 구축하고 작업했습니다. JSONB 컬럼이 있는 테이블을 생성하는 것으로 시작하여 -> 및 ->>와 같은 연산자를 사용하여 JSON 데이터를 저장하고 조회하는 방법을 배웠습니다.

그런 다음 배열 컬럼을 추가하여 테이블을 확장하고, 단일 컬럼에 여러 값을 저장하는 방법과 배열 인덱싱 및 UNNEST와 같은 함수를 사용하여 조회하는 방법을 배웠습니다. 이어서 uuid-ossp 확장을 활성화하고 UUID 컬럼을 추가하여 고유 식별자를 생성함으로써 UUID 기능을 추가했습니다.

마지막으로, JSONB, 배열, UUID라는 세 가지 고급 데이터 타입을 모두 결합한 정교한 쿼리를 통해 포괄적인 데이터 추출 기술을 연습했습니다. 이 단계별 접근 방식은 이러한 데이터 타입들이 실제 데이터베이스 스키마에서 어떻게 함께 작동하여 현대적인 애플리케이션에 유연성과 강력한 조회 기능을 제공하는지 보여주었습니다.