Postgres sort numeric on text varchar column Postgres Useful Tips 2022-03-07

You would always get this kind of result when you’re using a text or varchar column in an ORDER BY query. The sorting is not according to the numeric value.

If you need the following result, here is how you can order by numeric on the text & varchar column.

Alter column with numeric sorting

CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

ALTER TABLE "your_table" ALTER COLUMN "your_column" type TEXT COLLATE numeric;

More details & examples

-- view all collations in this table
SELECT * FROM pg_collation;

-- create a new collation
CREATE COLLATION your_new_collation_name (provider = icu, locale = 'en@colCaseFirst=upper;colNumeric=yes');

en: locale (omittable)
colCaseFirst: upper | lower | false
colNumeric: yes | no

-- omitted locale
CREATE COLLATION your_new_collation_name (provider = icu, locale = '@colCaseFirst=upper;colNumeric=yes');

For more details:

–