Thread: Oracle MINUS is not supported
Hello, Oracle has MINUS keyword used instead of EXCEPT. I know that this sucks, but I like sometimes to test Oracle queries on PostgreSQL (I should use Oracle...). Here is a patch to make minus working same way as except. I am not sure it is complety correct (I am not very big guru in grammars... Just wrote draft version of Oberon-2 -> C++ translator). Please someone more expirienced check that all is fine. BTW, I am not ask for inclusion in current tree. I just create a patch, and if someone need it it can be picked up. -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
Attachment
Ultimlately, what we would like is a pre-grammar to convert Oracle-isms to PostgreSQL/ANSI. [ Charset KOI8-R unsupported, converting... ] > Hello, > > Oracle has MINUS keyword used instead of EXCEPT. > I know that this sucks, but I like sometimes to test Oracle queries on > PostgreSQL (I should use Oracle...). > > Here is a patch to make minus working same way as except. I am not sure it is > complety correct (I am not very big guru in grammars... Just wrote draft > version of Oberon-2 -> C++ translator). Please someone more expirienced check > that all is fine. > > BTW, I am not ask for inclusion in current tree. I just create a patch, and > if someone need it it can be picked up. > > -- > Sincerely Yours, > Denis Perchine > > ---------------------------------- > E-Mail: dyp@perchine.com > HomePage: http://www.perchine.com/dyp/ > FidoNet: 2:5000/120.5 > ---------------------------------- [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Saturday 10 March 2001 21:09, Bruce Momjian wrote: > Ultimlately, what we would like is a pre-grammar to convert Oracle-isms > to PostgreSQL/ANSI. Not sure that I get what do you mean? Could you please be more verbose. > [ Charset KOI8-R unsupported, converting... ] > > > Hello, > > > > Oracle has MINUS keyword used instead of EXCEPT. > > I know that this sucks, but I like sometimes to test Oracle queries on > > PostgreSQL (I should use Oracle...). > > > > Here is a patch to make minus working same way as except. I am not sure > > it is complety correct (I am not very big guru in grammars... Just wrote > > draft version of Oberon-2 -> C++ translator). Please someone more > > expirienced check that all is fine. > > > > BTW, I am not ask for inclusion in current tree. I just create a patch, > > and if someone need it it can be picked up. > > > > -- > > Sincerely Yours, > > Denis Perchine > > > > ---------------------------------- > > E-Mail: dyp@perchine.com > > HomePage: http://www.perchine.com/dyp/ > > FidoNet: 2:5000/120.5 > > ---------------------------------- > > [ Attachment, skipping... ] > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
Denis Perchine <dyp@perchine.com> writes:
> Oracle has MINUS keyword used instead of EXCEPT.
Doesn't Oracle also accept EXCEPT? They claim to be SQL-compliant ...
regards, tom lane
On Saturday 10 March 2001 22:18, Tom Lane wrote: > Denis Perchine <dyp@perchine.com> writes: > > Oracle has MINUS keyword used instead of EXCEPT. > > Doesn't Oracle also accept EXCEPT? They claim to be SQL-compliant ... Unfortunatly - no. At least 8.1.6 says very bad words about this... :-((( Also there is only MINUS in SQL reference SELECT grammar. I tried to execute SQL worked on PostgreSQL, and was forced to do 2 corrections: 1. Replace EXCEPT with MINUS 2. Remove AS after subquery for target column Both if I am not mistaken is ANSI/SQL. :-(( -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
> On Saturday 10 March 2001 21:09, Bruce Momjian wrote: > > Ultimlately, what we would like is a pre-grammar to convert Oracle-isms > > to PostgreSQL/ANSI. > > Not sure that I get what do you mean? Could you please be more verbose. Sorry, I have a bad habit of being too brief. :-) There have been requests to accept Oracle-specific grammar. Rather than adding complexity to our existing grammar, and perhaps having cases where we can't do things the ANSI way and the ORACLE way at the same time, we would rather have a pass over the queries before they reach our grammar. The pass would restructure any Oracle-specific syntax into PostgreSQL syntax. That allows us to have all the Oracle-specific stuff in the one place, and to turn it on and off as needed. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Sorry, I have a bad habit of being too brief. :-) > > There have been requests to accept Oracle-specific grammar. Rather than > adding complexity to our existing grammar, and perhaps having cases > where we can't do things the ANSI way and the ORACLE way at the same > time, we would rather have a pass over the queries before they reach our > grammar. The pass would restructure any Oracle-specific syntax into > PostgreSQL syntax. That allows us to have all the Oracle-specific stuff > in the one place, and to turn it on and off as needed. That what I thought... But this is not so easy... It is something like converting Oracle->Postgres on the fly... Hugh... For sure I will have no time for this in near future,,, :-(( -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> we would rather have a pass over the queries before they reach our
> grammar. The pass would restructure any Oracle-specific syntax into
> PostgreSQL syntax.
More specifically, a separate program to convert Oracle-isms into
Postgres-isms (and, perhaps, MySQL-isms, DB2-isms, ...). Feed it
a script, get a new script back.
Do we have a TODO list entry for this? I don't see it offhand.
I do see something about an SQL*Net listener, which would probably
need to run this sort of conversion on-the-fly, but ISTM the first
step ought to be a simple script converter.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > we would rather have a pass over the queries before they reach our
> > grammar. The pass would restructure any Oracle-specific syntax into
> > PostgreSQL syntax.
>
> More specifically, a separate program to convert Oracle-isms into
> Postgres-isms (and, perhaps, MySQL-isms, DB2-isms, ...). Feed it
> a script, get a new script back.
>
> Do we have a TODO list entry for this? I don't see it offhand.
> I do see something about an SQL*Net listener, which would probably
> need to run this sort of conversion on-the-fly, but ISTM the first
> step ought to be a simple script converter.
Added now:
* Convert non-Ansi feature to PostgreSQL features
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026