2

I have many queries that return some statistical data on a subject in various tables. The result includes a "classification" that is one of {"C","P","M"}. I want the query to return the rows in the order {"M","P","C"} i.e. its is not alphabetic nor any built in collation.

The data is irrelevant, but say I want to query something like a jockey's strike rate by those classifications and order the result by those codes in that order. I hope that makes sense?

So, is there a way to create a Postgresql Collation that can help me or is there another way? Note there are many queries, so a complex CASE clause is not my preferred solution as I'd have to put it in every query (in every program that may even contain it's own query as a literal.

Improve this question

2 Answers 2

Reset to default
0

You can create an ICU collation with explicit rules:

CREATE COLLATION weird (
   PROVIDER = icu,
   LOCALE = 'und',
   RULES = '&M < P < C'
);

This feature has been available from PostgreSQL v16 on.

Improve this answer
0

A CASE WHEN should not be that complex:

SELECT * 
FROM sampledata
ORDER BY CASE WHEN t='M' THEN 1
              WHEN t='P' then 2
              WHEN t='C' then 3
              ELSE 9 END;

NOTE:

  • The ELSE 9 is only needed then other values than 'M','P' or 'C' exist.

But it is also possible without a CASE WHEN statement, using an ENUM:

CREATE TYPE mpc AS ENUM ('M', 'P', 'C');

SELECT  *
FROM sampledata 
ORDER BY cast(t as mpc);

NOTE: It might be better to change the column type of this field to mpc, so it can be used in an index. This should speed up sorting.

see: DBFIDDLE

Improve this answer
4
  • The point of the question is how to create a collation. I don't want to have to change 800 odd query strings, just to make the column sort the way I want. Commented Mar 30 at 11:10
  • If your point is "how to create a collation" Why did you not use that as a title of your question ? Commented Mar 30 at 12:40
  • Please read collation support, and you may conclude that creating a collation to solve this might not work..... Commented Mar 30 at 12:43
  • Sorry folks, in the comment I meant collation in the general sense, not a Postgresql Collation. Commented Apr 1 at 23:58

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.