Postgresql Is it possible to create a selection based on a 'case value'

Question

Is it possible to create a selection based on a 'case value'. In below example the Category. Let's say I want to get the Low value. It throws up a error when I use WHERE Category = 'Low', because the column Category does not exist.

I know I could create the selection by replacing WHERE Category = 'Low' with Where digit > 0 AND digit <=40. But that seems like a bad way to do so because off the double expression.

SELECT digits,

    CASE 
        When digit > 0 AND digit <=40 THEN 'Low'
        When digit > 40 AND digit <=75 THEN 'Average'
        When digit > 75  THEN 'High'
    END AS Category

FROM digits_table

WHERE Category  = 'Low'

Denis de Bernardy · Accepted Answer · 2013-10-21 08:08:38Z

2

You can use a with statement:

WITH tmp as (
SELECT digits,

CASE 
    When digit > 0 AND digit <=40 THEN 'Low'
    When digit > 40 AND digit <=75 THEN 'Average'
    When digit > 75  THEN 'High'
END AS Category

FROM digits_table
)
select * from tmp where category = 'Low'

edited Oct 21, 2013 at 8:08

answered Oct 21, 2013 at 6:56

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

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

Comments

Milen A. Radev · Accepted Answer · 2013-10-21 07:43:03Z

1

Or (old skool):

SELECT * FROM (
    SELECT digits,
    CASE 
        When digit > 0 AND digit <=40 THEN 'Low'
        When digit > 40 AND digit <=75 THEN 'Average'
        When digit > 75  THEN 'High'
    END AS Category
    FROM digits_table
) AS tmp WHERE Category = 'Low'

answered Oct 21, 2013 at 7:43

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Comments

Evgeniy Chekan · Accepted Answer · 2013-10-21 13:10:42Z

With PSQL 9.2+ you can use Range types and Range functions and operators

tl;dr;

select * from digits_table where digits <@ int4range(0, 40, '(]');

Long version

Consider this table:

postgres=# select * from tests;
 id | digit 
----+-------
  1 |    10
  2 |    20
  3 |    30
  4 |    40

You can query (note that int4range bottom bound here is inclusive, whereas top bound is exclusive):

postgres=# select * from tests where digit <@ int4range(20,40);
 id | digit 
----+-------
  2 |    20
  3 |    30

Of course, you can cast a string to a desired in/exclusion mode (just put it down as you would normally do at math class, then cast to a range type):

postgres=# select * from tests where digit <@ '(20,40]'::int4range;
 id | digit 
----+-------
  3 |    30
  4 |    40

postgres=# select * from tests where digit <@ '[20,40]'::int4range;
 id | digit 
----+-------
  2 |    20
  3 |    30
  4 |    40

There is an easier yet explicit way to control bounds (this is PostgreSQL, baby!):

postgres=# select * from tests where digit <@ int4range(20,40,'[]');
 id | digit 
----+-------
  2 |    20
  3 |    30
  4 |    40

postgres=# select * from tests where digit <@ int4range(20,40,'()');
 id | digit 
----+-------
  3 |    30

postgres=# select * from tests where digit <@ int4range(20,40,'[)');
 id | digit 
----+-------
  2 |    20
  3 |    30

Collectives™ on Stack Overflow

Postgresql Is it possible to create a selection based on a 'case value'

3 Answers 3

Comments

Comments

tl;dr;

Long version

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

tl;dr;

Long version

Comments

Your Answer

Sign up or log in

Post as a guest

Related