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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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?

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly