CODEWITHSUNDEEP
sqlpostgresqlsortingpg
Puneet Pandey
Puneet Pandey

Reputation: 555

PostgreSQL: Sorting on alphanumeric string and decimal field

Hope you all are fine and doing good!

I have a table items with 2 million+ records the structure looks like below:

id (int) | price (decimal) | priority (int)
-------------------------------------------
10001    |  59000.25       |    1
10002    |  73000.91       |    2
10003    |  1000.23        |    1
10004    |  9567.18        |    1

The solution that I am looking for is very straightforward: How do I sort this table on price+priority on ASC|DESC order?

Current and working solution: I am using ORDER BY priority ASC, price ASC. But as far as my knowledge goes, sorting on multiple column is slow and not optimised approach (and I am facing realtime slowness because of this).

Solutions I tried: I've added a temporary column to this table:

id (int) | price (decimal) | priority (int) | new_priority (varchar)
--------------------------------------------------------------------
10001    |  59000.25       |    1           | a59000.25
10002    |  73000.91       |    2           | b73000.91
10003    |  1000.23        |    1           | a1000.23
10004    |  9567.18        |    1           | a9567.18

I've replaced 1 => a, 2 => b, 3 => c up till 10 (max number I have in database)

Now, whenever I am trying below SQLs, none of them is working

SELECT * FROM items
ORDER BY new_priority ASC

SELECT * FROM items
ORDER BY new_priority::bytea

SELECT * FROM items
ORDER BY SUBSTRING(new_priority FROM '^(.*?)( \\d+)?$'),
     COALESCE(SUBSTRING(new_priority FROM ' (\\d+)$')::INTEGER, 0)

Please advise!

Links I referred:

  1. Postgresql sorting mixed alphanumeric data
  2. Alphanumeric Sorting in PostgreSQL
  3. Alphanumeric sorting with PostgreSQL
  4. Alphanumeric case in-sensitive sorting in postgres

Upvotes: 3

Views: 2241

Answers (3)

joninx
joninx

Reputation: 1780

How about creating an index? Indexes are a mechanism to enhance the performance of databases. Creating an index may be slow and might last some hours, but you'll notice differences in further queries.

Upvotes: 2

librata
librata

Reputation: 150

How often does the data change ? If it's not often, use CLUSTER. See : https://www.postgresql.org/docs/9.5/static/sql-cluster.html It will basically sort your table in the order of your index. Combine this cluster with the answer from etsa.

The catch is, the CLUSTER doesn't maintain the order. So if you have new row inserted, it will be inserted to the last row.

Upvotes: 0

etsa
etsa

Reputation: 5060

May be you could do a better solution using NEW_PRIORITY as DECIMAL.

Assuming the value in PRICE is not greater then (say) 999999 and the PRIORITY is not too "large", you could use PRIORITY*1000000+PRICE.

Anyway, I think using index as said in comments could be a better solution.

To create a "composite index", you can use following sintax:

CREATE INDEX index_name ON table_name (priority ASC, price ASC);

Upvotes: 0

Related Questions