Thread: switching from mysql
Hello,
I am in the process of trying to migrate a project from mysql to
postgres and I am running into some problems with a few queries. I am
hoping that someone can offer help.
The first query type is related to GROUP BY. If I have a table:
CREATE TABLE items (
id int primary key,
name varchar(30)
);
and 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.
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?
A real-world example would be:
CREATE TABLE items (
id int primary key,
name varchar(30)
);
CREATE TABLE store_items (
item_id int,
store_id int,
PRIMARY KEY (item_id, store_id)
);
SELECT items.* FROM items, store_items
WHERE
items.id = store_items.item_id
GROUP BY items.id
In postgres I can't do this. Does anyone have a helpful alternative?
--------------
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
Can anyone help explain to me why this would work on MySQL and not on
Postgres? Also, can you provide a working query string?
Many thanks,
-Brad
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
On Thu, 2001-11-15 at 22:16, Tom Lane wrote: > 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. The example I gave was a simple one to demonstrate my problem. Let me give a better example. Let's say I have 3 tables: items, stores, and store_items. 'store_items' maps items to stores. If I want to select all items that are in at least one store, I currently do: select items.* from items, store_items where items.id = store_items.item_id group by items.id Is there a better way to do this query? > 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) > Thank you for your help! -Brad
On 16 Nov 2001, Brad Hilton wrote:
> The example I gave was a simple one to demonstrate my problem. Let me
> give a better example. Let's say I have 3 tables: items, stores, and
> store_items. 'store_items' maps items to stores. If I want to select
> all items that are in at least one store, I currently do:
>
> select items.* from items, store_items where items.id =
> store_items.item_id group by items.id
>
> Is there a better way to do this query?
SELECT DISTINCT ON (items.id) items.* FROM items
JOIN store_items ON store_items.id = items.id
regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de> /| \__
Network admin, systems programmer /_|____\
_/\ | __)
$ ./configure \\ \|__/__|
checking whether build environment is sane... yes \\/___/ |
checking for AIX... no (we already did this) |
Brad,
> The example I gave was a simple one to demonstrate my problem. Let
> me
> give a better example. Let's say I have 3 tables: items, stores, and
> store_items. 'store_items' maps items to stores. If I want to
> select
> all items that are in at least one store, I currently do:
>
> select items.* from items, store_items where items.id =
> store_items.item_id group by items.id
>
> Is there a better way to do this query?
In postgreSQL, you would use SELECT DISTINCT ON:
SELECT DISTINCT ON items.id *
FROM items JOIN store_items ON items.id = store_items.item_id;
However, you should consider that neither MySQL's implementation of
GROUP BY, nor SELECT DISTINCT ON, is ANSI-compliant SQL. Therefore
portablility of either approach is limited.
-Josh
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
> However, you should consider that neither MySQL's implementation of
> GROUP BY, nor SELECT DISTINCT ON, is ANSI-compliant SQL. Therefore
> portablility of either approach is limited.
A spec-compliant way to do it (assuming I've correctly understood
Brad's problem) is:
select items.* from items where
exists(select 1 from store_items where items.id = store_items.item_id);
However this has got performance problems in current Postgres releases
(soluble problems, but not solved yet), and I'm not sure if it works
at all in current MySQL releases --- I know they're working on subselect
support, but not sure if it's in any stable release yet.
So you're kinda stuck with one of the nonstandard approaches :-(
regards, tom lane