Re: [SQL] Aggregates in WHERE clause?

[Tom Lane]

"Richard Broersma" <[EMAIL PROTECTED]> writes:
> On Wed, Sep 10, 2008 at 4:11 PM, Ruben Gouveia <[EMAIL PROTECTED]> wrote:
>> ERROR:  aggregates not allowed in WHERE clause

> Having clause works on agregates.  If you want to force it in the
> where, you need to put the groupby in a sub-query.

The way I like to think about it is "WHERE filters rows before aggregate
functions are computed; HAVING filters them afterwards".  Seen in those
terms, it's obvious why WHERE can't contain any aggregates.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Aggregates in WHERE clause?

[Igor Neyman]

select employee,count(distinct tasks),
greatest(max(last_job_date),max(last_position_date)) 
from job
group by employee
having greatest(max(last_job_date),max(last_position_date)) <
2008-08-28 + integer '1';





From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ruben Gouveia
Sent: Wednesday, September 10, 2008 7:11 PM
To: pgsql-sql
Subject: [SQL] Aggregates in WHERE clause?


I tried to do the following and got the following error message:

select employee,count(distinct tasks)
from job
where greatest(max(last_job_date),max(last_position_date)) <
2008-08-28 + integer '1'
group by employee;

ERROR:  aggregates not allowed in WHERE clause

** Error **

ERROR: aggregates not allowed in WHERE clause
SQL state: 42803

Is there away around this? Should i create a function to populate a
variable that can be used in it's place...will that even work?

Re: [SQL] pg_restore in java connection (auto_commit = false)

[Emi Lu]

> See:
> http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html
>
> where it says that ALTER TABLE obtains the ACCESS EXCLUSIVE lock. You
> can confirm this by issuing the command of interest then running:
>
>   SELECT * from pg_catalog.pg_locks;
>
> With that transaction still open, and that lock still held, you then
> execute a new process (pg_restore) that establishes its own unrelated
> connection to the database and tries to get a ROW EXCLUSIVE lock (if
> using INSERT, and presumably COPY though the docs don't say so) on the
> table. It can't do so, because your Java program holds an ACCESS
> EXCLUSIVE lock on the table that conflicts with the requested lock mode.
>
> Your java code won't release the lock until pg_restore finishes, and
> pg_restore won't finish until your java code releases the lock.
>
> Deadlock.
>
>
> There is no way you can "pass" your connection to pg_restore when you
> invoke it from Java. Thus, you must either not hold any locks that would
> prevent pg_restore from acting on the table, or you must do all the work
> within Java using your existing JDBC connection.

This is exactly what I was trying to make it clear to myself.
Thank you Craig!


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] DIfference between max() and greatest() ?

[Ruben Gouveia]

What is the difference between these two. I know that max() is an aggregate
function

select model,count(distinct cars)
from rc_cars
where greatest(baught,returned) < current_date - integer '1'
group by model;

Do i need to have a max () around a greatest() to make sure i get the most
recent of the two.

select model,count(distinct cars)
from rc_cars
where max(greatest(baught,returned)) < current_date - integer '1'
group by model;