Multiple conditions in case expression in postgres

Question

I am trying to order rows by string column like this:

select * from cards
order by (case gate 
          when 'mastercard' then 1
          when 'visa' then 2
          when 'fibi' then 3
          when 'uzcard' then 4
          when 'humo' then 5 
          end)

But I want mastercard and visa have the same index. I tried something like this:

select * from cards
order by (case gate 
          when 'mastercard' or 'visa' then 1
          when 'fibi' then 2
          when 'uzcard' then 3
          when 'humo' then 4
          end)

but I get this error:

ERROR: invalid input syntax for type boolean

"didn't work" is not a problem description. What did it do? Why was that wrong? Sample input and desired output would greatly help, and current output and an explanation of what's wrong with it. — underscore_d
– underscore_d, Commented Jun 1, 2021 at 9:17
Simply use when 'mastercard' then 1 and when 'visa' then 1 . — Zilog80
– Zilog80, Commented Jun 1, 2021 at 9:19

Masklinn · Accepted Answer · 2021-06-01 09:21:35Z

3

Either create a second case with the same result, or convert your case to a full conditional.

CASE has two forms: the base form is

CASE 
    WHEN condition THEN result
    WHEN condition THEN result
    ...
END

in which case condition is an arbitrary boolean expression, similar to a sequence of if/else if/else if in C, or the shortcut

CASE expression
    WHEN value THEN result
    WHEN value THEN result
    ...
END

in which case value is... just a simple value, similar to a switch statement in C.

Here you have the second form. 'mastercard' or 'visa' is just plain nonsense, it's not SQL at all as it will attempt to treat 'mastercard' and visa' as booleans (which will fail, because neither is a valid boolean literal), then OR them, then will check if gate is equal to the result.

You can either add a new case with the same result

case gate 
          when 'mastercard' then 1
          when 'visa' then 1
          when 'fibi' then 2
          when 'uzcard' then 3
          when 'humo' then 4
          end

or expand the statement to a full conditional:

case
    when gate = 'mastercard' or gate = 'visa' then 1
    when gate = 'fibi' then 2
    ...

answered Jun 1, 2021 at 9:21

Masklinn

43.7k4 gold badges58 silver badges78 bronze badges

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

2 Comments

Dils Matchanov Over a year ago

@underscore_d can you answer the question with in example please?

underscore_d Over a year ago

@DilsMatchanov thinking about it properly now, it wouldn't really help that much here, as Masklinn said, as it would quite a bit longer / more repetitive. But what I meant was you would write e.g. case when gate in ('mastercard', 'visa') then 1 when gate = 'fibi' then 2... etc. The in operator allows comparing the lhs against a set of multiple values.

trillion · Accepted Answer · 2021-06-01 09:45:29Z

1

you can also use or operator here, this way you can prevent multiple when with same values and the list can be extended as well:

Case when gate in ('master_card','visa') then 1
     when gate = 'fibi' then 2
     .....
End as [column name as you want it to be]

answered Jun 1, 2021 at 9:45

trillion

1,4011 gold badge9 silver badges23 bronze badges

Collectives™ on Stack Overflow

Multiple conditions in case expression in postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related