Hi Abhinandan,
I suppose you mean this:
CREATE TABLE nav (
name varchar NOT NULL,
attribute text NOT NULL,
value numeric );
ALTER TABLE ONLY nav ADD CONSTRAINT nav_pkey PRIMARY KEY (name, attribute);
insert into nav values ('James','Weight',70);
insert into nav values ('James','Height',165);
ins
There are two problems with the OTLT approach (as well as EAV). One is
laid out nicely by Tony.
The second issue is that this big, generic table hides crucial
information from the optimizer. If you cluster/order the data by the
lookup type you can at least minimize page/block reads and improve
dat
I have not.
I've already skimmed through it.
Indeed, it is very interesting
Thanx , Scott
Best,
Oliver
- Original Message -
From: "Scott Swank"
To: "Oliveiros d'Azevedo Cristina"
Cc: "Abhinandan Raghavan" ;
Sent: Tuesday, December 06, 2011 5:17 PM
Subject: Re: [SQL] Self-Join
Have you read Tony Andrew's 2004 piece on this approach? It is a classic.
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
Scott
On Tue, Dec 6, 2011 at 6:39 AM, Oliveiros d'Azevedo Cristina
wrote:
> Howdy, Abhinandan,
>
> A quick and dirty solution might be this
Howdy, Abhinandan,
A quick and dirty solution might be this :
SELECT *
FROM
(
SELECT a.name,MAX(b.value) as height
FROM original a
LEFT JOIN original b
ON a.name = b.name
AND b.attribute = 'Height'
GROUP BY a.name
) height
NATURAL JOIN
(
SELECT a.name,MAX(b.value) as weigth
FROM original a
LEFT
Hi Abhinandan,
it's just the same outer join you'd do in Oracle, see:
http://www.postgresql.org/docs/9.1/static/tutorial-join.html
Bèrto
On 6 December 2011 16:57, Abhinandan Raghavan
wrote:
> Hi,
>
> I'm looking to frame an SQL statement in Postgres for what's explained in
> the attached imag
Hi,
I'm looking to frame an SQL statement in Postgres for what's explained
in the attached image.
The original table is at the top and is called NAV (Short for Name,
Attribute, Value). I want to create a view (NWHA_View) involving values
from within (presumably from a self join). I would've
