PostgreSQL만의 함수 to_tsvector(), to_tsquery()

to_tsvector(), to_tsquery() 란?

1. to_tsvector() 함수:
   • to_tsvector() 함수는 텍스트 열을 Full-Text 검색을 위한 tsvector 데이터 형식으로 변환합니다.
   • 이 함수는 텍스트에서 단어를 추출하고, 각 단어의 위치 및 가중치를 저장합니다.
   • tsvector는 텍스트 검색 인덱스에 저장되며, 검색 쿼리를 빠르게 수행할 수 있도록 도와줍니다.
2. to_tsquery() 함수:
   • to_tsquery() 함수는 Full-Text 검색 쿼리를 위한 tsquery 데이터 형식으로 변환합니다.
   • tsquery는 검색 쿼리를 나타내며, 검색하려는 단어 및 논리 연산자를 포함할 수 있습니다.
   • 이 함수를 사용하여 텍스트 검색 쿼리를 생성하고 검색할 수 있습니다.

Full-Text 검색은 다양한 언어 및 검색 기능에 대한 지원을 제공하며, 대용량의 텍스트 데이터에서 효율적인 검색을 수행할 수 있도록 합니다. PostgreSQL의 to_tsvector() 및 to_tsquery() 함수를 사용하면 풀 텍스트 검색을 구현하고 검색 결과를 개선할 수 있습니다.

 

코드

네이티브 쿼리로 원하는 데이터를 가져 왔다.

    @Override
    public Page<IdeaListResponse> findTitle(Pageable pageable, String keyword) {

        String sql = "SELECT * FROM idea WHERE to_tsvector('english', title) @@ to_tsquery('english', ?1) ORDER BY created_at DESC OFFSET ?2 ROWS FETCH FIRST ?3 ROWS ONLY";
        Query nativeQuery = entityManager.createNativeQuery(sql, Idea.class);
        nativeQuery.setParameter(1, keyword+":*");
        nativeQuery.setParameter(2, pageable.getOffset());
        nativeQuery.setParameter(3, pageable.getPageSize());
        List<Idea> responses = nativeQuery.getResultList();

        return new PageImpl<>(responses.stream()
                .map(idea -> new IdeaListResponse(
                        idea.getId(),
                        idea.getUsers().getNickname(),
                        idea.getTitle(),
                        idea.getContent(),
                        idea.getImageName(),
                        idea.getAuctionStatus(),
                        idea.getMinimumStartingPrice(),
                        idea.getBidWinPrice()
                ))
                .collect(Collectors.toList()),
                pageable, 0);

    }

 

주의 사항

실제로 QueryDSL 로 구현을 하다 알게 된점이 있다. 

특정 SQL 구문을 지원하는 부분은 QueryDSL의 제약이 있어 PostgreSQL 의 특정 기능을 지원하지 않는다고 한다.

라는 점이다... 이 점을 모르고 계속 QueryDSL 로 구현을 해보았지만 계속 실패하였다. 결국엔 네이티브 쿼리로 해결 하였다. 

02:00:15.955 [WARN ] - SQL Error: 0, SQLState: 42601
02:00:15.955 [ERROR] - ERROR: syntax error at or near "order"
Position: 104
02:00:15.971 [ERROR] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT * FROM Idea WHERE to_tsvector('english', title) @@ to_tsquery('english', ?) LIMIT ? OFFSET ? order by createdAt asc fetch first ? rows only] [ERROR: syntax error at or near "order"
Position: 104] [n/a]; SQL [n/a]] with root cause
org.postgresql.util.PSQLException: ERROR: syntax error at or near "order"
Position: 104
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)

당시에 에러 사항인데, order by 절은 limit 및 offset 절 앞에 와야되는 QueryDSL 에서는 order by 절을 먼저 써서 에러가 발생되었다

 

공식 문서

12.3. Controlling Text Search