UPSERT / JSON / 配列 / 全文検索

INSERT INTO users (id, email, name)
VALUES (1, 'alice@ex.com', 'Alice')
ON CONFLICT (id) DO UPDATE
  SET email = EXCLUDED.email,
      name  = EXCLUDED.name;

INSERT INTO products (sku, name, stock)
VALUES ('A1', 'Widget', 10)
ON CONFLICT (sku) DO UPDATE
  SET stock = products.stock + EXCLUDED.stock;
-- 同じ SKU なら stock を加算

INSERT INTO logs (event_id, payload)
VALUES (1, '...')
ON CONFLICT (event_id) DO NOTHING;

INSERT INTO products (sku, price)
VALUES ('A1', 100)
ON CONFLICT (sku) DO UPDATE
  SET price = EXCLUDED.price
  WHERE products.price < EXCLUDED.price;
-- 値上げのみ反映

MERGE INTO users u
USING (VALUES (1, 'alice@ex.com', 'Alice')) AS s(id, email, name)
ON u.id = s.id
WHEN MATCHED THEN UPDATE SET email = s.email, name = s.name
WHEN NOT MATCHED THEN INSERT (id, email, name) VALUES (s.id, s.email, s.name);

INSERT INTO posts (title) VALUES ('hello')
ON CONFLICT (title) DO UPDATE SET updated_at = NOW()
RETURNING id, (xmax = 0) AS inserted;
-- inserted = true なら新規、false なら更新

SELECT '{"a": 1, "b": [2, 3]}'::jsonb;
SELECT json_build_object('id', 1, 'name', 'Alice');
SELECT jsonb_build_array(1, 2, 3);

-- ->  JSON / JSONB を返す
-- ->> テキストを返す
SELECT meta->'tags' FROM posts;       -- jsonb
SELECT meta->>'title' FROM posts;     -- text

-- パス
SELECT meta#>'{address,city}' FROM users;
SELECT meta#>>'{address,city}' FROM users;

-- jsonb_path_query (SQL/JSON path)
SELECT jsonb_path_query(meta, '$.tags[*]') FROM posts;

-- 部分一致 (@> / <@)
SELECT * FROM posts WHERE meta @> '{"tags": ["js"]}';

-- キー存在
SELECT * FROM posts WHERE meta ? 'category';
SELECT * FROM posts WHERE meta ?| array['a','b'];   -- いずれか
SELECT * FROM posts WHERE meta ?& array['a','b'];   -- 全部

-- マージ（||）
UPDATE posts SET meta = meta || '{"views": 100}' WHERE id = 1;

-- パスでセット
UPDATE posts SET meta = jsonb_set(meta, '{address,city}', '"Tokyo"') WHERE id = 1;

-- 削除
UPDATE posts SET meta = meta - 'tags' WHERE id = 1;
UPDATE posts SET meta = meta #- '{address,city}' WHERE id = 1;

-- 全機能
CREATE INDEX posts_meta_gin ON posts USING GIN (meta);

-- 包含検索専用（小さい・速い）
CREATE INDEX posts_meta_path ON posts USING GIN (meta jsonb_path_ops);

SELECT
  p.id,
  jsonb_agg(c ORDER BY c.created_at) AS comments
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id;

CREATE TABLE posts (id BIGSERIAL, tags TEXT[]);

INSERT INTO posts (tags) VALUES (ARRAY['js', 'react']);
INSERT INTO posts (tags) VALUES ('{ts,nextjs}');

-- 取得
SELECT tags[1], tags[2:3], array_length(tags, 1) FROM posts;

-- 検索
SELECT * FROM posts WHERE 'js' = ANY (tags);
SELECT * FROM posts WHERE tags @> ARRAY['js', 'react'];   -- 全部含む
SELECT * FROM posts WHERE tags && ARRAY['js', 'ts'];      -- いずれか
SELECT * FROM posts WHERE array_length(tags, 1) > 2;

-- 展開
SELECT id, unnest(tags) AS tag FROM posts;

SELECT to_tsvector('simple', 'The quick brown fox');
-- 'brown':3 'fox':4 'quick':2 'the':1

SELECT to_tsquery('simple', 'fox & brown');
-- 'fox' & 'brown'

SELECT to_tsvector('simple', 'The quick brown fox') @@ to_tsquery('simple', 'fox');
-- true

ALTER TABLE posts
  ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (
    to_tsvector('simple',
      coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX posts_fts_idx ON posts USING GIN (fts);

SELECT * FROM posts
WHERE fts @@ websearch_to_tsquery('simple', 'react performance');

SELECT
  id, title,
  ts_rank(fts, websearch_to_tsquery('simple', 'react')) AS rank
FROM posts
WHERE fts @@ websearch_to_tsquery('simple', 'react')
ORDER BY rank DESC
LIMIT 20;

CREATE EXTENSION pg_trgm;

CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- 部分一致が高速
SELECT * FROM users WHERE name ILIKE '%alice%';

-- 類似度
SELECT * FROM users WHERE name % 'alise';   -- typo にも対応
SELECT name, similarity(name, 'alise') FROM users ORDER BY 2 DESC;

CREATE EXTENSION postgis;

CREATE TABLE places (
  id BIGSERIAL,
  name TEXT,
  geom GEOGRAPHY(POINT, 4326)
);

INSERT INTO places (name, geom)
VALUES ('Tokyo Station', ST_MakePoint(139.766, 35.681));

CREATE INDEX places_geom_idx ON places USING GIST (geom);

-- 1km 以内
SELECT * FROM places
WHERE ST_DWithin(geom, ST_MakePoint(139.7, 35.7)::geography, 1000);

-- 距離順
SELECT name, ST_Distance(geom, ST_MakePoint(139.7, 35.7)::geography) AS m
FROM places
ORDER BY geom <-> ST_MakePoint(139.7, 35.7)::geography
LIMIT 10;

CREATE EXTENSION vector;

CREATE TABLE embeddings (
  id BIGSERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

CREATE INDEX embeddings_idx
  ON embeddings USING HNSW (embedding vector_cosine_ops);

-- 類似検索
SELECT content, 1 - (embedding <=> $1) AS similarity
FROM embeddings
ORDER BY embedding <=> $1
LIMIT 5;

SELECT gen_random_uuid();   -- pgcrypto
SELECT uuid_generate_v4();  -- uuid-ossp

-- v7（時系列含む、Postgres 18+）
SELECT uuidv7();

CREATE TABLE bookings (
  id BIGSERIAL,
  room_id BIGINT,
  during tstzrange NOT NULL,

  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
-- 同じ部屋で時間が重なる予約を DB が拒否

SELECT day::date FROM generate_series(
  '2026-01-01'::date,
  '2026-12-31'::date,
  '1 day'::interval
) AS day;

CREATE MATERIALIZED VIEW post_summary AS
SELECT
  p.id, p.title, p.user_id,
  count(c.id) AS comment_count,
  max(c.created_at) AS last_comment_at
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id;

CREATE UNIQUE INDEX post_summary_idx ON post_summary (id);

-- 定期更新
REFRESH MATERIALIZED VIEW CONCURRENTLY post_summary;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts WHERE meta @> '{"tag":"js"}';

-- (a, b) を辞書順に比較
SELECT * FROM events
WHERE (created_at, id) > ('2026-01-01', 100)
ORDER BY created_at, id
LIMIT 20;
-- カーソル式ページングの安全な書き方