Feature: Sort multiple columns individually as a sequence of sort steps

[0-issue]

Is it possible to sort multiple columns? With such a feature one would be able to answer questions like: for every unique value in column-x (sorted ascending ↑), what are all values in column-y (sorted descending ↓)?

In the header row, next to the name, there would be special label that indicates that a sort was performed on that column, what the sort order was, and where is it in sequence of sort steps. For instance "land_area ↑2" in header row would suggest that this column "land_area" was sorted ascending (↑), and was the second (2) column to be sorted.

If such a feature is supported, then additionally, a keybinding would allow undo/redo of sorts in the sort sequence.

Sidenote question: what all PostgreSQL datatypes does pspg support for sorting? and are there any expected differences from PostgreSQL's sorting for columns of same datatypes? Is the goal to match the sort functionality to PostgreSQL's sort? If so, perhaps a collection of tests would be good to have.

[okbob]

st 28. 6. 2023 v 5:44 odesílatel amanvm @.***> napsal:

Is it possible to sort multiple columns individually? With such a feature one would be able to answer questions like: for every unique value in column-x (sorted ascending), what are all values in column-y (sorted descending)?

In the header row, next to the name, there would be special label that indicates that a sort was performed on that row, what it's sort order was, and where it is in sequence of sort steps. For instance "land_area ↑2" next to column name would suggest that this column "land_area" was sorted ascending, and was the second column to be sorted.

If such a feature is supported, then additionally, a keybinding would allow undo/redo of sorts in the sort sequence.

No, it is not possible. It requires a very significant redesign of pspg, and I don't plan it. Currently, pspg works over input data in original text tabular format. For more complex tasks it is better to store data to columns on input, and hold already parsed data in memory. I think it is going out of pspg. Usually you can simply write ORDER BY in a query and there you can use more columns, more directions.

There are some more data oriented tools that can be used for this purpose like https://www.visidata.org/ or https://github.com/andmarti1424/sc-im or any other.

pspg is a pager for psql (pgcli, ..) and it is not a replacement for SQL ;-)

— Reply to this email directly, view it on GitHub https://github.com/okbob/pspg/issues/225, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO44HG2XUGJIWJPTD6Q3XNOSCHANCNFSM6AAAAAAZWN74YU . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[0-issue]

Thanks for your prompt response. Ideally such a feature would be part of psql, instead of it requiring an external pager. That way we won't need to rely on a external pager implementing PostgreSQL's handling of sorts, and perhaps such an inbuilt pager in psql would just plug into a C library of Psql that sends the real SQL sort queries to database and display the results. You seem quite involved in PostgreSQL development, have you considered merging pspg in psql to enable such advanced functionality?

[okbob]

st 28. 6. 2023 v 6:19 odesílatel amanvm @.***> napsal:

Thanks for your prompt response. Ideally such a feature would be part of psql, instead of it requiring an external pager. That way we won't need to rely on a external pager implementing PostgreSQL's handling of sorts, and perhaps such an inbuilt pager in psql would just plug into a C library of Psql that sends the real SQL sort queries to database and display the results. You seem quite involved in PostgreSQL development, have you considered merging pspg in psql to enable such advanced functionality?

Fortunately, this is Unix design - without an external pager, the pspg cannot be implemented. But it has some disadvantages. Unfortunately, there is not any more structured communication between pspg and psql, so pspg has to do hard work to get some information about result structure, although these data are in psql in native format.

I don't think significant changes inside psql (like merging of pspg) are possible. psql is a fundamental part of the Postgres project, and the community strongly prefers stability against features there. psql has very old code, and any modernization or more significant enhancement is hard (or almost impossible). The people from the community are very sensitive to possible breaking of psql, and large changes are not acceptable. pspg supplies some functionality that I cannot push to psql. And I understand. psql was designed in the era of dumb terminals, slow network and slow CPU and low memory. It should be totally rewritten, but the community has no capacity and motivation for this hard and long task - only few percent of users use terminal clients. Writing a nice terminal application is not easy. I spent hundreds of hours on pspg. Development applications with a rich UI in the terminal is hell. Some platforms use two decades old libraries, popular MacOS two decades old BSD libraries. For the C language there is no modern API or any API without historical garbage deposits.

There are some interesting projects, but without strong progress now like https://github.com/xo/usql

psql is not dead, still there are (and will be) minor enhancing and bug fixing, but bigger changes (like merging pspg or own lighter pager) are impossible. Unfortunately.

— Reply to this email directly, view it on GitHub https://github.com/okbob/pspg/issues/225#issuecomment-1610676089, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO45HXEQ66A4A4OVJJVLXNOWFDANCNFSM6AAAAAAZWN74YU . You are receiving this because you commented.Message ID: @.***>

[0-issue]

Ok, thanks a lot for your explanation. Perhaps I can try filing a feature request on usql.