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

Re: table inheritance

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Shuying Wang <wang(dot)shuying(dot)lists(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: table inheritance
Date: 2006-02-22 05:56:56
Message-ID: 20060222055656.GA98618@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Feb 22, 2006 at 04:39:12PM +1100, Shuying Wang wrote:
> I've got table x and table y which inherits table x. I want to get
> items from table x which are not in table y. According to the
> PostgreSQL documentation, the syntax would be "select * FROM ONLY x"
> however this yields no rows, whereas something like "select * from x
> where id not in (select id from y) " gives me what I want. Could
> someone explain to me what I'm doing wrong using ONLY?

Do any other tables inherit x?  What's the output of the following
query?

SELECT tableoid::regclass, * FROM x WHERE id NOT IN (SELECT id FROM y);

Could you show a simple but complete test case?  It works fine here
in 8.1.3:

CREATE TABLE x (id integer);
CREATE TABLE y () INHERITS (x);

INSERT INTO x VALUES (1);
INSERT INTO x VALUES (2);
INSERT INTO y VALUES (3);
INSERT INTO y VALUES (4);

SELECT * FROM ONLY x;
 id 
----
  1
  2
(2 rows)

SELECT * FROM x;
 id 
----
  1
  2
  3
  4
(4 rows)

-- 
Michael Fuhr

In response to

Responses

pgsql-novice by date

Next:From: Shuying WangDate: 2006-02-22 06:19:57
Subject: Re: table inheritance
Previous:From: Shuying WangDate: 2006-02-22 05:39:12
Subject: table inheritance

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