SQL Language
18
In HyperSQL 2, length, precision and scale qualifiers are always enforced. For backward compatibility, when older
databases which had the property hsqldb.enforce_strict_size=false are converted to version 2, this property is retained.
However, this is a temporary measure. You should test your application to ensure the length, precision and scale that
is used for column definitions is appropriate for the application data. You can test with the default database setting,
which enforces the sizes.
String types, including all BIT, BINARY and CHAR string types plus CLOB and BLOB, are generally defined with
a length. If no length is specified for BIT, BINARY and CHAR, the default length is 1. For CLOB and BLOB an
implementation defined length of 1M is used.
TIME and TIMESTAMP types can be defined with a fractional second precision between 0 and 9. INTERVAL type
definition may have precision and, in some cases, fraction second precision. DECIMAL and NUMERIC types may be
defined with precision and scale. For all of these types a default precision or scale value is used if one is not specified.
The default scale is 0. The default fractional precision for TIME is 0, while it is 6 for TIMESTAMP.
Values can be converted from one type to another in two different ways: by using explicit CAST expression or by
implicit conversion used in assignment, comparison and aggregation.
String values cannot  be assigned to VARCHAR columns if  they  are longer than the  defined type length. For
CHARACTER columns, a long string can be assigned (with truncation) only if all the characters after the length are
spaces. Shorter strings are padded with the space character when inserted into a CHARACTER column. Similar rules
are applied to VARBINARY and BINARY columns. For BINARY columns, the padding and truncation rules are
applied with zero bytes, instead of spaces.
Explicit CAST of a value to a CHARACTER or VARCHAR type will result in forced truncation or padding. So a test
such as CAST (mycol AS VARCHAR(2)) = 'xy' will find the values beginning with 'xy'. This is the equivalent
of SUBSTRING(mycol FROM 1 FOR 2)= 'xy'.
For all numeric types, the rules of explicit cast and implicit conversion are the same. If cast or conversion causes any
digits to be lost from the fractional part, it can take place. If the non-fractional part of the value cannot be represented
in the new type, cast or conversion cannot take place and will result in a data exception.
There are special rules for DATE, TIME, TIMESTAMP and INTERVAL casts and conversions.
Datetime types
HSQLDB fully supports datetime and interval types and operations, including all relevant optional features, as
specified by the SQL Standard since SQL-92. The two groups of types are complementary.
The DATE type represents a calendar date with YEAR, MONTH and DAY fields.
The TIME type represents time of day with HOUR, MINUTE and SECOND fields, plus an optional SECOND
FRACTION field.
The TIMESTAMP type represents the combination of DATE and TIME types.
TIME and TIMESTAMP types can include WITH TIME ZONE or WITHOUT TIME ZONE (the default) qualifiers.
They can have fractional second parts. For example, TIME(6) has six fractional digits for the second field.
If fractional second precision is not specified, it defaults to 0 for TIME and to 6 for TIMESTAMP.
<datetime type> ::= DATE | TIME [ <left paren> <time precision> <right paren> ]
[ <with or without time zone> ] | TIMESTAMP [ <left paren> <timestamp precision>
<right paren> ] [ <with or without time zone> ]
<with or without time zone> ::= WITH TIME ZONE | WITHOUT TIME ZONE
Pdf preview thumbnail - Library application component:C# PDF Thumbnail Create SDK: Draw thumbnail images for PDF in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Support Thumbnail Generation with Various Options for Quick PDF Navigation
www.rasteredge.com
Pdf preview thumbnail - Library application component:VB.NET PDF Thumbnail Create SDK: Draw thumbnail images for PDF in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Support Thumbnail Generation with Various Options for Quick PDF Navigation
www.rasteredge.com
SQL Language
19
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
DATE
TIME(6)
TIMESTAMP(2) WITH TIME ZONE
Examples of the string literals used to represent date time values, some with time zone, some without, are below:
DATE '2008-08-22'
TIMESTAMP '2008-08-08 20:08:08'
TIMESTAMP '2008-08-08 20:08:08+8:00' /* Beijing */
TIME '20:08:08.034900'
TIME '20:08:08.034900-8:00' /* US Pacific */
Time Zone
DATE values do not take time zones. For example United Nations designates 5 June as World Environment Day,
which was observed on DATE '2008-06-05' in different time zones.
TIME and TIMESTAMP values without time zone, usually have a context that indicates some local time zone. For
example, a database for college course timetables usually stores class dates and times without time zones. This works
because the location of the college is fixed and the time zone displacement is the same for all the values. Even when the
events take place in different time zones, for example international flight times, it is possible to store all the datetime
information as references to a single time zone, usually GMT. For some databases it may be useful to store the time
zone displacement together with each datetime value. SQL’s TIME WITH TIME ZONE and TIMESTAMP WITH
TIME ZONE values include a time zone displacement value.
The time zone displacement is of the type INTERVAL HOUR TO MINUTE. This data type is described in the next
section. The legal values are between '–14:00' and   '+14:00'.
Operations on Datetime Types
The expression <datetime expression> AT TIME ZONE <time displacement> evaluates to a datetime
value representing exactly the same point of time in the specified <time displacement>. The expression, AT
LOCAL is equivalent to AT TIME ZONE <local time displacement>. If AT TIME ZONE is used with
a datetime operand of type WITHOUT TIME ZONE, the operand is first converted to a value of type WITH TIME
ZONE at the session’s time displacement, then the specified time zone displacement is set for the value. Therefore, in
these cases, the final value depends on the time zone of the session in which the statement was used.
AT TIME ZONE, modifies the field values of the datetime operand. This is done by the following procedure:
1. determine the corresponding datetime at UTC.
2. find the datetime value at the given time zone that corresponds with the UTC value from step 1.
Example a:
TIME '12:00:00' AT TIME ZONE INTERVAL '1:00' HOUR TO MINUTE
If the session’s time zone displacement is -'8:00', then in step 1, TIME '12:00:00' is converted to UTC, which is TIME
'20:00:00+0:00'. In step 2, this value is expressed as TIME '21:00:00+1:00'.
Example b:
Library application component:How to C#: Preview Document Content Using XDoc.Word
With the SDK, you can preview the document content according to the preview thumbnail by the ways as following. C# DLLs for Word File Preview. Add references:
www.rasteredge.com
Library application component:How to C#: Preview Document Content Using XDoc.PowerPoint
With the SDK, you can preview the document content according to the preview thumbnail by the ways as following. C# DLLs: Preview PowerPoint Document.
www.rasteredge.com
SQL Language
20
TIME '12:00:00-5:00' AT TIME ZONE INTERVAL '1:00' HOUR TO MINUTE
Because the operand has a time zone, the result is independent of the session  time zone displacement. Step 1 results
in TIME '17:00:00+0:00', and step 2 results in TIME '18:00:00+1:00'
Note that the operand is not limited to datetime literals used in these examples. Any valid expression that evaluates
to a datetime value can be the operand.
Type Conversion
CAST is used to for all other conversions. Examples:
CAST (<value> AS TIME WITHOUT TIME ZONE)
CAST (<value> AS TIME WITH TIME ZONE)
In the first example, if <value> has a time zone component, it is simply dropped. For example TIME '12:00:00-5:00'
is converted to TIME '12:00:00'
In the second example, if <value> has no time zone component, the current time zone displacement of the session is
added. For example TIME '12:00:00' is converted to TIME '12:00:00-8:00' when the session time zone displacement
is '-8:00'.
Conversion between DATE and TIMESTAMP is performed by removing the TIME component of a TIMESTAMP
value or by setting the hour, minute and second fields to zero. TIMESTAMP '2008-08-08 20:08:08+8:00' becomes
DATE '2008-08-08', while DATE '2008-08-22' becomes TIMESTAMP '2008-08-22 00:00:00'.
Conversion between TIME and TIMESTAMP is performed by removing the DATE field values of a TIMESTAMP
value or by appending the fields of the TIME value to the fields of the current session date value.
Assignment
When a value is assigned to a datetime target, e.g., a value is used to update a row of a table, the type of the value must
be the same as the target, but the WITH TIME ZONE or WITHOUT TIME ZONE characteristics can be different. If
the types are not the same, an explicit CAST must be used to convert the value into the target type.
Comparison
When values WITH TIME ZONE are compared, they are converted to UTC values before comparison. If a value
WITH TIME ZONE is compared to another WITHOUT TIME ZONE, then the WITH TIME ZONE value is converted
to AT LOCAL, then converted to WITHOUT TIME ZONE before comparison.
It is not recommended to design applications that rely on comparisons and conversions between TIME values WITH
TIME ZONE. The conversions may involve normalisation of the time value, resulting in unexpected results. For
example, the expression: BETWEEN(TIME '12:00:00-8:00', TIME '22:00:00-8:00') is converted to BETWEEN(TIME
'20:00:00+0:00', TIME '06:00:00+0:00') when it is evaluated in the UTC zone, which is always FALSE.
Functions
Several functions return the current session timestamp in different datetime types:
CURRENT_DATE
DATE
CURRENT_TIME
TIME WITH TIME ZONE
CURRENT_TIMESTAMP
TIMESTAMP WITH TIME ZONE
LOCALTIME
TIME WITHOUT TIME ZONE
LOCALTIMESTAMP
TIMESTAMP WITHOUT TIME ZONE
Library application component:How to C#: Set Image Thumbnail in C#.NET
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel, VB.NET PowerPoint, VB How to C#: Set Image Thumbnail in C#.NET. To Preview Images in WinForm Application.
www.rasteredge.com
Library application component:How to C#: Preview Document Content Using XDoc.excel
document in memory. With the SDK, you can preview the document content according to the preview thumbnail by the ways as following.
www.rasteredge.com
SQL Language
21
HyperSQL supports a very extensive range of functions for conversion, extraction and manipulation of DATE and
TIMESTAMP values. See the  Built In Functions  chapter.
Session Time Zone Displacement
When an SQL session is started (with a JDBC connection) the local time zone of the client JVM (including any seasonal
time adjustments such as daylight saving time) is used as the session time zone displacement. Note that the SQL session
time displacement is not changed when a seasonal time adjustment takes place while the session is open. To change
the SQL session time zone displacement use the following commands:
SET TIME ZONE <time displacement>
SET TIME ZONE LOCAL
The first command sets the displacement to the given value. The second command restores the original, real time zone
displacement of the session.
Datetime Values and Java
When datetime values are  sent to the database using the  PreparedStatement or CallableStatement
interfaces, the Java object is converted to the type of the prepared or callable statement parameter. This type may
be DATE, TIME, or TIMESTAMP (with or without time zone). The time zone displacement is the time zone of the
JDBC session.
When datetime values are retrieved from the database using the ResultSet interface, there are two representations.
The getString(…) methods of the ResultSet interface, return an exact representation of the value in the SQL
type as it is stored in the database. This includes the correct number of digits for the fractional second field, and for
values with time zone displacement, the time zone displacement. Therefore if TIME '12:00:00' is stored in the database,
all users in different time zones will get '12:00:00' when they retrieve the value as a string. The getTime(…) and
getTimestamp(…) methods of the ResultSet interface return Java objects that are corrected for the session
time zone. The UTC millisecond value contained the java.sql.Time or java.sql.Timestamp objects will
be adjusted to the time zone of the session, therefore the toString() method of these objects return the same values
in different time zones.
If you want to store and retrieve UTC values that are independent of any session's time zone, you can use a
TIMESTAMP WITH TIME ZONE column. The setTime(...) and setTimestamp(...) methods of the PreparedStatement
interface which have a Calendar parameter can be used to assign the values. The time zone of the given Calendar
argument is used as the time zone. Conversely, the getTime(...) and getTimestamp(...) methods of the ResultSet
interface which have a Calendar parameter can be used with a Calendar argument to retrieve the values.
JDBC has an unfortunate limitation and does not include type codes for SQL datetime types that have a TIME
ZONE  property.  Therefore,  for  compatibility  with  database  tools  that  are  limited  to  the  JDBC  type  codes,
HyperSQL reports these types by default as datetime types without TIME ZONE. You can use the URL property
hsqldb.translate_dti_types=false to override the default behaviour.
Non-Standard Extensions
HyperSQL version 2.3.0 supports some extenstions to the SQL standard treatment of datetime and interval types. For
example, the Standard expression to add a number of days to a date has an explicit INTERVAL value but HSQLDB
also allows an integer to be used without specifying DAY. Examples of some Standard expressions and their non-
standard alternatives are given below:
-- standard forms
CURRENT_DATE + '2' DAY
SELECT (LOCALTIMESTAMP - atimestampcolumn) DAY TO SECOND FROM atable
Library application component:How to C#: Generate Thumbnail for Word
Preview Document. Conversion. Convert Word to PDF. Convert Word Convert Word to ODT. Convert PDF to Word. Text Search. Insert Image. Thumbnail Create. Thumbnail Create
www.rasteredge.com
Library application component:How to C#: Generate Thumbnail for PowerPoint
Preview Document. Conversion. Convert PowerPoint to PDF. Convert PowerPoint to Pages. Annotate PowerPoint. Text Search. Insert Image. Thumbnail Create.
www.rasteredge.com
SQL Language
22
-- non-standard forms
CURRENT_DATE + 2
SELECT LOCALTIMESTAMP - atimestampcolumn FROM atable
It is recommended to use the SQL Standard syntax as it is more precise and avoids ambiguity.
Interval Types
Interval types are used to represent differences between date time values. The difference between two date time values
can be measured in seconds or in months. For measurements in months, the units YEAR and MONTH are available,
while for measurements in seconds, the units DAY, HOUR, MINUTE, SECOND are available. The units can be used
individually, or as a range. An interval type can specify the precision of the most significant field and the second fraction
digits of the SECOND field (if it has a SECOND field). The default precision is 2. The default second precision is 0.
<interval type> ::= INTERVAL <interval qualifier>
<interval qualifier> ::= <start field> TO <end field> | <single datetime field>
<start field> ::= <non-second primary datetime field> [ <left paren> <interval
leading field precision> <right paren> ]
<end field> ::= <non-second primary datetime field> | SECOND [ <left paren>
<interval fractional seconds precision> <right paren> ]
<single datetime field> ::= <non-second primary datetime field> [ <left paren>
<interval leading field precision> <right paren> ] | SECOND [ <left paren>
<interval  leading  field  precision>  [  <comma>  <interval  fractional  seconds
precision> ] <right paren> ]
<primary datetime field> ::= <non-second primary datetime field> | SECOND
<non-second primary datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE
<interval fractional seconds precision> ::= <unsigned integer>
<interval leading field precision> ::= <unsigned integer>
Examples of INTERVAL type definition:
INTERVAL YEAR TO MONTH
INTERVAL YEAR(3)
INTERVAL DAY(4) TO HOUR
INTERVAL MINUTE(4) TO SECOND(6)
INTERVAL SECOND(4,6)
The word INTERVAL  indicates  the  general  type name. The  rest of the definition  is called  an  <interval
qualifier>. This designation is important, as in most expressions <interval qualifier> is used without
the word INTERVAL.
Interval Values
An interval value can be negative, positive or zero. An interval type has all the datetime fields in the specified range.
These fields are similar to those in the TIMESTAMP type. The differences are as follows:
The first field of an interval value can hold any numeric value up to the specified precision. For example, the hour
field in HOUR(2) TO SECOND can hold values above 23 (up to 99). The year and month fields can hold zero (unlike
a TIMESTAMP value) and the maximum value of a month field that is not the most significant field, is 11.
Library application component:How to C#: Generate Thumbnail for Excel
Preview Document. Conversion. Convert Excel to PDF. Convert Excel to Insert Image. Thumbnail Create. Thumbnail Create. |. Home ›› XDoc.Excel ›› C# Excel
www.rasteredge.com
Library application component:How to C#: Overview of Using XDoc.PowerPoint
document (ODP). Empower to navigate PowerPoint document content quickly via thumbnail. Able to you want. Create Thumbnail. See this
www.rasteredge.com
SQL Language
23
The standard function ABS(<interval value expression>) can be used to convert a negative interval value
to a positive one.
The literal representation of interval values consists of the type definition, with a string representing the interval value
inserted after the word INTERVAL. Some examples of interval literal below:
INTERVAL '145 23:12:19.345' DAY(3) TO SECOND(3)
INTERVAL '3503:12:19.345' HOUR TO SECOND(3) /* equal to the first value */
INTERVAL '19.345' SECOND(4,3) /* maximum number of digits for the second value is 4, and each
value is expressed with three fraction digits. */
INTERVAL '-23-10' YEAR(2) TO MONTH
Interval values of the types that are based on seconds can be cast into one another. Similarly those that are based on
months can be cast into one another. It is not possible to cast or convert a value based on seconds to one based on
months, or vice versa.
When a cast is performed to a type with a smaller least-significant field, nothing is lost from the interval value.
Otherwise, the values for the missing least-significant fields are discarded. Examples:
CAST ( INTERVAL '145 23:12:19' DAY TO SECOND AS INTERVAL DAY TO HOUR ) = INTERVAL '145 23' DAY
TO HOUR
CAST(INTERVAL '145 23' DAY TO HOUR AS INTERVAL DAY TO SECOND) = INTERVAL '145 23:00:00' DAY TO
SECOND
A numeric value can be cast to an interval type. In this case the numeric value is first converted to a single-field
INTERVAL type with the same field as the least significant field of the target interval type. This value is then converted
to the target interval type For example CAST( 22 AS INTERVAL YEAR TO MONTH) evaluates to INTERVAL '22'
MONTH and then INTERVAL '1 10' YEAR TO MONTH. Note that SQL Standard only supports casts to single-field
INTERVAL types, while HyperSQL allows casting to multi-field types as well.
An interval value can be cast to a numeric type. In this case the interval value is first converted to a single-field
INTERVAL type with the same field as the least significant filed of the interval value. The value is then converted
to the target type. For example CAST (INTERVAL '1-11' YEAR TO MONTH AS INT) evaluates to INTERVAL
'23' MONTH, and then 23.
An interval value can be cast into a character type, which results in an INTERVAL literal. A character value can be
cast into an INTERVAL type so long as it is a string with a format compatible with an INTERVAL literal.
Two interval values can be added or subtracted so long as the types of both are based on the same field, i.e., both are
based on MONTH or SECOND. The values are both converted to a single-field interval type with same field as the
least-significant field between the two types. After addition or subtraction, the result is converted to an interval type
that contains all the fields of the two original types.
An interval value can be multiplied or divided by a numeric value. Again, the value is converted to a numeric, which
is then multiplied or divided, before converting back to the original interval type.
An interval value is negated by simply prefixing with the minus sign.
Interval values used in expressions are either typed values, including interval literals, or are interval casts. The
expression: <expression> <interval qualifier> is a cast of the result of the <expression> into the
INTERVAL type specified by the <interval qualifier>. The cast can be formed by adding the
keywords and parentheses as follows: CAST ( <expression> AS INTERVAL <interval
qualifier> ).
The examples below  feature different forms of expression  that represent an
interval value, which is then added to the given date literal.
DATE '2000-01-01' + INTERVAL '1-10' YEAR TO MONTH /* interval literal */
Library application component:How to C#: Overview of Using XDoc.Word
Tell C# users how to: create a new Word file and load Word from pdf; merge, append, and split Word files; insert, delete, move, rotate, copy Create Thumbnail.
www.rasteredge.com
Library application component:How to C#: Overview of Using XDoc.Excel
Empower to navigate Excel document content quickly via thumbnail. Able to support text search in Excel document, as well as text extraction. Create Thumbnail.
www.rasteredge.com
SQL Language
24
DATE '2000-01-01' + '1-10' YEAR TO MONTH /* the string '1-10' is cast into INTERVAL YEAR TO
MONTH */
DATE '2000-01-01' + 22 MONTH /* the integer 22 is cast into INTERVAL MONTH, same value as above
*/
DATE '2000-01-01' - 22 DAY /* the integer 22 is cast into INTERVAL DAY */
DATE '2000-01-01' + COL2 /* the type of COL2 must be an INTERVAL type */
DATE '2000-01-01' + COL2 MONTH /* COL2 may be a number, it is cast into a MONTH interval */
Datetime and Interval Operations
An interval can be added to or subtracted from a datetime value so long as they have some fields in common. For
example, an INTERVAL MONTH cannot be added to a TIME value, while an INTERVAL HOUR TO SECOND can.
The interval is first converted to a numeric value, then the value is added to, or subtracted from, the corresponding
field of the datetime value.
If the result of addition or subtraction is beyond the permissible range for the field, the field value is normalised and
carried over to the next significant field until all the fields are normalised. For example, adding 20 minutes to TIME
'23:50:10' will result successively in '23:70:10', '24:10:10' and finally TIME '00:10:10'. Subtracting 20 minutes from
the result is performed as follows: '00:-10:10', '-1:50:10', finally TIME '23:50:10'. Note that if DATE or TIMESTAMP
normalisation results in the YEAR field value out of the range (1,1000), then an exception condition is raised.
If an interval value based on MONTH is added to, or subtracted from a DATE or TIMESTAMP value, the result may
have an invalid day (30 or 31) for the given result month. In this case an exception condition is raised.
The result of subtraction of two datetime expressions is an interval value. The two datetime expressions must be of
the same type. The type of the interval value must be specified in the expression, using only the interval field names.
The two datetime expressions are enclosed in parentheses, followed by the <interval qualifier> fields. In
the first example below, COL1 and COL2 are of the same datetime type, and the result is evaluated in INTERVAL
YEAR TO MONTH type.
(COL1 – COL2) YEAR TO MONTH /* the difference between two DATE or two TIEMSTAMP values in years
and months */
(CURRENT_DATE – COL3) DAY /* the number of days between the value of COL3 and the current date
*/
(CURRENT_DATE - DATE '2000-01-01') YEAR TO MONTH /* the number of years and months since the
beginning of this century */
CURRENT_DATE - 2 DAY /* the date of the day before yesterday */
(CURRENT_TIMESTAMP - TIMESTAMP '2009-01-01 00:00:00') DAY(4) TO SECOND(2) /* days to seconds
since the given date */
The individual fields of both datetime and interval values can be extracted using the EXTRACT function. The same
function can also be used to extract the time zone displacement fields of a datetime value.
EXTRACT  ({YEAR  |  MONTH  |  DAY  |  HOUR  |  MINUTE  |  SECOND  |  TIMEZONE_HOUR  |
TIMEZONE_MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR } FROM {<datetime value> | <interval
value>})
The dichotomy between interval types based on seconds, and those based on months, stems from the fact that the
different calendar months have different numbers of days. For example, the expression, “nine months and nine days
since an event” is not exact when the date of the event is unknown. It can represent a period of around 284 days give
or take one. SQL interval values are independent of any start or end dates or times. However, when they are added to
or subtracted from certain date or timestamp values, the result may be invalid and cause an exception (e.g. adding one
month to January 30 results in February 30, which is invalid).
JDBC has an unfortunate limitation and does not include type codes for SQL INTERVAL types. Therefore, for
compatibility with database tools that are limited to the JDBC type codes, HyperSQL reports these types by default as
VARCHAR. You can use the URL property hsqldb.translate_dti_types=false to override the default
behaviour.
SQL Language
25
Arrays
Array are a powerful feature of SQL:2008 and can help solve many common problems. Arrays should not be used
as a substitute for tables.
HyperSQL supports arrays of values according to the SQL:2008 Standard.
Elements of the array are either NULL, or of the same data type. It is possible to define arrays of all supported types,
including the types covered in this chapter and user defined types, except LOB types. An SQL array is one dimensional
and is addressed from position 1. An empty array can also be used, which has no element.
Arrays can be stored in the database, as well as being used as temporary containers of values for simplifying SQL
statements. They facilitate data exchange between the SQL engine and the user's application.
The full range of supported syntax allows array to be created, used in SELECT or other statements, combined with
rows of tables and used in routine calls.
Array Definition
The type of a table column, a routine parameter, a variable, or the return value of a function can be defined as an array.
<array  type>  ::=  <data  type>  ARRAY  [  <left  bracket  or  trigraph>  <maximum
cardinality> <right bracket or trigraph> ]
The word ARRAY is added to any valid type definition except BLOB and CLOB type definitions. If the optional
<maximum cardinality> is not used, the default value is 1024. The size of the array cannot be extended beyond
maximum cardinality.
In the example below, the table contains a column of integer arrays and a column of varchar arrays. The VARCHAR
array has an explicit maximum size of 10, which means each array can have between 0 and 10 elements. The INTEGER
array has the default maximum size of 1024. The scores column has a default clause with an empty array. The default
clause can be defined only as DEFAULT  NULL or DEFAULT  ARRAY[] and does not allow arrays containing
elements.
CREATE TABLE t (id INT PRIMARY KEY, scores INT ARRAY DEFAULT ARRAY[], names VARCHAR(20)
ARRAY[10])
An array can be constructed from value expressions or a query expression.
<array value constructor by enumeration> ::= ARRAY <left bracket or trigraph>
<array element list> <right bracket or trigraph>
<array element list> ::= <value expression> [ { <comma> <value expression> }... ]
<array value constructor by query> ::= ARRAY <left paren> <query expression>
[ <order by clause> ] <right paren>
In the examples below, arrays are constructed from values, column references or variables, function calls, or query
expressions.
ARRAY [ 1, 2, 3 ]
ARRAY [ 'HOT', 'COLD' ]
ARRAY [ var1, var2, CURRENT_DATE ]
ARRAY (SELECT lastname FROM namestable ORDER BY id)
Inserting and updating a table with an ARRAY column can use array constructors, not only for updated column values,
but also in equality search conditions:
SQL Language
26
INSERT INTO t VALUES 10, ARRAY[1,2,3], ARRAY['HOT', 'COLD']
UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id = 12
UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND scores = ARRAY[3,4]
When using a PreparedStatement with an ARRAY parameter, an object of the type java.sql.Array must be used to set
the parameter. The org.hsqldb.jdbc.JDBCArrayBasic class can be used for constructing a java.sql.Array
object in the user's application. Code fragment below:
String sql = "UPDATE t SET names = ? WHERE id = ?";
PreparedStatement ps = connection.prepareStatement(sql)
Object[] data = new Object[]{"one", "two"};
// default types defined in org.hsqldb.types.Type can be used
org.hsqldb.types.Type type = org.hsqldb.types.Type.SQL_VARCHAR_DEFAULT;
JDBCArrayBasic array = new JDBCArrayBasic(data, type);
ps.setArray(1, array);
ps.setInt(2, 1000);
ps.executeUpdate();
Trigraph
A trigraph is a substitute for <left bracket> and <right bracket>.
<left bracket trigraph> ::= ??(
<right bracket trigraph> ::= ??)
The example below shows the use of trigraphs instead of brackets.
INSERT INTO t VALUES 10, ARRAY??(1,2,3??), ARRAY['HOT', 'COLD']
UPDATE t SET names = ARRAY ??('LARGE', 'SMALL'??) WHERE id = 12
UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND scores = ARRAY[3,4]
Array Reference
The most common operations on an array are element reference and assignment, which are used when reading or
writing an element of the array. Unlike Java and many other languages, arrays are extended if an element is assigned
to an index beyond the current length. This can result in gaps containing NULL elements. Array length cannot exceed
the maximum cardinality.
Elements of all arrays, including those that are the result of function calls or other operations can be referenced for
reading.
<array element reference> ::= <array value expression> <left bracket> <numeric
value expression> <right bracket>
Elements of arrays that are table columns or routine variables can be referenced for writing. This is done in a SET
statement, either inside an UPDATE statement, or as a separate statement in the case of routine variables, OUT and
INOUT parameters.
<target array element specification> ::= <target array reference> <left bracket
or trigraph> <simple value specification> <right bracket or trigraph>
<target array reference> ::= <SQL parameter reference> | <column reference>
Note that only simple values or variables are allowed for the array index when an assignment is performed. The
examples below demonstrates how elements of the array are referenced in SELECT and an UPDATE statement.
SELECT scores[ranking], names[ranking] FROM t JOIN t1 on (t.id = t1.tid)
UPDATE t SET scores[2] = 123, names[2] = 'Reds' WHERE id = 10
SQL Language
27
SELECT scores[ranking], names[ranking] FROM t JOIN t1 on (t.id = t1.tid)
UPDATE t SET scores[2] = 123, names[2] = 'Reds' WHERE id = 10
Array Operations
Several SQL operations and functions can be used with arrays.
CONCATENATION
Array concatenation is performed similar to string concatenation. All elements of the array on the right are appended
to the array on left.
<array concatenation> ::= <array value expression 1> <concatenation operator>
<array value expression 2>
<concatenation operator> ::= ||
FUNCTIONS
Seven functions operate on arrays. Details are described in the  Built In Functions  chapter.
ARRAY_AGG is an aggregate function and produces an array containing values from differnt rows of a SELECT
statement. Details are described in the  Data Access and Change  chapter.
SEQUENCE_ARRAY creates an array with sequential elements.
CARDINALITY <left paren> <array value expression> <right paren>
MAX_CARDINALITY <left paren> <array value expression> <right paren>
Array cardinality and max cardinality are functions that return an integer. CARDINALITY returns the element count,
while MAX_CARDINALITY returns the maximum declared cardinality of an array.
POSITION_ARRAY <left paren> <value expression> IN <array value expression> [FROM
<numeric value expression>] <right paren>
The POSITION_ARRAY function returns the position of the first match for the <value expression> from the start or
from the given start position when <numeric value expression> is used.
TRIM_ARRAY  <left  paren>  <array  value  expression>  <comma>  <numeric  value
expression> <right paren>
The TRIM_ARRAY function returns a copy of an array with the specified number of elements removed from the end
of the array. The <array value expression> can be any expression that evaluates to an array.
SORT_ARRAY <left paren> <array value expression> [ { ASC | DESC } ] [ NULLS
{ FIRST | LAST } ] <right paren>
The SORT_ARRAY function returns a sorted copy of an array. NULL elements appear at the beginning of the new
array. You can change the sort direction or the position of NULL elements with the option keywords.
CAST
An array can be cast into an array of a different type. Each element of the array is cast into the element type of the
target array type.
UNNEST
Documents you may be interested
Documents you may be interested