Hacker News Reader

Comment by dmitryminkovsky | Hacker News Reader

The Use The Index Luke material about this is essential background reading, but in terms of actually implementing:

My favorite SQL implementation of cursor-based keyset pagination can be found on this Hasura issue: https://github.com/hasura/graphql-engine/issues/141, specifically this comment: https://github.com/hasura/graphql-engine/issues/141#issuecom.... Something so elegant about this SQL. I enjoy it a lot.

Also really enjoy this answer about how to implement keyset pagination on multiple columns:

https://stackoverflow.com/questions/38017054/mysql-cursor-ba...

View on HN

That multi column answer doesn't seem to address pagination of queries where some of the columns are in descending order and some aren't.

I implemented a keyset paging library for sqlalchemy which supports this, and found the nicest way to do it is to swap the comparisons for the descending columns. For instance if this is an all-ascending paginated query:

    select ... where row(c1, c2, c3) > row(11, 12, 13) order by c1, c2, c3

Then with c2 descending it would look like this:

    select ... where row(c1, 12, c3) > row(11, c2, 13) order by c1, c2 desc, c3

fyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]:

> For row comparisons, (a, b) > (x, y) is equivalent to:

> (a > x) OR ((a = x) AND (b > y))

[1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...

Just in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like:

SELECT * EXCEPT FOO_ID FROM FOO;

I have always wanted this but have never vome across it...

The correct answer is to just apply a view and enumerate the wanted column names once.

Otherwise, dynamic column names (that's your search engine fodder) require introspection…

    -- "Chinook" sample database
    select column_name
    from information_schema.columns
    where table_name = 'Track'
    except
    select 'UnitPrice'

… coupled with the moral equivalent of PL/pgSQL `execute` (i.e. run-time eval) statement.

Thanks for answering! Currently I do use views but it's just something I had wondered about.

I mean, the information from the information_schema must be updated anyway when one deletes a column or table, so I thought maybe a function like that which looks it up could exist.

I will try with PL/pgSQL, have long wanted to familiarise myself with it anyway.

Postgres has a clever trick [1] you can use, though I wouldn't say it's something I would ever use.

[1] https://blog.jooq.org/2018/05/14/selecting-all-columns-excep...

I've also wanted this on Postgres for years.

If there's subset of rows you frequently want, you may just be able to define a view and use that. (At one point, I defined a text macro in my terminal to list the fields I usually wanted on our "orders" table.)

Never heard of such a thing but maybe a good question for SO.

Maybe

WHERE NOT EXIST (SELECT ...)

can help?

I think the question was about excluding columns, not rows.

I don't know any way to do that personally.

That is good to know! I’ve only used this approach with PGSQL

Reply on news.ycombinator.com