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

Re: switching from mysql

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Josh Berkus" <josh(at)agliodbs(dot)com>
Cc: Brad Hilton <bhilton(at)vpop(dot)net>, pgsql-novice(at)postgresql(dot)org
Subject: Re: switching from mysql
Date: 2001-11-16 19:35:22
Message-ID: 9314.1005939322@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Josh Berkus" <josh(at)agliodbs(dot)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

In response to

pgsql-novice by date

Next:From: Francisco ReyesDate: 2001-11-18 04:24:12
Subject: Drop table vs begin/end transaction
Previous:From: Josh BerkusDate: 2001-11-16 18:30:42
Subject: Re: switching from mysql

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