5

I have the following table:

CREATE TABLE post (
  id            bigint primary key,
  thread_id     bigint,
  is_notice     boolean,
  title         text,
  content       text
)

I display the list using the following query:

SELECT * FROM post ORDER BY is_notice desc, thread_id desc, id

Then, given the post selected by id(i.e. SELECT * FROM post where id=3), how do I retrieve the next and previous posts?

  • 1
    Well, not having a WHERE clause, you return all rows from the table, there is no next or previous. Could you explain a bit better? – dezso Nov 24 '13 at 13:06
  • @dezso Sorry, I edited the post – alice Nov 24 '13 at 14:24
8

Using PostgreSQL's Window Functions, specifically LAG and LEAD, should be able to show you the previous and next entries in your table.

select *
from (
    select  id, thread_id, is_notice, title, content,
            lag(id) over (order by is_notice desc, thread_id desc, id asc) as prev,
            lead(id) over (order by is_notice desc, thread_id desc, id asc) as next
    from post
    ) x
where 3 IN (id, prev, next);

A demo can be found here: http://sqlfiddle.com/#!15/9fd7a/8

  • I wonder why you used 3 = ANY(array[id, prev, next]) and not the simpler 3 IN (id, prev, next) – ypercubeᵀᴹ Nov 24 '13 at 16:50
  • 1
    And shouldn't there be an ORDER BY inside the OVER (...)? – ypercubeᵀᴹ Nov 24 '13 at 16:52
  • 1
    @ypercube: You may be interested that 3 IN (id, prev, next) is rewritten to 3 = ANY('{id, prev, next}') internally. EXPLAIN ANALYZE reveals it. – Erwin Brandstetter Nov 26 '13 at 0:52
  • 1
    @bma: Well, ultimately either of these expressions is resolved to a list of ORed expressions. Maybe, the 9.3.1 planner goes one step further in this process. I didn't retest with the latest version. – Erwin Brandstetter Nov 26 '13 at 16:48
  • 1
    Wow the 3 in (id, prev, next) blows my mind, I wish I knew about that before :) – Davos Nov 13 '17 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.