Thread: table inheritance

Hi,

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?

Thanks in advance,
Shuying

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

Actually, it's my fault. It turns out that the parent table was empty
and there was some other table that had all the other data.

Thanks, that was a quick response.
--Shuying

On 2/22/06, Michael Fuhr <mike@fuhr.org> wrote:
> 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?