Re: table inheritance

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