PostgreSQL Extensions - Design and Use of Relational Databases in Chemistry - page 115

10.4 Array Data Types for Two- and
Three-Dimensional Coordinates
For every standard SQL data type available in PostgreSQL, there is a cor-
responding array data type. While it is possible to define a composite data
type for coordinates, consider using the array data type. For example:
Create table ctest (smiles text, name text, coord float[][3]);
The column coord is a two-dimensional array of float values. The exter-
nal representation of array data uses curly braces. For example:
Insert Into coordtest (smiles,name,coord) Values
('CN1C=C(C)C(=O)NC1=O','1-methylthymine', '{
{-0.5223,-1.2374,0.2579},
{-1.8677,-1.3917,0.1177},
{-2.7245,-0.3893,-0.2291},
{-2.2127,0.8622,-0.4679},
{-0.8652,1.0783,-0.3284},
{0.0270,0.0456,0.0406},
{-3.9604,-0.6494,-0.3168},
{0.1995,-2.2271,0.5751},
{1.4782,0.3157,0.1674},
{-3.0687,1.9631,-0.8370}}');
The first dimension refers to the atom number and the second dimension refers
to the Cartesian coordinates of the atom. So, the following SQL would select
the smiles and the coordinates of the first two atoms of 1-methylthymine.
Select smiles, coord[1:2] From coordtest Where name='1-methylthymine';
Arrays are indexed starting with 1, rather than with 0 as is done in some
computer languages. Notice that there are no array upper bounds speci-
fied for the first dimension of coords in the table creation. This allows each
row to have a different number of atoms. The array _ upper function
can be used to return the actual dimension used in any array.
The array _ upper function requires two arguments: the name of the
array and the index for which the upper limit is requested. The function call
array _ upper(coord,1) would return the number of atoms. The func-
tion call array _ upper(coord,2) would return 3. Even though the sec-
ond index upper limit was specified as 3 in the table creation above, this was
done for clarity and because the array was intended to hold 3-D coordinates.
PostgreSQL does not enforce this upper limit. In fact, it would be possible to
insert two-dimensional coordinates into the coordtest table. However, it is
not allowed to mix two- and three-dimensional coordinates within any one
array. Once the first atoms coordinates are given the insert statement, each
succeeding atom must have the same dimensionality of coordinates.