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

Re: Thoughts about updateable views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Yann Michel <yann-postgresql(at)spline(dot)de>,Jaime Casanova <systemguards(at)yahoo(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts about updateable views
Date: 2004-12-22 16:54:56
Message-ID: 4826.1103734496@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Richard Huxton <dev(at)archonet(dot)com> writes:
> Yann Michel wrote:
>> I think you mean UNION ALL, i.e. the set addition, don't you?

> Not if you can identify the underlying table(s) and key(s). If the UNION 
> hides that information, then you are correct.

If a unique key of the underlying table is included in the UNION data, then
there can't be any duplicate rows and so the UNION really reduces to
UNION ALL.  However, I'm unconvinced that there are any cases like this
that are interesting in practice.  Consider

	CREATE TABLE a (id int primary key, ...);

	CREATE TABLE b (id int primary key, ...);

	CREATE VIEW v AS SELECT * FROM a UNION SELECT * FROM b;

If a and b have disjoint key sets then the UNION is theoretically
updatable, but there is no way to specify such a constraint and thus
no way for the system to know that the UNION is updatable.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Richard HuxtonDate: 2004-12-22 16:58:01
Subject: Re: Thoughts about updateable views
Previous:From: Bernd HelmleDate: 2004-12-22 16:49:58
Subject: Re: Thoughts about updateable views

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