This week I have been to Southern Germany. This customer has sucessfully
migrated from Oracle to PostgreSQL. With the help of a gborg package
(Oracle style data dictionary) and CREATE DOMAIN they were able to run
there current application WITHOUT modification.
There was just one thing we have encountered:
SELECT * FROM X MINUS SELECT * FROM X
does not work on PostgreSQL (we use EXCEPT instead).
This small patch fixes this problem.
Maybe it improvements can be integrated into 7.6
Regards
Hans
*** ./doc/src/sgml/keywords.sgml.orig 2004-07-08 21:11:29.694379616 +0200
--- ./doc/src/sgml/keywords.sgml 2004-07-08 21:11:12.583980792 +0200
***************
*** 1785,1790 ****
--- 1785,1796 ----
<entry>reserved</entry>
</row>
<row>
+ <entry><token>MINUS</token></entry>
+ <entry>reserved</entry>
+ <entry>reserved</entry>
+ <entry>reserved</entry>
+ </row>
+ <row>
<entry><token>MINUTE</token></entry>
<entry>non-reserved</entry>
<entry>reserved</entry>
*** ./doc/src/sgml/ref/select_into.sgml.orig 2004-07-08 21:15:47.767146592 +0200
--- ./doc/src/sgml/ref/select_into.sgml 2004-07-08 21:16:17.406640704 +0200
***************
*** 27,33 ****
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable
class="PARAMETER">operator</replaceable>] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
--- 27,33 ----
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable
class="PARAMETER">operator</replaceable>] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
*** ./doc/src/sgml/ref/select.sgml.orig 2004-07-08 21:12:47.145605240 +0200
--- ./doc/src/sgml/ref/select.sgml 2004-07-08 21:15:17.869691696 +0200
***************
*** 26,32 ****
[ WHERE <replaceable class="parameter">condition</replaceable> ]
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable
class="parameter">operator</replaceable>] [, ...] ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> ]
--- 26,32 ----
[ WHERE <replaceable class="parameter">condition</replaceable> ]
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] <replaceable class="parameter">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable
class="parameter">operator</replaceable>] [, ...] ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> ]
***************
*** 92,99 ****
<literal>INTERSECT</literal> operator returns all rows that are
strictly in both result sets. The <literal>EXCEPT</literal>
operator returns the rows that are in the first result set but
! not in the second. In all three cases, duplicate rows are
! eliminated unless <literal>ALL</literal> is specified. (See
<xref linkend="sql-union" endterm="sql-union-title">, <xref
linkend="sql-intersect" endterm="sql-intersect-title">, and
<xref linkend="sql-except" endterm="sql-except-title"> below.)
--- 92,102 ----
<literal>INTERSECT</literal> operator returns all rows that are
strictly in both result sets. The <literal>EXCEPT</literal>
operator returns the rows that are in the first result set but
! not in the second. <literal>MINUS</literal> behaves the same way
! as <literal>EXCEPT</literal>. <literal>MINUS</literal> should
! only be used for Oracle compatibility. In all three cases,
! duplicate rows are eliminated unless <literal>ALL</literal>
! is specified. (See
<xref linkend="sql-union" endterm="sql-union-title">, <xref
linkend="sql-intersect" endterm="sql-intersect-title">, and
<xref linkend="sql-except" endterm="sql-except-title"> below.)
*** ./src/backend/parser/gram.y.orig 2004-07-08 20:25:04.559784432 +0200
--- ./src/backend/parser/gram.y 2004-07-08 20:36:16.820585256 +0200
***************
*** 371,377 ****
LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
LOCK_P
! MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
--- 371,377 ----
LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
LOCK_P
! MATCH MAXVALUE MINUS MINUTE_P MINVALUE MODE MONTH_P MOVE
NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
***************
*** 422,428 ****
%token <ival> ICONST PARAM
/* precedence: lowest to highest */
! %left UNION EXCEPT
%left INTERSECT
%left OR
%left AND
--- 422,428 ----
%token <ival> ICONST PARAM
/* precedence: lowest to highest */
! %left UNION EXCEPT MINUS
%left INTERSECT
%left OR
%left AND
***************
*** 4799,4804 ****
--- 4799,4808 ----
{
$$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
}
+ | select_clause MINUS opt_all select_clause
+ {
+ $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
+ }
;
into_clause:
***************
*** 7877,7882 ****
--- 7881,7887 ----
| LIMIT
| LOCALTIME
| LOCALTIMESTAMP
+ | MINUS
| NEW
| NOT
| NOWAIT
*** ./src/backend/parser/keywords.c.orig 2004-07-08 21:08:24.252571056 +0200
--- ./src/backend/parser/keywords.c 2004-07-08 21:08:48.524881104 +0200
***************
*** 195,200 ****
--- 195,201 ----
{"lock", LOCK_P},
{"match", MATCH},
{"maxvalue", MAXVALUE},
+ {"minus", MINUS},
{"minute", MINUTE_P},
{"minvalue", MINVALUE},
{"mode", MODE},
*** ./src/bin/psql/sql_help.h.orig 2004-07-08 21:26:33.987906104 +0200
--- ./src/bin/psql/sql_help.h 2004-07-08 21:27:58.389075176 +0200
***************
*** 367,377 ****
{ "SELECT",
N_("retrieve rows from a table or view"),
! N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n * | expression [ AS output_name ] [, ...]\n
[FROM from_item [, ...] ]\n [ WHERE condition ]\n [ GROUP BY expression [, ...] ]\n [ HAVING condition [, ...]
]\n [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]\n [ ORDER BY expression [ ASC | DESC | USING operator ] [,
...]]\n [ LIMIT { count | ALL } ]\n [ OFFSET start ]\n [ FOR UPDATE [ OF table_name [, ...] ] ]\n\nwhere
from_itemcan be one of:\n\n [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n ( select )
[AS ] alias [ ( column_alias [, ...] ) ]\n function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [,
...]| column_definition [, ...] ) ]\n function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )\n
from_item[ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]") },
{ "SELECT INTO",
N_("create a new table from the results of a query"),
! N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n * | expression [ AS output_name ] [, ...]\n
INTO[ TEMPORARY | TEMP ] [ TABLE ] new_table\n [ FROM from_item [, ...] ]\n [ WHERE condition ]\n [ GROUP BY
expression[, ...] ]\n [ HAVING condition [, ...] ]\n [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]\n [
ORDERBY expression [ ASC | DESC | USING operator ] [, ...] ]\n [ LIMIT { count | ALL } ]\n [ OFFSET start ]\n
[FOR UPDATE [ OF tablename [, ...] ] ]") },
{ "SET",
N_("change a run-time parameter"),
--- 367,377 ----
{ "SELECT",
N_("retrieve rows from a table or view"),
! N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n * | expression [ AS output_name ] [, ...]\n
[FROM from_item [, ...] ]\n [ WHERE condition ]\n [ GROUP BY expression [, ...] ]\n [ HAVING condition [, ...]
]\n [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] select ]\n [ ORDER BY expression [ ASC | DESC | USING operator
][, ...] ]\n [ LIMIT { count | ALL } ]\n [ OFFSET start ]\n [ FOR UPDATE [ OF table_name [, ...] ] ]\n\nwhere
from_itemcan be one of:\n\n [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n ( select )
[AS ] alias [ ( column_alias [, ...] ) ]\n function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [,
...]| column_definition [, ...] ) ]\n function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )\n
from_item[ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]") },
{ "SELECT INTO",
N_("create a new table from the results of a query"),
! N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n * | expression [ AS output_name ] [, ...]\n
INTO[ TEMPORARY | TEMP ] [ TABLE ] new_table\n [ FROM from_item [, ...] ]\n [ WHERE condition ]\n [ GROUP BY
expression[, ...] ]\n [ HAVING condition [, ...] ]\n [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] select ]\n
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]\n [ LIMIT { count | ALL } ]\n [ OFFSET start ]\n
[ FOR UPDATE [ OF tablename [, ...] ] ]") },
{ "SET",
N_("change a run-time parameter"),
