PostgreSQL演算子完全マスター！基本から特殊演算子まで実例で理解する全パターン

「LIKEとILIKEの違いって？」 「配列演算子の@>って何？」 「正規表現やJSONB演算子がよく分からない…」

PostgreSQLには70種類以上の演算子があります。

基本的な算術演算子だけでなく、 配列、JSON、全文検索など 他のDBにはない特殊な演算子が満載！

この記事を読めば、 複雑なクエリも演算子を使って シンプルに書けるようになります！

---

基本演算子

算術演算子

-- 基本的な算術演算
SELECT 
    10 + 5 as addition,           -- 15（加算）
    10 - 5 as subtraction,        -- 5（減算）
    10 * 5 as multiplication,     -- 50（乗算）
    10 / 4 as division,            -- 2（整数除算）
    10.0 / 4 as float_division,   -- 2.5（小数除算）
    10 % 3 as modulo,             -- 1（剰余）
    10 ^ 2 as exponent,           -- 100（べき乗）
    |/ 25 as square_root,         -- 5（平方根）
    ||/ 27 as cube_root,          -- 3（立方根）
    @ -5 as absolute,             -- 5（絶対値）
    5! as factorial;              -- 120（階乗）

-- 実践例：消費税計算
SELECT 
    product_name,
    price,
    price * 1.10 as price_with_tax,
    ROUND(price * 0.10) as tax_amount
FROM products;

比較演算子

-- 基本的な比較
SELECT * FROM employees WHERE
    salary = 500000           -- 等しい
    OR salary != 500000       -- 等しくない（<>も可）
    OR salary < 400000        -- より小さい
    OR salary > 600000        -- より大きい
    OR salary <= 500000       -- 以下
    OR salary >= 500000;      -- 以上

-- BETWEEN演算子（範囲指定）
SELECT * FROM products 
WHERE price BETWEEN 1000 AND 5000;
-- 同じ意味：price >= 1000 AND price <= 5000

-- IN演算子（複数値の一致）
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

-- IS NULL/IS NOT NULL
SELECT * FROM users 
WHERE deleted_at IS NULL;  -- NULLチェック

-- DISTINCT FROM（NULL安全な比較）
SELECT 
    NULL = NULL,                    -- NULL（不明）
    NULL IS NOT DISTINCT FROM NULL; -- true（同じと判定）

論理演算子

-- AND, OR, NOT
SELECT * FROM products 
WHERE 
    (category = 'Electronics' AND price < 50000)
    OR 
    (category = 'Books' AND NOT discontinued)
    AND 
    stock > 0;

-- 優先順位に注意（括弧を使う）
SELECT * FROM users 
WHERE 
    status = 'active' 
    AND (role = 'admin' OR role = 'manager');

---

文字列演算子

連結と操作

-- 文字列連結
SELECT 
    'Hello' || ' ' || 'World' as concatenation,     -- Hello World
    'Post' || 'greSQL' as name,                     -- PostgreSQL
    CONCAT('A', 'B', 'C') as concat_function,       -- ABC
    CONCAT_WS('-', '2024', '01', '15') as with_sep; -- 2024-01-15

-- 文字列の長さと位置
SELECT 
    LENGTH('PostgreSQL') as length,                 -- 10
    CHAR_LENGTH('日本語') as char_len,              -- 3
    POSITION('SQL' IN 'PostgreSQL') as position,    -- 8
    STRPOS('PostgreSQL', 'SQL') as strpos;         -- 8

パターンマッチング

-- LIKE演算子（大文字小文字を区別）
SELECT * FROM users WHERE
    email LIKE '%@gmail.com'        -- gmailアドレス
    OR name LIKE 'John%'            -- Johnで始まる
    OR phone LIKE '090-____-____';  -- 090で始まる電話番号

-- ILIKE演算子（大文字小文字を区別しない）
SELECT * FROM products WHERE
    name ILIKE '%iphone%';          -- iPhone, IPHONE, iphoneすべてマッチ

-- SIMILAR TO（SQL標準の正規表現）
SELECT * FROM files WHERE
    filename SIMILAR TO '%(jpg|png|gif)';  -- 画像ファイル

-- 正規表現演算子
SELECT * FROM logs WHERE
    message ~ '^ERROR:'                    -- ERRORで始まる（大文字小文字区別）
    OR message ~* '^error:'                -- errorで始まる（区別しない）
    OR message !~ '^\d+'                   -- 数字で始まらない
    OR message !~* '^warning:';            -- warningで始まらない（区別しない）

-- 正規表現での抽出
SELECT 
    regexp_match('Email: test@example.com', '([a-zA-Z0-9._%+-]+)@([a-zA-Z0-9.-]+)') as email_parts,
    regexp_matches('Call 090-1234-5678 or 03-1234-5678', '\d{2,4}-\d{4}-\d{4}', 'g') as phone_numbers;

---

配列演算子

配列の操作

-- 配列の作成と基本操作
SELECT 
    ARRAY[1,2,3] as int_array,
    ARRAY['a','b','c'] as text_array,
    '{1,2,3}'::int[] as literal_array;

-- 配列演算子
SELECT 
    ARRAY[1,2,3] || ARRAY[4,5] as concatenation,        -- {1,2,3,4,5}
    ARRAY[1,2,3] || 4 as append_element,                -- {1,2,3,4}
    ARRAY[1,2,3] @> ARRAY[1,2] as contains,            -- true（含む）
    ARRAY[1,2] <@ ARRAY[1,2,3] as is_contained,        -- true（含まれる）
    ARRAY[1,2,3] && ARRAY[2,3,4] as overlaps,          -- true（重複あり）
    ARRAY[1,2,3] = ARRAY[1,2,3] as equals;             -- true（等しい）

-- 配列要素へのアクセス
SELECT 
    (ARRAY[10,20,30])[1] as first_element,              -- 10（1始まり）
    (ARRAY[10,20,30])[2:3] as slice;                    -- {20,30}

-- 実践例：タグ検索
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

-- タグを含む記事を検索
SELECT * FROM articles 
WHERE tags @> ARRAY['PostgreSQL', 'Database'];  -- 両方のタグを含む

SELECT * FROM articles 
WHERE tags && ARRAY['PostgreSQL', 'MySQL'];     -- どちらかのタグを含む

---

JSON/JSONB演算子

JSON操作

-- JSON/JSONB演算子
SELECT 
    '{"name":"John","age":30}'::jsonb -> 'name' as extract_text,        -- "John"（JSON形式）
    '{"name":"John","age":30}'::jsonb ->> 'name' as extract_string,     -- John（テキスト）
    '{"user":{"name":"John"}}'::jsonb -> 'user' -> 'name' as nested,    -- "John"
    '{"user":{"name":"John"}}'::jsonb #> '{user,name}' as path_extract, -- "John"
    '{"user":{"name":"John"}}'::jsonb #>> '{user,name}' as path_text;   -- John

-- JSONB包含演算子
SELECT 
    '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb as contains,            -- true
    '{"a":1}'::jsonb <@ '{"a":1,"b":2}'::jsonb as contained_in,        -- true
    '{"a":1,"b":2}'::jsonb ? 'a' as has_key,                           -- true
    '{"a":1,"b":2}'::jsonb ?& array['a','b'] as has_all_keys,         -- true
    '{"a":1,"b":2}'::jsonb ?| array['a','c'] as has_any_keys;         -- true

-- 実践例：JSONBカラムの検索
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

-- 特定の属性を持つユーザーを検索
SELECT * FROM users 
WHERE profile @> '{"country": "Japan"}';

-- 年齢が30歳以上のユーザー
SELECT * FROM users 
WHERE (profile ->> 'age')::int >= 30;

-- 特定のキーを持つユーザー
SELECT * FROM users 
WHERE profile ? 'email';

JSONB更新演算子

