이 랩에서는 PostgreSQL 의 고급 데이터 유형, 특히 JSON/JSONB, 배열 및 UUID 를 탐색합니다. 이러한 유형 내에서 데이터를 저장, 쿼리 및 조작하는 방법을 배우게 됩니다.
랩은 JSON 및 JSONB 데이터 저장 및 쿼리 방법을 시연하는 것으로 시작합니다. 여기에는 JSONB 열이 있는 테이블 생성, JSON 데이터 삽입, -> 및 ->>와 같은 연산자를 사용하여 특정 값 추출 등이 포함됩니다. 그런 다음 배열 열 및 UUID 에 대해 배우게 됩니다.
이 단계에서는 PostgreSQL 에서 JSON 및 JSONB 데이터를 저장하고 쿼리하는 방법을 배웁니다. PostgreSQL 은 JSON 데이터를 저장하기 위한 두 가지 데이터 유형인 JSON과 JSONB를 제공합니다. JSON 데이터 유형은 JSON 입력 텍스트의 정확한 복사본을 저장하는 반면, JSONB 데이터 유형은 분해된 이진 형식으로 JSON 데이터를 저장합니다. JSONB는 쿼리 및 인덱싱 성능이 더 뛰어나기 때문에 일반적으로 선호됩니다.
먼저 PostgreSQL 셸을 열어 보겠습니다. 먼저 labex 데이터베이스에 연결합니다.
sudo -u postgres psql -d labexPostgreSQL 프롬프트가 표시되어야 합니다.
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 1JSON 데이터를 쿼리하려면 -> 및 ->> 연산자를 사용할 수 있습니다. -> 연산자는 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}';이 명령은 products 테이블에서 data 열이 price 키와 75 의 값을 포함하는 모든 행의 이름을 선택합니다.
다음과 유사한 출력이 표시되어야 합니다.
?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 명령에 대해 다음과 유사한 출력이 표시되어야 합니다.
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;이 명령은 tags 배열에서 name과 각 개별 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'];이 명령은 products 테이블에서 tags 배열에 'electronics' 값이 포함된 모든 행의 이름을 선택합니다.
다음과 유사한 출력이 표시되어야 합니다.
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 및 배열 데이터 유형이 모두 포함되어 다음 단계를 진행할 준비가 되었습니다.
이 단계에서는 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 1UUID 를 사용하여 데이터를 쿼리하려면 WHERE 절에 UUID 값을 사용할 수 있습니다. UUID 는 무작위로 생성되므로 먼저 현재 데이터를 살펴보겠습니다.
SELECT id, name, uuid_id FROM products;이 명령은 products 테이블에서 모든 열을 선택하여 다른 데이터와 함께 생성된 UUID 를 확인합니다.
다음과 유사한 출력이 표시되어야 합니다 (UUID 는 다를 수 있습니다).
id
------------------------------------
a1b2c3d4-e5f6-7890-1234-567890abcdef
(1 row)이제 해당 UUID 를 다음 명령에서 사용하고 <YOUR_UUID_HERE>를 검색한 실제 UUID 로 바꿉니다.
SELECT name FROM products WHERE id = '<YOUR_UUID_HERE>';이 명령은 id가 지정된 UUID 와 일치하는 products 테이블에서 name을 선택합니다.
다음과 유사한 출력이 표시되어야 합니다 (선택한 UUID 에 따라 다름).
name
----------
Laptop
(1 row)마지막으로 생성한 테이블과 확장을 정리해 보겠습니다.
DROP TABLE products;
DROP EXTENSION "uuid-ossp";각 drop 명령에 대해 다음과 유사한 출력이 표시되어야 합니다.
DROP TABLE
DROP EXTENSION이 마지막 단계에서는 이 실습 전반에 걸쳐 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 의 세 가지 고급 데이터 유형을 모두 결합한 포괄적인 데이터 추출 기술을 정교한 쿼리에서 연습했습니다. 이 단계별 접근 방식은 이러한 데이터 유형이 실제 데이터베이스 스키마에서 함께 작동하여 최신 애플리케이션에 대한 유연성과 강력한 쿼리 기능을 제공하는 방법을 보여주었습니다.
