Skip site navigation (1) Skip section navigation (2)

switching from mysql

From: Brad Hilton <bhilton(at)vpop(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: switching from mysql
Date: 2001-11-16 01:16:08
Message-ID: 1005873368.28689.13.camel@aragorn (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-11-16 04:55:02
Subject: Re: PostgreSQL performance deteriorates over time?
Previous:From: Josh BerkusDate: 2001-11-16 00:22:06
Subject: Re: java and postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group