Question 90449 in PostgreSQL Question posted in
The official documentation can be found here.

SQL – How to sort numbers in a VARCHAR column with empty strings as entries – Postgresql

I have a postgres column which is like so: enter image description here

It only has numbers or empty string.

I want to be able to sort the numbers by the numbers but as I go to cast the column to a float, it will give me the following error:

ERROR:  invalid input syntax for type double precision: ""

Is there a way I can do this sort, and having the empty strings be treated as 0?

This is my query that’s failing:

SELECT C.content
FROM row R 
LEFT JOIN cell C ON C.row_id = R.row_id 
WHERE R.database_id = 'd1c39d3a-0205-4ee3-b0e3-89eda54c8ad2' 
AND C.column_id = '57833374-8b2f-43f3-bdf5-369efcfedeed'
ORDER BY cast(C.content as float)
Tags:

2

Answers


  1. 0

    when its an empty string you need to either treat it as null or 0 and then it will work, try putting a case statement like so in the order by

    ORDER BY 
    case when C.content = '' then 0
         else cast(C.content as float)
    end
    Login or Signup to reply.
  2. 0

    If it’s sure this column will never have negative values, a simple option is just adding a leading zero.

    If the column is NULL or has an empty string, it will be sorted as 0.

    Otherwise, the value will be sorted as it is because adding a leading zero doesn’t change anything.

    SELECT yourcolumn 
FROM yourtable
ORDER BY CAST(CONCAT('0',yourcolumn) AS FLOAT);

    If negative values can appear, this would fail, so I would then use CASE WHEN.

    But I propose to also take 0 for NULL values, not only for empty strings:

    SELECT yourcolumn 
FROM yourtable
ORDER BY 
CASE WHEN yourcolumn = '' OR yourcolumn IS NULL 
  THEN 0
  ELSE CAST(yourcolumn AS FLOAT)
END;

    Otherwise, NULL values would be sorted as highest number which is likely not intended.

    And yes, I know you wrote there are numbers and empy strings only in your table, but maybe this can change (unless the column is not nullable). So adding this condition doesn’t hurt.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search