Postgres Pro
Downloads
Home   >   mailing lists

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

What is the alternate of FILTER below Postgresql 9.4 ?

From
Arup Rakshit
Date:
Hi,

Please look at my query :

[shreyas@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 ?

--
================
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

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

From
Pavel Stehule
Date:
Hi

2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:
Hi,

Please look at my query :

[shreyas@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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From
Andreas Kretschmer
Date:
Arup Rakshit <aruprakshit@rocketmail.com> wrote:

> This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ?


case when ...

see:
http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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

From
Arup Rakshit
Date:
On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote:
> Hi
>
> 2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:
>
> > Hi,
> >
> > Please look at my query :
> >
> > [shreyas@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

Pavel,

I tried, but it is not giving the output exactly like *FILTER*.

app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN lower(location)
!='x' THEN COUNT(id) ELSE 0 END AS Non_X_loc from people group by lower(location); 
 x_loc | non_x_loc
-------+-----------
     0 |         1
     2 |         0
(2 rows)
app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) !=
'x'THEN 1 END) AS Non_X_loc from people group by lower(location); 
 x_loc | non_x_loc
-------+-----------
     0 |         1
     2 |         0
(2 rows)

It is 2 rows output.

--
================
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

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

From
Arup Rakshit
Date:
On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote:
> Arup Rakshit <aruprakshit@rocketmail.com> wrote:
>
> > This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ?
>
>
> case when ...
>
> see:
> http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/
>
> Andreas
>

Humm.. That's correct. I used the same with group by, that's why got 2 rows output.

Anway thanks for your help! :-)

--
================
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

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

From
Arup Rakshit
Date:
On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote:
> Arup Rakshit <aruprakshit@rocketmail.com> wrote:
>
> > This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ?
>
>
> case when ...
>
> see:
> http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/
>
> Andreas
>

Humm. It is the one I wanted. But I used it, with group by, which is why I got 2 rows output.

Thanks!!

--
================
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

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

From
Roxanne Reid-Bennett
Date:
On 2/24/2015 11:29 AM, Arup Rakshit wrote:
> On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote:
>> Hi
>>
>> 2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:
>>
>>> Hi,
>>>
>>> Please look at my query :
>>>
>>> [shreyas@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
> Pavel,
>
> I tried, but it is not giving the output exactly like *FILTER*.
>
> app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN
lower(location)!= 'x' THEN COUNT(id) ELSE 0 END AS Non_X_loc from people group by lower(location); 
>   x_loc | non_x_loc
> -------+-----------
>       0 |         1
>       2 |         0
> (2 rows)
> app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location)
!='x' THEN 1 END) AS Non_X_loc from people group by lower(location); 
>   x_loc | non_x_loc
> -------+-----------
>       0 |         1
>       2 |         0
> (2 rows)
>
> It is 2 rows output.
>

then don't include the group by (which forces 1 row per location)

select sum(case WHEN lower(location) = 'x' THEN 1 else 0 end) AS x_loc,
sum(case WHEN lower(location) != 'x' THEN 1 else 0 end) AS not_x_loc
from people;


Roxanne

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

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

From
Ken Tanzer
Date:


I tried, but it is not giving the output exactly like *FILTER*.

app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 END AS Non_X_loc from people group by lower(location);
  x_loc | non_x_loc
-------+-----------
      0 |         1
      2 |         0
(2 rows)
app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc from people group by lower(location);
  x_loc | non_x_loc
-------+-----------
      0 |         1
      2 |         0
(2 rows)

It is 2 rows output.


then don't include the group by (which forces 1 row per location)

select sum(case WHEN lower(location) = 'x' THEN 1 else 0 end) AS x_loc, sum(case WHEN lower(location) != 'x' THEN 1 else 0 end) AS not_x_loc
from people;


Roxanne


Also, if performance is not a big concenr, you can define a little function, which I find makes the queries easier to read:

CREATE OR REPLACE FUNCTION or_null( boolean ) RETURNS boolean AS $$

     SELECT CASE WHEN $1 THEN TRUE ELSE NULL END;

$$ LANGUAGE sql IMMUTABLE;


select count(or_null(lower(location) = 'x')) AS x_loc, count(or_null(lower(location) != 'x'))) AS not_x_loc
from people;

Cheers,
Ken




--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.