Thread: No DIVIDE Operator
Hi! Consider the following szenario: Two Tables: create table Person ( name text, prename text, street text, town text ); create table Officer ( name text, prename text, bureau text ); So, every Officer is a Person. Now, I would like to get all persons except for Officers. If there would be a divide operator, I could write select name, prename from Person divide select name, prename from Officer; without it, however, things get really complicated: select name, prename from Person where prename not in ( select prename from Officer where Officer.name = Person.name ); This is an easy example. However, I have to deal with about 10 tables in a corresponding way. Any better idea? Will there be a divide operator in PGSQL soon? Thanks! Regards Joerg Fischer
Isn't that what EXCEPT does? chris=> create table foo (text text); CREATE chris=> insert into foo values('aaa'); INSERT 22153 1 chris=> insert into foo values('bbb'); INSERT 22154 1 chris=> insert into foo values('ccc'); INSERT 22155 1 chris=> select * from foo EXCEPT select 'aaa'; text ---- bbb ccc (2 rows) > If there would be a divide operator, I could write > > select name, prename from Person divide select name, prename from > Officer; > > without it, however, things get really complicated: > > select name, prename from Person where prename not in ( select prename > from Officer where Officer.name = Person.name ); > > This is an easy example. However, I have to deal with about 10 tables in > a corresponding way. > > Any better idea? Will there be a divide operator in PGSQL soon? > > Thanks! > > Regards > > Joerg Fischer -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
Chris Bitmead wrote: > > Isn't that what EXCEPT does? Maybe the operator is named differently between different database systems. Anyway, as far as I am concerned, PGSQL lacks it. Or (hopefully), am I wrong?? Regards Joerg Fischer > chris=> create table foo (text text); > CREATE > chris=> insert into foo values('aaa'); > INSERT 22153 1 > chris=> insert into foo values('bbb'); > INSERT 22154 1 > chris=> insert into foo values('ccc'); > INSERT 22155 1 > chris=> select * from foo EXCEPT select 'aaa'; > text > ---- > bbb > ccc > (2 rows) > > > If there would be a divide operator, I could write > > > > select name, prename from Person divide select name, prename from > > Officer; > > > > without it, however, things get really complicated: > > > > select name, prename from Person where prename not in ( select prename > > from Officer where Officer.name = Person.name ); > > > > This is an easy example. However, I have to deal with about 10 tables in > > a corresponding way. > > > > Any better idea? Will there be a divide operator in PGSQL soon? > > > > Thanks! > > > > Regards > > > > Joerg Fischer
Joerg Fischer wrote: > > Chris Bitmead wrote: > > > > Isn't that what EXCEPT does? > > Maybe the operator is named differently between different database > systems. Anyway, as far as I am concerned, PGSQL lacks it. Or > (hopefully), am I wrong?? Well Postgres has EXCEPT. Is that what you want? > > Regards > > Joerg Fischer > > > chris=> create table foo (text text); > > CREATE > > chris=> insert into foo values('aaa'); > > INSERT 22153 1 > > chris=> insert into foo values('bbb'); > > INSERT 22154 1 > > chris=> insert into foo values('ccc'); > > INSERT 22155 1 > > chris=> select * from foo EXCEPT select 'aaa'; > > text > > ---- > > bbb > > ccc > > (2 rows) > > > > > If there would be a divide operator, I could write > > > > > > select name, prename from Person divide select name, prename from > > > Officer; > > > > > > without it, however, things get really complicated: > > > > > > select name, prename from Person where prename not in ( select prename > > > from Officer where Officer.name = Person.name ); > > > > > > This is an easy example. However, I have to deal with about 10 tables in > > > a corresponding way. > > > > > > Any better idea? Will there be a divide operator in PGSQL soon? > > > > > > Thanks! > > > > > > Regards > > > > > > Joerg Fischer -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
> Joerg Fischer wrote: > > > > Chris Bitmead wrote: > > > > > > Isn't that what EXCEPT does? > > > > Maybe the operator is named differently between different database > > systems. Anyway, as far as I am concerned, PGSQL lacks it. Or > > (hopefully), am I wrong?? > > Well Postgres has EXCEPT. Is that what you want? I think we only got EXCEPT in 6.5 beta, so he may not have it yet. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Chris Bitmead wrote: > > Joerg Fischer wrote: > > > > Chris Bitmead wrote: > > > > > > Isn't that what EXCEPT does? > > Maybe the operator is named differently between different database > > systems. Anyway, as far as I am concerned, PGSQL lacks it. Or > > (hopefully), am I wrong?? > > Well Postgres has EXCEPT. Is that what you want? AFAIK, EXCEPT is included in version 6.5beta1. Monday, I installed that one. Now it works! Great! Thanks! Regards Joerg Fischer