-- JSONB連結と削除
SELECT 
    '{"a":1}'::jsonb || '{"b":2}'::jsonb as concatenate,               -- {"a":1,"b":2}
    '{"a":1,"b":2}'::jsonb - 'a' as remove_key,                        -- {"b":2}
    '{"a":1,"b":2,"c":3}'::jsonb - '{a,b}'::text[] as remove_keys,    -- {"c":3}
    '["a","b","c"]'::jsonb - 1 as remove_array_element;                -- ["a","c"]

-- JSONBパス削除
SELECT 
    '{"a":{"b":{"c":1}}}'::jsonb #- '{a,b}' as remove_path;           -- {"a":{}}

---

範囲演算子

範囲型の操作

-- 範囲の作成
SELECT 
    '[1,10]'::int4range as closed_range,        -- 1から10（両端含む）
    '[1,10)'::int4range as half_open,           -- 1から9（右端含まない）
    '(1,10]'::int4range as half_open2,          -- 2から10（左端含まない）
    '(1,10)'::int4range as open_range;          -- 2から9（両端含まない）

-- 範囲演算子
SELECT 
    int4range(1, 10) @> 5 as contains,          -- true（5を含む）
    int4range(1, 10) @> int4range(2, 5) as contains_range,  -- true
    int4range(1, 5) <@ int4range(1, 10) as contained_in,    -- true
    int4range(1, 5) && int4range(3, 10) as overlaps,        -- true（重複）
    int4range(1, 5) << int4range(10, 20) as left_of,        -- true（左側）
    int4range(10, 20) >> int4range(1, 5) as right_of;       -- true（右側）

-- 実践例：予約システム
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT,
    reserved_period tstzrange  -- タイムスタンプ範囲
);

-- 時間帯が重複する予約を検索
SELECT * FROM reservations 
WHERE reserved_period && tstzrange('2024-01-15 10:00', '2024-01-15 12:00');

---

幾何学演算子

図形の操作

-- 点と線の演算
SELECT 
    point(1,2) + point(3,4) as point_add,              -- (4,6)
    point(10,10) - point(3,4) as point_subtract,       -- (7,6)
    point(2,3) * 2.0 as point_scale,                   -- (4,6)
    point(10,10) <-> point(0,0) as distance,           -- 14.142（距離）
    box('(0,0)','(10,10)') @> point(5,5) as contains,  -- true（含む）
    circle('(0,0)',5) @> point(3,4) as in_circle;      -- true（円内）

-- 実践例：地理情報
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    position POINT
);

-- 特定地点から10km以内の場所を検索
SELECT * FROM locations 
WHERE position <-> point(35.6762, 139.6503) < 10;

---

ビット演算子

ビット操作

-- ビット演算
SELECT 
    B'1010' & B'1100' as bit_and,         -- 1000（AND）
    B'1010' | B'1100' as bit_or,          -- 1110（OR）
    B'1010' # B'1100' as bit_xor,         -- 0110（XOR）
    ~ B'1010' as bit_not,                 -- 0101（NOT）
    B'1010' << 2 as left_shift,           -- 101000（左シフト）
    B'1010' >> 2 as right_shift;          -- 10（右シフト）

-- 整数のビット演算
SELECT 
    10 & 12 as int_and,                   -- 8
    10 | 12 as int_or,                    -- 14
    10 # 12 as int_xor,                   -- 6
    ~10 as int_not,                       -- -11
    10 << 2 as int_left_shift,            -- 40
    10 >> 2 as int_right_shift;           -- 2

-- 実践例：権限管理
CREATE TABLE user_permissions (
    user_id INT,
    permissions INT  -- ビットフラグ
);

-- 特定の権限を持つユーザーを検索
SELECT * FROM user_permissions 
WHERE permissions & 4 = 4;  -- 3ビット目（値4）の権限を持つ

---

全文検索演算子

テキスト検索

-- 全文検索演算子
SELECT 
    to_tsvector('english', 'PostgreSQL is a database') @@ to_tsquery('database') as match,  -- true
    to_tsvector('english', 'PostgreSQL database') @@ to_tsquery('PostgreSQL & database'),   -- true
    to_tsvector('english', 'PostgreSQL') @@ to_tsquery('MySQL | PostgreSQL');              -- true

