From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Brad Hilton <bhilton(at)vpop(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: switching from mysql |
Date: | 2001-11-16 06:16:27 |
Message-ID: | 23686.1005891387@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Brad Hilton <bhilton(at)vpop(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Colin Barnard | 2001-11-16 14:26:57 | Migrating db from Oracle to Postgresql |
Previous Message | Tom Lane | 2001-11-16 04:55:02 | Re: PostgreSQL performance deteriorates over time? |