CREATE TABLE config_json (id SERIAL PRIMARY KEY, data JSON);
CREATE TABLE config_jsonb (id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO config_json  VALUES (1, '{"b": 1, "a": 2, "a": 3}');
INSERT INTO config_jsonb VALUES (1, '{"b": 1, "a": 2, "a": 3}');

SELECT data FROM config_json;
SELECT data FROM config_jsonb;

실행 완료 또는 조회 결과가 표시됩니다.

CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    metadata JSONB
);

INSERT INTO products (name, metadata) VALUES
('노트북', '{"brand": "Samsung", "specs": {"ram": 16, "ssd": 512}, "tags": ["laptop", "work"]}'),
('마우스', '{"brand": "Logitech", "specs": {"dpi": 1600}, "tags": ["peripheral"]}'),
('모니터', '{"brand": "LG", "specs": {"size": 27, "resolution": "4K"}, "tags": ["display", "work"]}');

SELECT metadata -> 'brand' FROM products;

SELECT name FROM products
WHERE metadata ->> 'brand' = 'Samsung';

SELECT metadata -> 'specs' ->> 'ram' AS ram_gb
FROM products
WHERE metadata -> 'specs' ? 'ram';

SELECT metadata #>> '{specs, ram}' AS ram FROM products;

SELECT name FROM products
WHERE metadata @> '{"brand": "LG"}';

SELECT name FROM products
WHERE metadata -> 'tags' @> '["work"]';

CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

CREATE INDEX idx_products_brand ON products
USING BTREE ((metadata ->> 'brand'));

CREATE INDEX idx_products_metadata_path ON products
USING GIN (metadata jsonb_path_ops);

CREATE TABLE user_settings (
    user_id  BIGINT PRIMARY KEY REFERENCES users(id),
    settings JSONB NOT NULL DEFAULT '{}'
);

UPDATE user_settings
SET settings = jsonb_set(settings, '{theme}', '"dark"')
WHERE user_id = 1;

UPDATE user_settings
SET settings = settings || '{"language": "ko", "timezone": "Asia/Seoul"}'
WHERE user_id = 1;

UPDATE user_settings
SET settings = settings - 'old_key'
WHERE user_id = 1;

SELECT
    metadata ->> 'brand' AS brand,
    COUNT(*) AS product_count,
    AVG((metadata -> 'specs' ->> 'ram')::INT) AS avg_ram
FROM products
WHERE metadata -> 'specs' ? 'ram'
GROUP BY metadata ->> 'brand';

CREATE TABLE articles (
    id     SERIAL PRIMARY KEY,
    title  VARCHAR(255),
    tags   TEXT[],
    scores INT[]
);

INSERT INTO articles (title, tags, scores) VALUES
('PostgreSQL 가이드', ARRAY['database', 'postgresql', 'sql'], ARRAY[95, 87, 92]);

SELECT title FROM articles WHERE tags @> ARRAY['postgresql'];

SELECT title FROM articles WHERE tags && ARRAY['sql', 'nosql'];

SELECT title, unnest(tags) AS tag FROM articles;

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

CREATE TABLE page_views (
    page_url       VARCHAR(500) PRIMARY KEY,
    view_count     BIGINT NOT NULL DEFAULT 0,
    last_viewed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO page_views (page_url, view_count, last_viewed_at)
VALUES ('/home', 1, NOW())
ON CONFLICT (page_url)
DO UPDATE SET
    view_count     = page_views.view_count + EXCLUDED.view_count,
    last_viewed_at = EXCLUDED.last_viewed_at;

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

WITH incoming AS (
    SELECT * FROM (VALUES
        ('SKU001', 100),
        ('SKU002', 0),
        ('SKU003', 50)
    ) AS t(sku, quantity)
)
INSERT INTO inventory (sku, quantity, updated_at)
SELECT sku, quantity, NOW() FROM incoming
ON CONFLICT (sku)
DO UPDATE SET
    quantity   = EXCLUDED.quantity,
    updated_at = EXCLUDED.updated_at
WHERE inventory.quantity != EXCLUDED.quantity;

SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');

SELECT to_tsquery('english', 'quick & fox');
SELECT to_tsquery('english', 'cat | dog');
SELECT to_tsquery('english', 'quick & !lazy');

SELECT to_tsvector('english', 'quick brown fox') @@ to_tsquery('english', 'fox');

SELECT plainto_tsquery('english', 'quick brown fox');

SELECT websearch_to_tsquery('english', '"quick fox" OR cat -lazy');

CREATE TABLE articles (
    id            SERIAL PRIMARY KEY,
    title         VARCHAR(500) NOT NULL,
    content       TEXT,
    search_vector TSVECTOR
);

CREATE OR REPLACE FUNCTION articles_search_vector_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_articles_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION articles_search_vector_update();

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

SELECT id, title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', content, query, 'MaxWords=50, MinWords=20') AS snippet
FROM articles, to_tsquery('english', 'postgresql & index') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_articles_title_trgm ON articles
USING GIN (title gin_trgm_ops);

SELECT * FROM articles WHERE title ILIKE '%데이터베이스%';

SELECT title, similarity(title, '데이터베이스 관리')
FROM articles
WHERE title % '데이터베이스'
ORDER BY similarity DESC;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    BIGINT NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE sessions_v2 (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

CREATE TYPE order_status AS ENUM (
    'pending', 'confirmed', 'shipped', 'delivered', 'cancelled'
);

CREATE TABLE orders (
    id     BIGSERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'delivered';

SELECT * FROM orders WHERE status = 'pending';

[트랜잭션 A]  BEGIN → UPDATE orders SET status='paid' WHERE id=1
   │
   ① 스냅샷 획득          (트랜잭션 시작 시 '지금 보이는 xid 범위'를 스냅샷으로 고정)
   │
   ② 가시성 판정          (행마다 xmin(생성 xid)·xmax(삭제 xid)를 스냅샷과 대조 → 내게 보이는 버전만 읽음)
   │                       → 읽는 쪽과 쓰는 쪽이 서로를 막지 않음
   │
   ③ UPDATE = 새 튜플 삽입  (기존 행을 덮어쓰지 않음! 새 버전을 추가하고 옛 버전 xmax에 내 xid 기록)
   │                       → 옛 버전은 '죽은 튜플' 예약
   │
   ④ 커밋                 (COMMIT → 새 버전이 이후 트랜잭션에 보임. 롤백하면 새 버전이 죽은 튜플이 됨)
   │
   ⑤ VACUUM 정리          (아무도 더 안 보는 죽은 튜플 공간을 autovacuum이 회수·재사용)
   ▼
[결과]  동시 읽기는 옛 버전을, 새 읽기는 새 버전을 — 락 없이 일관되게