Postgresql - Order by varchar (cast to float) double precision exception

I am trying to sort a list of table rows based on varchar column that contains values from null to like '1', '1.1', '9.2','12', etc...

Getting the double precision exception, while on some online postgresql database my code works.

select *  
from api r 
  join santa_api v on v.id = r.api_id 
order by r.api_id, r.prefix_1::float

You could try using regex so that it only attempts the conversion when the string contains only digits, decimal points and scientific notation. i.e. Th regex below matches a string that starts with an optional plus or minus sign followed by zero or more digits. Then there may be an optional decimal point followed by one or more digits. Finally, there may be optional scientific notation consisting of the letter ‘e’ or ‘E’ followed by an optional plus or minus sign and one or more digits.

SELECT column_name
FROM table_name
ORDER BY
    CASE
        WHEN column_name ~ '^[+-]?\d*\.?\d+([eE][+-]?\d+)?$'
        THEN column_name::numeric
        ELSE NULL
    END;

You could choose some other default value than NULL, that's up to you. here is a working example:

SELECT column_name
FROM (VALUES ('10.7'), ('2.1'), ('3.5'), ('fred')) AS t(column_name)
ORDER BY
    CASE
        WHEN column_name ~ '^[+-]?\d*\.?\d+([eE][+-]?\d+)?$'
        THEN column_name::numeric
        ELSE NULL
    END;

fiddle