Relational Database Management Systems - Design and Use of Relational Databases in Chemistry - page 33

to do except for a large and complex database application. But another
difference requires more careful consideration.
In Oracle, the string data type requires a specification of the maxi-
mum length of the text, for example varchar2(1000) . This is possible,
but unnecessary in PostgreSQL where the string data types can be of
unlimited size. Depending on the point of view, it may seem liberating to
be unconcerned about maximum text length, or it may seem dangerous to
allow text of arbitrary length. Another consideration in using the text data
type is how the null value is treated.
In standard SQL, a text column may contain data, or not. The null
value is used to denote that no value is contained in that colum in of any par-
ticular row. The null value can be used in SQL statements, for example:
Select smiles from nci.structure where name is null;
In Oracle, an empty text string is considered equivalent to the null
value. In PostgreSQL, an empty string is a valid, nonnull value. Neither
approach is more correct than the other, but it may require some program-
ming adjustments when moving from PostgreSQL to Oracle, or the other
way around.
Aside from standard SQL, RDBMS typically include a procedural lan-
guage that builds upon SQL. Oracle calls this plsql. PostgreSQL calls this
plpgsql. These languages include loops and conditionals that are not part of
the SQL standard language. Unfortunately, the differences among the pro-
cedural languages are much greater than the differences among the various
RDBMS implementations of the SQL standard language. One advantage
offered by PostgreSQL is a wider variety of procedural languages, namely
plpython, plperl, and pltcl. In addition, PostgreSQL allows the SQL lan-
guage to be extended using C language functions. These functions operate
just like internal standard SQL functions. The standard internal function
library is a rich source of coding examples for user-defined C functions. 8
4.4 Server and Client
The RDBMS is installed and runs on a computer that functions as a
database server. Any SQL commands are executed on the server by the
RDBMS. Functions written in SQL or in any of the procedural languages
mentioned above are also executed by the RDBMS. This has the advantage
that the data tables used by these SQL commands or procedural functions
are under the control of the server. This is the most efficient way to access
the data. The disadvantage is that the server may have many requests
to handle from many users. Another way to operate on data tables is
indirectly, using a client program typically (although not necessarily) run
from another computer.