Get total hit count with Postgres full text search

Question

When doing a Postgres full text search, is it possible to return the top 10 relevance-ranked rows (using LIMIT), but also get the total number of rows found?

I'd like to implement a Google-like message in my search results: "Showing rows 1 to 10 of 1000 results"

Hauleth · Accepted Answer · 2019-09-27 18:10:03Z

2

You can use window functions for that:

SELECT
  *,
  COUNT(*) OVER ()
FROM table
LIMIT 10

answered Sep 27, 2019 at 18:10

Hauleth

23.7k4 gold badges67 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ccleve Over a year ago

Wow. That really works. I wonder about performance? Any insight?

Hauleth Over a year ago

@ccleve the same as SELECT COUNT(*) FROM table

Collectives™ on Stack Overflow

Get total hit count with Postgres full text search

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related