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
