3
Oracle PostgreSQL
Oracle relational operators may have a space between the characters. For
example, the following select will work:
select id,name from employee where id > = 10;
// There are spaces and tabs between “>” and “=”
PostgreSQL relational operators doesn’t allow spaces, the characters that
compound an operator must be consecutive when the command is
parsed:
select id,name from employee where id >= 10;
To get the remainder of the division of 10 by 4 (modulo) use the “mod”
function:
select mod(10,4) from dual;
To get the remainder of the division of 10 by 4 (modulo)
use the “%” operator. (And PostgreSQL has many other arithmetic
operators.)
select 10 % 4;
The “ROWNUM” pseudo-column returns a number indicating the order
in which Oracle selects the row.
ROWNUM can be used to limit the number of rows returned by a query,
for example:
select * from employees where rownum < 10 order by name;
ROWNUM can be used in the projection as one of the values returned by
the query (first line has value 1, second line value 2, and so on):
select rownum, name from employees order by name;
There isn’t anything equivalent to Oracle ROWNUM.
However, you can limit the number of rows returned by a query using the
“LIMIT” clause:
select * from employees order by name limit 10;
In some cases, it’s possible that the pseudo-column OID may substitute
ROWNUM, although they have different behavior. OID is a unique
identifier of each line per table, while ROWNUM is always 1, 2, …, N
for each different query.
select oid, name from employees order by name;
And the query that uses ROWNUM can have join tables. If your select is
a join you’ll have a different OID for each table, because each one has an
OID column.
2. Database Server General Characteristics
Oracle PostgreSQL
A view can be “updatable” if some conditions are satisfied. Views are read only.