PostgreSQL「syntax error in tsquery」の原因と対処

-- 1) 連続演算子：左辺/右辺がない
SELECT to_tsquery('english', 'cat & & dog');  -- NG: syntax error in tsquery

-- 2) ダブルクオートを to_tsquery に渡す（これは websearch 用の記法）
SELECT to_tsquery('english', '"cat dog"');    -- NG

-- 3) C++ などの記号語をそのまま
SELECT to_tsquery('english', 'c++');          -- NG

-- 4) プレフィックス :* の付け方が不正
SELECT to_tsquery('english', 'postgres:*');   -- 失敗する例あり（書き方注意）

-- 5) 括弧の不一致
SELECT to_tsquery('simple', '(cat | dog');    -- NG

-- もっとも安全：web 検索風の入力をそのまま受ける
SELECT websearch_to_tsquery('english', ' "cat dog" OR rabbit -mouse ');

-- 単語列なら plainto（AND 連結）
SELECT plainto_tsquery('english', 'cat dog rabbit');

-- 明示フレーズなら phraseto
SELECT phraseto_tsquery('english', 'cat dog');

-- 予約記号を空白へ、連続空白を 1 つに
-- & | ! ( ) : < > ' " は to_tsquery で問題になりやすい
SELECT regexp_replace(
         regexp_replace($$C++ (intro) & tips$$, '[&|!():<>''"]', ' ', 'g'),
         '\s+', ' ', 'g');

-- C++ のような記号語は plainto/websearch で吸収するのが無難
SELECT websearch_to_tsquery('english', '"c++"');  -- OK

-- フレーズ（to_tsquery を使うなら <-> で連結）
SELECT to_tsquery('english', 'cat<->dog');   -- 「cat」と「dog」が隣接

-- 前方一致（lexeme':* の形。lexeme は基本クオートする）
SELECT to_tsquery('simple', '''postgre'':*');   -- 'postgre':*

-- OR / AND / NOT
SELECT to_tsquery('english', 'cat | dog & !mouse');

-- ユーザー入力を websearch→（失敗時）plain にフォールバックし、空は NULL
CREATE OR REPLACE FUNCTION safe_tsquery(conf regconfig, q text)
RETURNS tsquery
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE t tsquery;
BEGIN
  IF q IS NULL OR btrim(q) = '' THEN
    RETURN NULL;
  END IF;
  BEGIN
    t := websearch_to_tsquery(conf, q);
  EXCEPTION WHEN syntax_error THEN
    t := plainto_tsquery(conf, q);
  END;
  IF t::text = '' THEN
    RETURN NULL;
  END IF;
  RETURN t;
END$$;

-- 利用例
SELECT to_tsvector('english', title || ' ' || body) @@ safe_tsquery('english', :q);

-- トークナイズ/辞書適用の結果を確認
SELECT * FROM ts_debug('english', 'C++ tips for the cat and the dog');

-- tsquery の文字列表現を確認（演算子の付き方を可視化）
SELECT to_tsquery('english', 'cat & dog | !mouse')::text;

-- 1) tsvector を作って GIN インデックス
CREATE INDEX idx_docs_fts
ON docs USING GIN (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')));

-- 2) 検索は @@ 演算子で
SELECT id, title
FROM docs
WHERE to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')) @@
      websearch_to_tsquery('english', :q)
ORDER BY ts_rank_cd(to_tsvector('english', title || ' ' || body),
                    websearch_to_tsquery('english', :q)) DESC
LIMIT 50;

-- NG: ユーザー入力をそのまま to_tsquery
SELECT to_tsquery('english', :q);                 -- ❌

-- OK: websearch_to_tsquery / plainto_tsquery
SELECT websearch_to_tsquery('english', :q);       -- ✅

-- NG: "ダブルクオート" を to_tsquery に渡す
SELECT to_tsquery('english', '"cat dog"');        -- ❌

-- OK: フレーズは phraseto_tsquery か websearch_to_tsquery
SELECT phraseto_tsquery('english', 'cat dog');    -- ✅

-- NG: プレフィックスの書式
SELECT to_tsquery('simple', 'postgres:*');        -- ❌（lexeme の指定が不正）

-- OK: lexeme':* 形式（シングルクオート）
SELECT to_tsquery('simple', '''postgres'':*');    -- ✅  'postgres':*

-- Bash: ユーザー入力らしき文字列から危険記号を空白化して渡す（最終手段）
echo "$q" | sed 's/[&|!():"'\'']\+//g' | tr -s ' ' | psql -v q -c \
  "SELECT websearch_to_tsquery('english', :'q');"

-- SQL: 記号を取り除いた上で plainto に渡す（安全優先）
WITH cleaned AS (
  SELECT regexp_replace(:q, '[&|!():"'\'']', ' ', 'g') AS s
)
SELECT plainto_tsquery('english', s) FROM cleaned;