Re: switching from mysql - Mailing list pgsql-novice

From Tom Lane
Subject Re: switching from mysql
Date
Msg-id 23686.1005891387@sss.pgh.pa.us
Whole thread Raw
In response to switching from mysql  (Brad Hilton <bhilton@vpop.net>)
Responses Re: switching from mysql
List pgsql-novice
Brad Hilton <bhilton@vpop.net> writes:
> I issue
> select * from items GROUP BY id
> I get:
> "Attribute items.name must be GROUPed or used in an aggregate function"
> It appears in MySQL if you group on a unique key, then you aren't
> required to group on the rest of the fields you select from the table.

If you know it's a unique key, why are you bothering to GROUP?
If it's not a unique key, then there's no unique value for the other
columns.  Either way, I can see no sense to this query.  The SQL92
spec doesn't see any sense in it either.

> Postgres evidently doesn't work this way.  Is there any way to select
> all fields from a table without grouping on each of those fields if the
> group by field is a unique key?

Please define what answer you want, and then we can talk about how to
get it.


> The second problem is with LEFT JOIN.  Here's a sample query that works
> on Mysql but not on postgresql:

> select count(*) from a, b
> LEFT JOIN c on
>     c.foo = a.foo and
>     c.foo = b.foo
> where
> c.foo is not null and
> a.aid = b.bid

> This raises an error:

> ERROR:  JOIN/ON clause refers to "a", which is not part of JOIN

I believe MySQL thinks that this query means

    ... FROM (a CROSS JOIN b) LEFT JOIN c ON condition

which unfortunately for them is not what the SQL spec says the
syntax means.  The spec's interpretation is

    ... FROM a CROSS JOIN (b LEFT JOIN c ON condition)

from which the error complaint follows.

            regards, tom lane

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: PostgreSQL performance deteriorates over time?
Next
From: Jason Earl
Date:
Subject: Re: Perl and pgsql...