0

I'm sorry for the title, I don't know how to clearly summarize the problem.
That's probably why I couldn't find an answer when searching by myself.
Feel free to improve it.

Anyways, let's say I have a query returning primary id's.

SELECT id FROM ...

Instead of having results presented with one row for each id like this:

  id 
-----
   1
   2
   3
  45
 182
 183
 184

I would like to know if there's any access to some internal state based on the index that would return this:

  ranges
---------
     1-3
      45
 182-184

The whole point here is NOT to have a nice presentation, I can do that.
Besides it would add a treatment after having run the query, I want the opposite.

I'd like to know if it exists some king of shortcut that would accelerate the query by not having to return each row individually. Maybe something related to extracting data directly from the indexes used in the WHERE clause.

I'm not aware of a generic SQL way to do that but I would love to know if there's some postgres feature for this.

If the answer is "no", it's ok. I just had to ask...

Improve this question
4
  • OK, thanks @a_horse_with_no_name for pointing me to the "gaps & island" direction, that will help to find an answer Commented Apr 19, 2022 at 12:15
  • Btw, the provided "resolution" is very wrong, I clearly said I wasn't looking for a cosmetic solution involving more work from the db engine. The answer might just be "no" after all. Commented Apr 19, 2022 at 12:16
  • Well, all those duplicates return exactly what you want: the ranges of the "islands" (as an array or simply start/stop columns). If you don't want such an output, you shouldn't have shown that as an example of the output you want. Commented Apr 19, 2022 at 12:19
  • Hi, I know you just try to help and I thank you for that. I also know I didn't express myself correctly because a) I didn't know the gaps & island terms b) I'm not native English speaker. But I said I was looking for a potential postgres feature and said I already knew how to get the shown output. Commented Apr 19, 2022 at 13:50
Related questions
870
How to drop a PostgreSQL database if there are active connections to it?
529
Best way to select random rows PostgreSQL
400
Update multiple rows in same query using PostgreSQL
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.