SELECT after specific row with non-sequential (uuid) primary key

Question

With the following table:

CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    inserted_at timestamptz NOT NULL DEFAULT now()
    -- other fields
);

How could I retrieve n rows after a specific id, ordered by inserted_at ?

I'm a bit confused as to how your query is supposed to be working. The `where` clause would seem to be filtering `id`s that are less than the specified id, not the time associated with that id. — Gordon Linoff, Sep 15 '21 at 12:43
@GordonLinoff I'm confused too, I am unable to make my query work again. I think that because I picked a uuid beginning with `f` for manual tests I was unable to detect that it filtered value BELOW that and not after. Apologies ! — Malobre, Sep 15 '21 at 13:03

score 1 · Accepted Answer · answered Sep 15 '21 at 12:27

I want to retrieve n rows after a specific id, ordered by inserted_at.

I am expecting something like this:

select u.*
from users u
where u.inserted_at > (select u2.inserted_at from users u2 where u2.id = 'f4ae4105-1afb-4ba6-a2ad-4474c9bae483')
order by u.inserted_at
limit 10;

For this, you want one additional index on users(inserted_at).

SELECT after specific row with non-sequential (uuid) primary key

1 Answers1