postgresql – Comparing strings in postgres using comparison operators?

postgresql – Comparing strings in postgres using comparison operators?

NOTE: The original answer went off on a red herring.

A simple comparison sorts character by character.

select a1 < a9; -- true because a = a and 1 < 9.

…but quickly goes to pot.

select a10 < a9; -- also true for the same reason.

What you want is a natural sort where the string parts are compared as strings and the numbers are compared as numbers. Doing a natural sort in SQL is not the easiest thing. You either need fixed field widths to sort each substring separately, or maybe something with regexes…

Fortunately theres pg_natural_sort_order, a Postgres extension that implements an efficient natural sort.

If you cant install extensions you can use a stored procedure like btrsort by 2kan.

CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
    SELECT 
        CASE WHEN $1 ~ ^[^0-9]+ THEN
            COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM [^0-9]+))+1 ),  )
        ELSE
            COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM [0-9]+))+1 ),  )
        END

$$ LANGUAGE SQL;

CREATE FUNCTION btrsort(text) RETURNS text AS $$
    SELECT 
        CASE WHEN char_length($1)>0 THEN
            CASE WHEN $1 ~ ^[^0-9]+ THEN
                RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM ^[^0-9]+), ), 1, 12), 12,  ) || btrsort(btrsort_nextunit($1))
            ELSE
                LPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM ^[0-9]+), ), 1, 12), 12,  ) || btrsort(btrsort_nextunit($1))
            END
        ELSE
            $1
        END
      ;
$$ LANGUAGE SQL;

Though it doesnt provide a comparison operator and Im not going to pretend to understand it. This allows you to use it in an order by.

select * from things order by btrsort(whatever);

To prevent your naturally sorted queries from turning to mud on large tables, you can create a btree index on the result of that function.

create index things_whatever_btrsort_idx ON things( btrsort(whatever) );

SELECT
  CASE WHEN a < b THEN yes END
  FROM table
  Output: null

This will only output nothing if the table is empty. You dont need a table to test select statements.

SELECT
CASE WHEN a < b THEN yes END  -- yes

postgresql – Comparing strings in postgres using comparison operators?

Leave a Reply

Your email address will not be published. Required fields are marked *