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

Why is PostgreSQL 7.0 SQL semantics different from Oracle's?

From: Thomas Holmgren <thm(at)cs(dot)auc(dot)dk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
Date: 2000-05-24 17:58:59
Message-ID: Pine.GSO.4.21.0005241958440.28326-100000@luke.cs.auc.dk (view raw or flat)
Thread:
Lists: pgsql-sql
Hello everyone! :)

This little problem is bothering me a lot! It seems that PostgreSQL 7.0
uses different semantics than Oracle when evaluting SQL?!

I have two relations, A and B, both containing the attributes "number" 
(int) and "amount" (int). There's no primary key, and the two relations
can contain multiple identical tuples.

I would like to query for a table containing the total amount for each
different number in B, substracted from the total amount for each
different number in A. In other words, sum A by grouping "number", sum B 
by grouping "number" and finaly calculate the difference between the sums
for each "number".

I have defined two views, viewA and viewB. They are defined as follow:

CREATE VIEW viewA AS SELECT number, sum(amount) AS amount
FROM A GROUP BY number;

CREATE VIEW viewB AS SELECT number, sum(amount) AS amount
FROM B FROUP BY number;

This query then gives me the desired result (when I'm using Oracle):

SELECT viewA.number, viewA.amount - viewB.amount AS difference
FROM viewA, viewB
WHERE viewA.number = viewB.number

BUT WHEN I'm doing the EXACT SAME THING in PostgreSQL 7.0 I get a
different result! It seems that Postgres executes the natural join in the
query BEFORE performing the sum() in the definition of the views thus
giving me a wrong result.

How can I fix that??
How come PostgreSQL uses different semantics when evaluating SQL
expressions than other BDMSs?

Thank you! :))


Mvh.
Thomas Holmgren
Institut for Datalogi
Aalborg Universitet


Responses

pgsql-sql by date

Next:From: Kees KuipDate: 2000-05-24 19:38:26
Subject: Re: Automatic index numbers
Previous:From: Michael MaDate: 2000-05-24 17:21:11
Subject: Re: Unable to identify a right operator '>=' for type 'bpchar' (solved)

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