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, whole thread or download thread mbox)
Lists: pgsql-novice

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:

	id int primary key,
	name varchar(30)

and I issue

select * from items GROUP BY id

I get:

"Attribute 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:

	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 = store_items.item_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 = and =
where is not null and
a.aid =

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,


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-2017 The PostgreSQL Global Development Group