-- 実践例：記事検索
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);

-- 全文検索インデックス
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 検索実行
SELECT * FROM articles 
WHERE search_vector @@ plainto_tsquery('PostgreSQL performance');

---

特殊な演算子

その他の便利な演算子

-- 型キャスト演算子
SELECT 
    '123'::INTEGER as to_int,
    123::TEXT as to_text,
    '2024-01-15'::DATE as to_date,
    CAST('123.45' AS NUMERIC) as cast_numeric;

-- 存在確認演算子
SELECT EXISTS (SELECT 1 FROM users WHERE age > 30);

-- CASE演算子（条件分岐）
SELECT 
    name,
    CASE 
        WHEN age < 20 THEN 'Teen'
        WHEN age < 30 THEN 'Twenties'
        WHEN age < 40 THEN 'Thirties'
        ELSE 'Over 40'
    END as age_group
FROM users;

-- COALESCE演算子（NULL合体）
SELECT COALESCE(phone, mobile, 'No contact') as contact FROM users;

-- GREATEST/LEAST演算子
SELECT 
    GREATEST(10, 20, 30) as maximum,     -- 30
    LEAST(10, 20, 30) as minimum;        -- 10

---

演算子の優先順位

優先順位表（高→低）

優先度	演算子	例
1	::	型キャスト
2	[]	配列要素アクセス
3	.	テーブル/カラム名
4	– (単項)	負号
5	^	べき乗
6	* / %	乗除算、剰余
7	+ –	加減算
8	その他	IS, ISNULL等
9	BETWEEN, IN, LIKE	範囲、包含
10	< > = <= >= <>	比較
11	IS, IS NOT	NULL比較
12	NOT	論理否定
13	AND	論理積
14	OR	論理和

-- 優先順位の例
SELECT 
    2 + 3 * 4,          -- 14（乗算が先）
    (2 + 3) * 4,        -- 20（括弧が最優先）
    NOT true OR false,  -- false（NOTが先）
    true OR false AND false;  -- true（ANDが先）

---

カスタム演算子の作成

独自演算子の定義

-- カスタム演算子の作成例
CREATE OR REPLACE FUNCTION text_concat(text, text)
RETURNS text AS $$
    SELECT $1 || ' + ' || $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OPERATOR +++ (
    LEFTARG = text,
    RIGHTARG = text,
    FUNCTION = text_concat
);

-- 使用例
SELECT 'Hello' +++ 'World';  -- Hello + World

---

パフォーマンスTips

インデックスと演算子

-- 演算子に応じた適切なインデックス
CREATE INDEX idx_email ON users (email);                    -- =, IN
CREATE INDEX idx_email_pattern ON users (email text_pattern_ops);  -- LIKE
CREATE INDEX idx_tags ON articles USING GIN (tags);         -- @>, <@, &&
CREATE INDEX idx_profile ON users USING GIN (profile);      -- JSONB演算子
CREATE INDEX idx_position ON locations USING GIST (position);  -- 幾何演算子

---

まとめ：演算子クイックリファレンス

基本演算子：

+ - * / %  -- 算術
= != < > <= >=  -- 比較  
AND OR NOT  -- 論理

文字列演算子：

||  -- 連結
LIKE ILIKE  -- パターン
~ ~* !~ !~*  -- 正規表現

配列演算子：

@> <@ &&  -- 包含、重複

JSONB演算子：

-> ->> #> #>>  -- 抽出
@> <@ ? ?& ?|  -- 検索

特殊演算子：

::  -- 型キャスト
IS NULL  -- NULL判定
BETWEEN IN  -- 範囲

---

演算子を使いこなせば、 複雑な条件も簡潔に表現できます！

まずは基本的な演算子から始めて、 徐々に特殊な演算子にチャレンジしていきましょう。

---

PostgreSQLの演算子で困ったことがあれば、ぜひ質問してください。実践的な使い方を一緒に考えましょう！