Postgresql Sort

How to sort in PostgreSQL by uppercase first and after by alphabetically?

This can be done by using CASE WHEN and testing uppercase;

SELECT *,
       CASE
           WHEN name = UPPER(name) THEN 1
           WHEN LEFT(name, 1) = UPPER(LEFT(name, 1)) THEN 2
           ELSE 3
       END toOrder
FROM users
ORDER BY toOrder, name ASC

Tested on PostgreSQL 13

Postgresql order by out of order

Thou shalt cast thy id to integer to order it as number.

SELECT * FROM bible ORDER BY cast(id AS integer);

How to sort by biggest ID to smallest in PostgreSQL

What you are looking for is the desc keyword.

select company, url from clients order by id desc;

PostgreSQL - ORDER BY with LIMIT not using indexes as expected

Upgrading to Postgres 13 fixed this for us, with the introduction of incremental sort. From some docs on the feature:

Incremental sorting: Sorting is a performance-intensive task, so every improvement in this area can make a difference. Now PostgreSQL 13 introduces incremental sorting, which leverages early-stage sorts of a query and sorts only the incremental unsorted fields, increasing the chances the sorted block will fit in memory and by that, improving performance.

The new query plan from EXPLAIN is as follows, with the query now completing in <500ms consistently:

QUERY PLAN
Limit  (cost=71.06..820.32 rows=5000 width=20)
  ->  Incremental Sort  (cost=71.06..15461.82 rows=102706 width=20)
"        Sort Key: ed.event_id, ed.version"
        Presorted Key: ed.event_id
        ->  Nested Loop  (cost=0.84..6659.05 rows=102706 width=20)
              ->  Index Only Scan using event_id_version on deltas_to_retrieve zz  (cost=0.28..1116.39 rows=541 width=20)
              ->  Index Only Scan using event_deltas_pkey on event_deltas ed  (cost=0.56..8.35 rows=190 width=20)
                    Index Cond: ((event_id = zz.event_id) AND (version > zz.version))

Is there a way to store pre-sorted rows in postgres?

You can use a CLUSTER command, or you can just create new table by command CREATE TABLE xxx AS SELECT ... ORDER BY. Still you should to use ORDER BY statement because PostgreSQL has optimized reading of bigger tables and try to use synchronize reading for more processes. This synchronization can do so reading of table for one process starts in 1/3 of table - read to end, back to start and read first 1/3. So Postgres doesn't ensure reading data in physical order too.

Postgresql group by multiple columns and sort within each group

Use FIRST_VALUE() window function:

SELECT DISTINCT order_id, 
       FIRST_VALUE(doc_id) OVER (
         PARTITION BY order_id 
         ORDER BY amount DESC NULLS LAST, rating DESC NULLS LAST, percent DESC NULLS LAST, customer_id
       ) doc_id
FROM orders;

See the demo.

PostgreSQL Distinct Sort For Huge Amount of Data

It looks like the core of the problem is that the planner is not using a hashed subplan (where it runs it in bulk once and memorizes the results in a hash) for the NOT EXISTS, but rather is running it parameterized for each tuple in a loop. Usually this is because the planner thinks it will take too much memory to hash the results, but in this case I think it is just because it can not figure out how to analyze GROUP BY...HAVING.

You can guide it down the (presumably) correct path here by replacing the NOT EXISTS (...) with:

 AND e.objectid NOT IN (
   SELECT triggerid FROM functions f, items i, hosts_groups hgg
   LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (12, 13, 14 /*...*/)
   WHERE f.itemid=i.itemid  AND i.hostid=hgg.hostid
   GROUP BY triggerid, i.hostid HAVING MAX(permission)<2  OR MIN(permission) IS NULL OR MIN(permission)=0
 )

But before trying this, I might run just the inner query there by itself to see how long it takes and how many rows it returns.

If this ends up working, it might be worthwhile to investigate what it would take to make the planner smart enough to do this conversion on its own.

---

Related Topics