Re: What is the alternate of FILTER below Postgresql 9.4 ?

Hi

2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit(at)rocketmail(dot)com>:

> Hi,
>
> Please look at my query :
>
> [shreyas(at)rails_app_test (master)]$ rails db
> psql (9.4.1)
> Type "help" for help.
>
> app_development=# select id, location, name from people;
> id | location | name
> ----+----------+------
> 2 | X | foo
> 3 | X | foo
> 4 | Y | foo
> (3 rows)
>
> app_development=# SELECT COUNT(id) FILTER(WHERE lower(location) != 'x') AS
> Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc FROM
> "people";
> non_x_loc | x_loc
> -----------+-------
> 1 | 2
> (1 row)
>
> This *FILTER* method is available from 9.4, How can I get the same output
> below 9.4 version ?
>
>
use SQL CASE

SELECT COUNT(CASE lower(location) <> 'x' THEN 1 END), ...

attention: "lower" function is slow - so don't use it if it is not necessary

Regards

Pavel Stehule

> --
> ================
> Regards,
> Arup Rakshit
> ================
> Debugging is twice as hard as writing the code in the first place.
> Therefore, if you write the code as cleverly as possible, you are, by
> definition, not smart enough to debug it.
>
> --Brian Kernighan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>