How can I use postgresql to sort a query with a custom sort order on a string?

Question

I have a table incident that has a status that is a string.

I want to query all incidents with a custom sort order.

A status can be one of the following: inProgress, completed, canceled

I want to be able to have a sort that is custom. Let the client specify the sort order. I am having problems with the query itself though.

I've tried a few things:

SELECT * 
FROM incident as i 
ORDER BY array_position(array["inProgress", "completed", "canceled"], i.status)

SELECT * 
FROM incident as i 
ORDER BY case when status = "inProgress" then 0
         case when status = "completed" then 1
         case when status = "canceled" then 2
              else 3

I get the error Unhandled rejection SequelizeDatabaseError: column "inProgress" does not exist on all of my attempts.

I'm expecting inProgress to be a value of status, but I'm not sure what I'm doing wrong.

Juan Carlos Oropeza · Accepted Answer · 2018-12-01 06:28:51Z

2

Check documentation for the right sintaxis. And text use single quotes. Double quotes is for fieldnames

ORDER BY case 
              when status = 'inProgress' then 0
              when status = 'completed' then 1
              when status = 'canceled' then 2
              else 3
         end

answered Dec 1, 2018 at 6:28

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juan Carlos Oropeza Over a year ago

You cant be getting the same error because if use single quotes you dont have inProgress field. Check again.

Collectives™ on Stack Overflow

How can I use postgresql to sort a query with a custom sort order on a string?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related