텍스트 검색 함수·연산자

텍스트 검색 함수·연산자 (Full Text Search)

"이 문서 목록에서 조건에 맞는 검색어를 찾고, 관련도 순으로 정렬하고 싶다"면 PostgreSQL의 전문(full text) 검색이 정답이에요. tsvector(검색 대상 문서)와 tsquery(검색 질의)라는 특수 타입을 다루는 연산자·함수가 이 페이지에서 소개돼요. 이 함수들은 여러 개이지만, @@ 연산자부터 시작하면 흐름이 금방 잡혀요.

출처: PostgreSQL 공식 문서 — Text Search Functions and Operators

텍스트 검색 연산자

전문 검색의 핵심 연산자는 매칭 연산자 @@이에요.

연산자 설명 예시
tsvector @@ tsquery → boolean tsvector가 tsquery와 매치하나요? (인자 순서는 바뀌어도 돼요.) to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') → t
text @@ tsquery → boolean 텍스트 문자열이 to_tsvector() 암묵 호출 후 tsquery와 매치하나요? 'fat cats ate rats' @@ to_tsquery('cat & rat') → t
tsvector || tsvector → tsvector 두 tsvector를 이어 붙여요. 둘 다 lexeme 위치를 갖고 있으면 두 번째의 위치를 조정해요. 'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector → 'a':1 'b':2,5 'c':3 'd':4
tsquery && tsquery → tsquery 두 질의를 AND해서, 두 질의 모두에 매치하는 문서를 찾아요. 'fat | rat'::tsquery && 'cat'::tsquery → ('fat' | 'rat') & 'cat'
tsquery || tsquery → tsquery 두 질의를 OR해서, 둘 중 하나에 매치하는 문서를 찾아요. 'fat | rat'::tsquery || 'cat'::tsquery → 'fat' | 'rat' | 'cat'
!! tsquery → tsquery 질의를 부정해서, 질의에 매치하지 않는 문서를 찾아요. !! 'cat'::tsquery → !'cat'
tsquery <-> tsquery → tsquery 구(phrase) 질의를 만들어, 두 질의가 연속한 lexeme에서 매치하면 매치해요. to_tsquery('fat') <-> to_tsquery('rat') → 'fat' <-> 'rat'
tsquery @> tsquery → boolean 첫 질의가 두 번째를 포함하나요? (결합 연산자는 무시하고 lexeme만 봐요.) 'cat'::tsquery @> 'cat & rat'::tsquery → f
tsquery <@ tsquery → boolean 첫 질의가 두 번째에 포함되나요? 'cat'::tsquery <@ 'cat & rat'::tsquery → t

이 전용 연산자 외에도 tsvector·tsquery 타입에는 일반 비교 연산자가 제공돼요. 텍스트 검색에 그리 유용하진 않지만, 이 타입 컬럼에 고유 인덱스를 만들 수 있게 해 줘요.

텍스트 검색 함수

tsvector와 tsquery를 만드는 핵심 함수부터 볼게요.

  • to_tsvector([config,] document) → tsvector: 텍스트를 tsvector로 변환하고 단어를 정규화해요. 결과에 위치 정보가 포함돼요. to_tsvector('english', 'The Fat Rats') → 'fat':2 'rat':3
  • to_tsquery([config,] query) → tsquery: 텍스트를 tsquery로 변환해요. 단어는 유효한 tsquery 연산자로 결합되어야 해요. to_tsquery('english', 'The & Fat & Rats') → 'fat' & 'rat'
  • plainto_tsquery([config,] query) → tsquery: 텍스트를 tsquery로 변환하되, 구두점을 무시하고 모든 비정지어(non-stopword)를 AND로 묶어요. plainto_tsquery('english', 'The Fat Rats') → 'fat' & 'rat'
  • phraseto_tsquery([config,] query) → tsquery: 위와 비슷하지만 모든 비정지어가 포함된 **구(phrase)**에 매치해요. phraseto_tsquery('english', 'The Fat Rats') → 'fat' <-> 'rat'
  • websearch_to_tsquery([config,] query) → tsquery: 웹 검색 도구에 가까운 문법을 지원해요. 따옴표로 묶은 단어 시퀀스는 구 테스트로, "or"는 OR로, 대시는 NOT으로, 그 외 구두점은 무시돼요. websearch_to_tsquery('english', '"fat rat" or cat dog') → 'fat' <-> 'rat' \| 'cat' & 'dog'
  • array_to_tsvector(text[]) → tsvector: 텍스트 문자열 배열을 tsvector로 변환해요. 문자열은 가공 없이 lexeme으로 그대로 쓰여요. array_to_tsvector('{fat,cat,rat}'::text[]) → 'cat' 'fat' 'rat'
  • get_current_ts_config() → regconfig: 현재 기본 텍스트 검색 설정의 OID를 반환해요. get_current_ts_config() → english
  • length(tsvector) → integer: tsvector의 lexeme 수를 반환해요. length('fat:2,4 cat:3 rat:5A'::tsvector) → 3
  • numnode(tsquery) → integer: tsquery의 lexeme + 연산자 수를 반환해요. numnode('(fat & rat) \| cat'::tsquery) → 5

JSON 지원도 있어요. to_tsvector는 json/jsonb 문서의 각 문자열 값을 tsvector로 변환해 문서 순서대로 이어 붙여요. 문자열 값 쌍 사이에 정지어 하나가 있는 것처럼 위치 정보를 생성해요. (jsonb 입력 시 JSON 객체 필드의 "문서 순서"는 구현에 따라 달라질 수 있다는 점에 주의하세요.)

to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::json)   → 'dog':5 'fat':2 'rat':3
to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::jsonb)  → 'dog':1 'fat':4 'rat':5

json_to_tsvector([config,] document, filter) 와 jsonb_to_tsvector는 filter로 선택한 항목만 변환해요. filter는 "string", "numeric", "boolean", "key", "all" 키워드 중 일부를 담은 jsonb 배열이거나, 그 키워드 하나를 값으로 갖는 단순 JSON 값이에요.

tsvector·tsquery 가공 함수

가공·순위·변환 함수들이에요.

  • setweight(vector, weight [, lexemes]) → tsvector: 벡터 요소(또는 지정 lexeme)에 가중치를 부여해요. setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A') → 'cat':3A 'fat':2A,4A 'rat':5A
  • strip(tsvector) → tsvector: 위치와 가중치를 제거해요. strip('fat:2,4 cat:3 rat:5A'::tsvector) → 'cat' 'fat' 'rat'
  • ts_delete(vector, lexeme\|lexemes) → tsvector: lexeme(들)을 제거해요.
  • ts_filter(vector, weights) → tsvector: 주어진 가중치를 가진 요소만 남겨요.
  • ts_headline([config,] document, query [, options]) → text: 문서 안에서 질의 매치를 축약 형태로 보여줘요. 매치 단어를 <b>로 감싼 결과가 돼요. ts_headline('The fat cat ate the rat.', 'cat') → The fat <b>cat</b> ate the rat.
  • ts_rank([weights,] vector, query [, normalization]) → real: 벡터가 질의에 얼마나 잘 매치하는지 점수를 계산해요. ts_rank(to_tsvector('raining cats and dogs'), 'cat') → 0.06079271
  • ts_rank_cd([weights,] vector, query [, normalization]) → real: 커버 밀도 알고리즘으로 점수를 계산해요. ts_rank_cd(to_tsvector('raining cats and dogs'), 'cat') → 0.1
  • ts_rewrite(query, target, substitute) / ts_rewrite(query, select) → tsquery: 질의에서 target을 substitute로 바꾸거나, SELECT로 얻은 target·substitute로 치환해요. ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo\|bar'::tsquery) → 'b' & ('foo' \| 'bar')
  • tsquery_phrase(query1, query2 [, distance]) → tsquery: 구 질의를 만들어요(연속한 lexeme 또는 지정 거리만큼 떨어진 매치). tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10) → 'fat' <10> 'cat'
  • tsvector_to_array(tsvector) → text[]: tsvector를 lexeme 배열로 변환해요. tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector) → {cat,fat,rat}
  • unnest(tsvector) → setof record (lexeme, positions, weights): tsvector를 lexeme별 행으로 펼쳐요.
  • querytree(tsquery) → text: tsquery의 인덱싱 가능한 부분을 나타내요. 결과가 비거나 T면 인덱싱 불가 질의예요. querytree('foo & ! bar'::tsquery) → 'foo'

선택적 regconfig 인자를 받는 모든 텍스트 검색 함수는, 그 인자를 생략하면 default_text_search_config가 지정한 설정을 사용해요.

디버깅 함수

일상적인 검색보다 새 텍스트 검색 설정을 개발·디버깅할 때 유용한 함수들이 따로 있어요.

  • ts_debug([config,] document): 문서의 각 토큰이 어떻게 처리되는지 정보를 반환해요. ts_debug('english', 'The Brightest supernovaes') → (asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...
  • ts_lexize(dict, token) → text[]: 사전이 token을 알면 대체 lexeme 배열, 정지어면 빈 배열, 모르는 단어면 NULL을 반환해요. ts_lexize('english_stem', 'stars') → {star}
  • ts_parse(parser, document): 지정 파서로 토큰을 추출해요. ts_parse('default', 'foo - bar') → (1,foo) ...
  • ts_token_type(parser): 파서가 인식할 수 있는 각 토큰 타입을 설명하는 테이블을 반환해요.
  • ts_stat(sqlquery [, weights]): 단일 tsvector 컬럼을 반환하는 쿼리를 실행해 각 lexeme의 통계를 반환해요.

더 알아보기 (Learn more)