how to query for rows by specifying the range of row numbers in postgresql

WePython 1 Months+

I have an application where in the user can see the history of all questions he has posted. These are have to be displayed in the order newest to oldest. I am implementing this in Android.
I have two questions
1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?
2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

-----------------Answer-----------------

1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?

No. You can create one by using TIMESTAMP WITH TIME ZONE (timestamptz for short) and setting a default using an appropriate function. (You can choose the timestamp of the start of database transaction, the start of execution of the INSERT statement, or the moment the row is processed -- whichever best suits your needs.) Don't be tempted to use TIMESTAMP WITHOUT TIME ZONE (the default if you just say TIMESTAMP) -- you would regret that the first time you hit a daylight saving time boundary.

2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.

This one is a bit tricky.

  • You can use non-transactional cursors, but that prevents database maintenance operations for as long as the cursor is open, so you want to make sure that the time is not unbounded.

  • You can use OFFSET and LIMIT, but that gets slow if you need a big OFFSET, since it has to read through all the preceding rows each time. It also gets weird if the data is being modified while you are paging around.

  • You can save values for the ends of what is being displayed and run a query with LIMIT to get the rows just outside that. This one performs well; the only real down side is programming complexity.

  • You can run the whole list to storage somewhere outside the database when they first request it, and do your scrolling from that. It may even be possible to use a temporary table for this, depending on other details.

This is a problem that people are always sorting out, and I don't think that any one answer is the best for all situations.


Previous : Difference between count(*) and count(1) in mysql?
Next : Creating dump file with use command