PostgreSQL

Скачать в pdf «PostgreSQL»


1993-01-01 00:00:00-05 (l row)


test=> SELECT CAST(‘l/l/l992’ AS TIMESTAMP) + ‘l year’; ?column?


1993-01-01 00:00:00-05 (l row)


Figure 9.3: Error generated by undefined operator/type combination


Variable


Meaning


CURRENT_DATE


current date


CURRENT_TIME


current time


CURRENT TIMESTAMP


current date and time


CURRENTJJSER


user connected to the database

Table 9.5: Common variables


All data types support the standard comparison operators <, <=,=,>=, >, and <>. Not all operator/type combinations are defined, however. For example, if you try to add two date values, you will get an error, as shown in the first query of Figure 9.3.

9.6 Support Variables


Several variables are defined in PostgreSQL. These variables are shown in Table
9.5.


test=> CREATE TABLE array test


(


test(>


coll


INTEGER[5],


test(>


col2


INTEGER[][],


test(>


col3


INTEGER[2][2][]


test(> );


CREATE

Figure 9.4: Creation of array columns

9.7    Arrays


Arrays allow a column to store several simple data values. You can store one-dimensional arrays, two-dimensional arrays, or arrays with any number of dimensions.


You create an array column in the same way as an ordinary column, except that you use brackets to specify the dimensions of the array. The number of dimensions and size of each dimension are for documentation purposes only. Values that do not match the dimensions specified at the time of column creation are not rejected.


Figure
9.4 creates a table with one-, two-, and three-dimensional integer columns. The first and last columns have sizes specified. The first column is a one-dimensional array, also called a list or vector. Values inserted into that column have an appearance like {3,10,9,32,24} or {20,8,9,1,4}. That is, each value is a list of integers, surrounded by curly braces. The second column, co12, is a two-dimensional array. Typical values for this column are {{2,9,3},{4,3,5}} or {{18,6},{32,5}}. Notice the double braces. The outer brace surrounds two one-dimensional arrays. You can think of this structure as a matrix, with the first one-dimensional array representing the first row of the array, and the second representing the second row of the array. Commas separate the individual elements as well as each pair of braces. The third column of the arrayjest table is a threedimensional array, holding values like {{{3,T},{1,9Y},{{4,5},{8,2Y}}. This three-dimensional matrix is made up of two 2×2 matrices. Arrays of any size can be constructed.

Скачать в pdf «PostgreSQL»