Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 3.5 Functions and Operators
Postgres Plus Advanced Server Oracle Compatibility Developer's Guide
3.5 Functions and Operators
Postgres Plus Advanced Server provides a large number of functions and operators for the built-in data types.
3.5.1 Logical Operators
The usual logical operators are available: AND, OR, NOT
SQL uses a three-valued Boolean logic where the null value represents "unknown". Observe the following truth tables:
Table 3-3-14 AND/OR Truth Table
a |
b |
a AND b |
a OR b |
True |
True |
True |
True |
True |
False |
False |
True |
True |
Null |
Null |
True |
False |
False |
False |
False |
False |
Null |
False |
Null |
Null |
Null |
Null |
Null |
Table 3-3-15 NOT Truth Table
a |
NOT a |
True |
False |
False |
True |
Null |
Null |
The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result.
3.5.2 Comparison Operators
The usual comparison operators are shown in the following table.
Table 3-3-16 Comparison Operators
Operator |
Description |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
= |
Equal |
<> |
Not equal |
!= |
Not equal |
Comparison operators are available for all data types where this makes sense. All comparison operators are binary operators that return values of type BOOLEAN; expressions like 1 < 2 < 3 are not valid (because there is no < operator to compare a Boolean value with 3).
In addition to the comparison operators, the special BETWEEN construct is available.
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
Similarly,
a NOT BETWEEN x AND y
is equivalent to
a < x OR a > y
There is no difference between the two respective forms apart from the CPU cycles required to rewrite the first one into the second one internally.
To check whether a value is or is not null, use the constructs
expression IS NULL expression IS NOT NULL
Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard.
Some applications may expect that expression = NULL returns true if expression evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard.
3.5.3 Mathematical Functions and Operators
Mathematical operators are provided for many Postgres Plus Advanced Server types. For types without common mathematical conventions for all possible permutations (e.g., date/time types) the actual behavior is described in subsequent sections.
The following table shows the available mathematical operators.
Table 3-3-17 Mathematical Operators
Operator |
Description |
Example |
Result |
+ |
Addition |
2 + 3 |
5 |
- |
Subtraction |
2 – 3 |
-1 |
* |
Multiplication |
2 * 3 |
6 |
/ |
Division (integer division truncates results) |
4 / 2 |
2 |
The following table shows the available mathematical functions. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument. The functions working with DOUBLE PRECISION data are mostly implemented on top of the host system’s C library; accuracy and behavior in boundary cases may therefore vary depending on the host system.
Table 3-3-18 Mathematical Functions
Function |
Return Type |
Description |
Example |
Result |
ABS(x) |
Same as x |
Absolute value |
ABS(-17.4) |
17.4 |
CEIL(DOUBLE PRECISION or NUMBER) |
Same as input |
Smallest integer not less than argument |
CEIL(-42.8) |
-42 |
EXP(DOUBLE PRECISION or NUMBER) |
Same as input |
Exponential |
EXP(1.0) |
2.7182818284590452 |
FLOOR(DOUBLE PRECISION or NUMBER) |
Same as input |
Largest integer not greater than argument |
FLOOR(-42.8) |
43 |
LN(DOUBLE PRECISION or NUMBER) |
Same as input |
Natural logarithm |
LN(2.0) |
0.6931471805599453 |
LOG(b NUMBER, x NUMBER) |
NUMBER |
Logarithm to base b |
LOG(2.0, 64.0) |
6.0000000000000000 |
MOD(y, x) |
Same as argument types |
Remainder of y/x |
MOD(9, 4) |
1 |
NVL(x, y) |
Same as argument types; where both arguments are of the same data type |
If x is null, then NVL returns y |
NVL(9, 0) |
9 |
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION) |
DOUBLE PRECISION |
a raised to the power of b |
POWER(9.0, 3.0) |
729.0000000000000000 |
POWER(a NUMBER, b NUMBER) |
NUMBER |
a raised to the power of b |
POWER(9.0, 3.0) |
729.0000000000000000 |
ROUND(DOUBLE PRECISION or NUMBER) |
Same as input |
Round to nearest integer |
ROUND(42.4) |
42 |
ROUND(v NUMBER, s INTEGER) |
NUMBER |
Round to s decimal places |
ROUND(42.4382, 2) |
42.44 |
SIGN(DOUBLE PRECISION or NUMBER) |
Same as input |
Sign of the argument (-1, 0, +1) |
SIGN(-8.4) |
-1 |
SQRT(DOUBLE PRECISION or NUMBER) |
Same as input |
Square root |
SQRT(2.0) |
1.414213562373095 |
TRUNC(DOUBLE PRECISION or NUMBER) |
Same as input |
Truncate toward zero |
TRUNC(42.8) |
42 |
TRUNC(v NUMBER, s INTEGER) |
NUMBER |
Truncate to s decimal places |
TRUNC(42.4382, 2) |
42.43 |
WIDTH_BUCKET(op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER) |
INTEGER |
Return the bucket to which op would be assigned in an equidepth histogram with count buckets, in the range b1 to b2 |
WIDTH_BUCKET(5.35, 0.024, 10.06, 5) |
3 |
The following table shows the available trigonometric functions. All trigonometric functions take arguments and return values of type DOUBLE PRECISION.
Table 3-3-19 Trigonometric Functions
Function |
Description |
ACOS(x) |
Inverse cosine |
ASIN(x) |
Inverse sine |
ATAN(x) |
Inverse tangent |
ATAN2(x, y) |
Inverse tangent of x/y |
COS(x) |
Cosine |
SIN(x) |
Sine |
TAN(x) |
Tangent |
3.5.4 String Functions and Operators
This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types CHAR, VARCHAR2, and CLOB. Unless otherwise noted, all of the functions listed below work on all of these types, but be wary of potential effects of automatic padding when using the CHAR type. Generally, the functions described here also work on data of non-string types by converting that data to a string representation first.
Table 3-3-20 SQL String Functions and Operators
Function |
Return Type |
Description |
Example |
Result |
string || string |
CLOB |
String concatenation |
'Enterprise' || 'DB' |
EnterpriseDB |
CONCAT(string, string) |
CLOB |
String concatenation |
'a' || 'b' |
ab |
HEXTORAW(varchar2) |
RAW |
Converts a VARCHAR2 value to a RAW value |
HEXTORAW('303132') |
'012' |
RAWTOHEX(raw) |
VARCHAR2 |
Converts a RAW value to a HEXADECIMAL value |
RAWTOHEX('012') |
'303132' |
INSTR(string, set, [ start [, occurrence ] ]) |
INTEGER |
Finds the location of a set of characters in a string, starting at position start in the string, string, and looking for the first, second, third and so on occurrences of the set. Returns 0 if the set is not found. |
INSTR('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PI',1,3) |
30 |
INSTRB(string, set) |
INTEGER |
Returns the position of the set within the string. Returns 0 if set is not found. |
INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS', 'PICK') |
13 |
INSTRB(string, set, start) |
INTEGER |
Returns the position of the set within the string, beginning at start. Returns 0 if set is not found. |
INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PICK', 14) |
30 |
INSTRB(string, set, start, occurrence) |
INTEGER |
Returns the position of the specified occurrence of set within the string, beginning at start. Returns 0 if set is not found. |
INSTRB('PETER PIPER PICKED a PECK of PICKLED PEPPERS','PICK', 1, 2) |
30 |
LOWER(string) |
CLOB |
Convert string to lower case |
LOWER('TOM') |
tom |
SUBSTR(string, start [, count ]) |
CLOB |
Extract substring starting from start and going for count characters. If count is not specified, the string is clipped from the start till the end. |
SUBSTR('This is a test',6,2) |
is |
SUBSTRB(string, start [, count ]) |
CLOB |
Same as SUBSTR except start and count are in number of bytes. |
SUBSTRB('abc',3) (assuming a double-byte character set) |
bc |
TRIM([ LEADING | TRAILING | BOTH ] [ characters ] FROM string) |
CLOB |
Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string. |
TRIM(BOTH 'x' FROM 'xTomxx') |
Tom |
LTRIM(string [, set]) |
CLOB |
Removes all the characters specified in set from the left of a given string. If set is not specified, a blank space is used as default. |
LTRIM('abcdefghi', 'abc') |
defghi |
RTRIM(string [, set]) |
CLOB |
Removes all the characters specified in set from the right of a given string. If set is not specified, a blank space is used as default. |
RTRIM('abcdefghi', 'ghi') |
abcdef |
UPPER(string) |
CLOB |
Convert string to upper case |
UPPER('tom') |
TOM |
Additional string manipulation functions are available and are listed in the following table. Some of them are used internally to implement the SQL-standard string functions listed in Table 3-3-20.
Table 3-3-21 Other String Functions
Function |
Return Type |
Description |
Example |
Result |
ASCII(string) |
INTEGER |
ASCII code of the first byte of the argument |
ASCII('x') |
120 |
CHR(INTEGER) |
CLOB |
Character with the given ASCII code |
CHR(65) |
A |
DECODE(expr, expr1a, expr1b [, expr2a, expr2b ]... [, default ]) |
Same as argument types of expr1b, expr2b,..., default |
Finds first match of expr with expr1a, expr2a, etc. When match found, returns corresponding parameter pair, expr1b, expr2b, etc. If no match found, returns default. If no match found and default not specified, returns null. |
DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found') |
Three |
INITCAP(string) |
CLOB |
Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. |
INITCAP('hi THOMAS') |
Hi Thomas |
LENGTH |
INTEGER |
Returns the number of characters in a string value. |
LENGTH('Côte d''Azur') |
11 |
LENGTHC |
INTEGER |
This function is identical in functionality to LENGTH; the function name is supported for compatibility. |
LENGTHC('Côte d''Azur') |
11 |
LENGTH2 |
INTEGER |
This function is identical in functionality to LENGTH; the function name is supported for compatibility. |
LENGTH2('Côte d''Azur') |
11 |
LENGTH4 |
INTEGER |
This function is identical in functionality to LENGTH; the function name is supported for compatibility. |
LENGTH4('Côte d''Azur') |
11 |
LENGTHB |
INTEGER |
Returns the number of bytes required to hold the given value. |
LENGTHB('Côte d''Azur') |
12 |
LPAD(string, length INTEGER [, fill ]) |
CLOB |
Fill up string to size, length by prepending the characters, fill (a space by default). If string is already longer than length then it is truncated (on the right). |
LPAD('hi', 5, 'xy') |
xyxhi |
REPLACE(string, search_string [, replace_string ] |
CLOB |
Replaces one value in a string with another. If you do not specify a value for replace_string, the search_string value when found, is removed. |
REPLACE( 'GEORGE', 'GE', 'EG') |
EGOREG |
RPAD(string, length INTEGER [, fill ]) |
CLOB |
Fill up string to size, length by appending the characters, fill (a space by default). If string is already longer than length then it is truncated. |
RPAD('hi', 5, 'xy') |
hixyx |
TRANSLATE(string, from, to) |
CLOB |
Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. |
TRANSLATE('12345', '14', 'ax') |
a23x5 |
3.5.5 Pattern Matching Using the LIKE Operator
Postgres Plus Advanced Server provides pattern matching using the traditional SQL LIKE operator. The syntax for the LIKE operator is as follows.
string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ]
Every pattern defines a set of strings. The LIKE expression returns TRUE if string is contained in the set of strings represented by pattern. As expected, the NOT LIKE expression returns FALSE if LIKE returns TRUE, and vice versa. An equivalent expression is NOT (string LIKE pattern).
If pattern does not contain percent signs or underscore, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any string of zero or more characters.
Some examples:
'abc' LIKE 'abc' true 'abc' LIKE 'a%' true 'abc' LIKE '_b_' true 'abc' LIKE 'c' false
LIKE pattern matches always cover the entire string. To match a pattern anywhere within a string, the pattern must therefore start and end with a percent sign.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one may be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement. Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to the string literal parser, so you still need two of them.)
It’s also possible to select no escape character by writing ESCAPE ''. This effectively disables the escape mechanism, which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern.
3.5.6 Data Type Formatting Functions
The Postgres Plus Advanced Server formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table 3-3-22 lists them. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a string template that defines the output or input format.
Table 3-3-22 Formatting Functions
Function |
Return Type |
Description |
Example |
Result |
TO_CHAR(DATE [, format ]) |
VARCHAR2 |
Convert a date/time to a string with output, format. If omitted default format is DD-MON-YY. |
TO_CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM') |
07/25/2007 09:43:02 AM |
TO_CHAR(INTEGER [, format ]) |
VARCHAR2 |
Convert an integer to a string with output, format |
TO_CHAR(2412, '999,999S') |
2,412+ |
TO_CHAR(NUMBER [, format ]) |
VARCHAR2 |
Convert a decimal number to a string with output, format |
TO_CHAR(10125.35, '999,999.99') |
10,125.35 |
TO_CHAR(DOUBLE PRECISION, format) |
VARCHAR2 |
Convert a floating-point number to a string with output, format |
TO_CHAR(CAST(123.5282 AS REAL), '999.99') |
123.53 |
TO_DATE(string [, format ]) |
DATE |
Convert a date formatted string to a DATE data type |
TO_DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS') |
04-JUL-07 13:39:10 |
TO_NUMBER(string [, format ]) |
NUMBER |
Convert a number formatted string to a NUMBER data type |
TO_NUMBER('2,412-', '999,999S') |
-2412 |
TO_TIMESTAMP(string, format) |
TIMESTAMP |
Convert a timestamp formatted string to a TIMESTAMP data type |
TO_TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm') |
05-DEC-00 20:30:25 |
In an output template string (for TO_CHAR), there are certain patterns that are recognized and replaced with appropriately-formatted data from the value to be formatted. Any text that is not a template pattern is simply copied verbatim. Similarly, in an input template string (for anything but TO_CHAR), template patterns identify the parts of the input data string to be looked at and the values to be found there.
The following table shows the template patterns available for formatting date values using the TO_CHAR and TO_DATE functions.
Table 3-3-23 Template Date/Time Format Patterns
Pattern |
Description |
HH |
Hour of day (01-12) |
HH12 |
Hour of day (01-12) |
HH24 |
Hour of day (00-23) |
MI |
Minute (00-59) |
SS |
Second (00-59) |
SSSSS |
Seconds past midnight (0-86399) |
AM or A.M. or PM or P.M. |
Meridian indicator (uppercase) |
am or a.m. or pm or p.m. |
Meridian indicator (lowercase) |
Y,YYY |
Year (4 and more digits) with comma |
YEAR |
Year (spelled out) |
SYEAR |
Year (spelled out) (BC dates prefixed by a minus sign) |
YYYY |
Year (4 and more digits) |
SYYYY |
Year (4 and more digits) (BC dates prefixed by a minus sign) |
YYY |
Last 3 digits of year |
YY |
Last 2 digits of year |
Y |
Last digit of year |
IYYY |
ISO year (4 and more digits) |
IYY |
Last 3 digits of ISO year |
IY |
Last 2 digits of ISO year |
I |
Last 1 digit of ISO year |
BC or B.C. or AD or A.D. |
Era indicator (uppercase) |
bc or b.c. or ad or a.d. |
Era indicator (lowercase) |
MONTH |
Full uppercase month name |
Month |
Full mixed-case month name |
month |
Full lowercase month name |
MON |
Abbreviated uppercase month name (3 chars in English, localized lengths vary) |
Mon |
Abbreviated mixed-case month name (3 chars in English, localized lengths vary) |
mon |
Abbreviated lowercase month name (3 chars in English, localized lengths vary) |
MM |
Month number (01-12) |
DAY |
Full uppercase day name |
Day |
Full mixed-case day name |
day |
Full lowercase day name |
DY |
Abbreviated uppercase day name (3 chars in English, localized lengths vary) |
Dy |
Abbreviated mixed-case day name (3 chars in English, localized lengths vary) |
dy |
Abbreviated lowercase day name (3 chars in English, localized lengths vary) |
DDD |
Day of year (001-366) |
DD |
Day of month (01-31) |
D |
Day of week (1-7; Sunday is 1) |
W |
Week of month (1-5) (The first week starts on the first day of the month) |
WW |
Week number of year (1-53) (The first week starts on the first day of the year) |
IW |
ISO week number of year; the first Thursday of the new year is in week 1 |
CC |
Century (2 digits); the 21st century starts on 2001-01-01 |
SCC |
Same as CC except BC dates are prefixed by a minus sign |
J |
Julian Day (days since January 1, 4712 BC) |
Q |
Quarter |
RM |
Month in Roman numerals (I-XII; I=January) (uppercase) |
rm |
Month in Roman numerals (i-xii; i=January) (lowercase) |
RR |
First 2 digits of the year when given only the last 2 digits of the year. Result is based upon an algorithm using the current year and the given 2-digit year. The first 2 digits of the given 2-digit year will be the same as the first 2 digits of the current year with the following exceptions: If the given 2-digit year is < 50 and the last 2 digits of the current year is >= 50, then the first 2 digits for the given year is 1 greater than the first 2 digits of the current year. If the given 2-digit year is >= 50 and the last 2 digits of the current year is < 50, then the first 2 digits for the given year is 1 less than the first 2 digits of the current year. |
RRRR |
Only affects TO_DATE function. Allows specification of 2-digit or 4-digit year. If 2-digit year given, then returns first 2 digits of year like RR format. If 4-digit year given, returns the given 4-digit year. |
Certain modifiers may be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. The following table shows the modifier patterns for date/time formatting.
Table 3-3-24 Template Pattern Modifiers for Date/Time Formatting
Modifier |
Description |
Example |
FM prefix |
Fill mode (suppress padding blanks and zeros) |
FMMonth |
TH suffix |
Uppercase ordinal number suffix |
DDTH |
th suffix |
Lowercase ordinal number suffix |
DDth |
FX prefix |
Fixed format global option (see usage notes) |
FX Month DD Day |
SP suffix |
Spell mode |
DDSP |
Usage notes for date/time formatting:
● FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern fixed-width.
● TO_TIMESTAMP and TO_DATE skip multiple blank spaces in the input string if the FX option is not used. FX must be specified as the first item in the template. For example TO_TIMESTAMP('2000 JUN', 'YYYY MON') is correct, but TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.
● Ordinary text is allowed in TO_CHAR templates and will be output literally.
● In conversions from string to timestamp or date, the CC field is ignored if there is a YYY, YYYY or Y,YYY field. If CC is used with YY or Y then the year is computed as (CC-1)*100+YY.
The following table shows the template patterns available for formatting numeric values.
Table 3-3-25 Template Patterns for Numeric Formatting
Pattern |
Description |
9 |
Value with the specified number of digits |
0 |
Value with leading zeroes |
. (period) |
Decimal point |
, (comma) |
Group (thousand) separator |
$ |
Dollar sign |
PR |
Negative value in angle brackets |
S |
Sign anchored to number (uses locale) |
L |
Currency symbol (uses locale) |
D |
Decimal point (uses locale) |
G |
Group separator (uses locale) |
MI |
Minus sign specified in right-most position (if number < 0) |
RN or rn |
Roman numeral (input between 1 and 3999) |
V |
Shift specified number of digits (see notes) |
Usage notes for numeric formatting:
● 9 results in a value with the same number of digits as there are 9s. If a digit is not available it outputs a space.
● TH does not convert values less than zero and does not convert fractional numbers.
V effectively multiplies the input values by 10^{n}, where n is the number of digits following V. TO_CHAR does not support the use of V combined with a decimal point. (E.g., 99.9V99 is not allowed.)
The following table shows some examples of the use of the TO_CHAR and TO_DATE functions.
Table 3-3-26 TO_CHAR Examples
Expression |
Result |
TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD HH12:MI:SS') |
'Tuesday , 06 05:39:18' |
TO_CHAR(CURRENT_TIMESTAMP, 'FMDay, FMDD HH12:MI:SS') |
'Tuesday, 6 05:39:18' |
TO_CHAR(-0.1, '99.99') |
' -.10' |
TO_CHAR(-0.1, 'FM9.99') |
'-.1' |
TO_CHAR(0.1, '0.9') |
' 0.1' |
TO_CHAR(12, '9990999.9') |
' 0012.0' |
TO_CHAR(12, 'FM9990999.9') |
'0012.' |
TO_CHAR(485, '999') |
' 485' |
TO_CHAR(-485, '999') |
'-485' |
TO_CHAR(1485, '9,999') |
' 1,485' |
TO_CHAR(1485, '9G999') |
' 1,485' |
TO_CHAR(148.5, '999.999') |
' 148.500' |
TO_CHAR(148.5, 'FM999.999') |
'148.5' |
TO_CHAR(148.5, 'FM999.990') |
'148.500' |
TO_CHAR(148.5, '999D999') |
' 148.500' |
TO_CHAR(3148.5, '9G999D999') |
' 3,148.500' |
TO_CHAR(-485, '999S') |
'485-' |
TO_CHAR(-485, '999MI') |
'485-' |
TO_CHAR(485, '999MI') |
'485 ' |
TO_CHAR(485, 'FM999MI') |
'485' |
TO_CHAR(-485, '999PR') |
'<485>' |
TO_CHAR(485, 'L999') |
'$ 485' |
TO_CHAR(485, 'RN') |
' CDLXXXV' |
TO_CHAR(485, 'FMRN') |
'CDLXXXV' |
TO_CHAR(5.2, 'FMRN') |
'V' |
TO_CHAR(12, '99V999') |
' 12000' |
TO_CHAR(12.4, '99V999') |
' 12400' |
TO_CHAR(12.45, '99V9') |
' 125' |
3.5.7 Date/Time Functions and Operators
Table 3-3-28 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 3-3-27 illustrates the behaviors of the basic arithmetic operators (+, -). For formatting functions, refer to Section 3.5.6. You should be familiar with the background information on date/time data types from Section 3.2.4.
Table 3-3-27 Date/Time Operators
Operator |
Example |
Result |
+ |
DATE '2001-09-28' + 7 |
05-OCT-01 00:00:00 |
+ |
TIMESTAMP '2001-09-28 13:30:00' + 3 |
01-OCT-01 13:30:00 |
- |
DATE '2001-10-01' - 7 |
24-SEP-01 00:00:00 |
- |
TIMESTAMP '2001-09-28 13:30:00' - 3 |
25-SEP-01 13:30:00 |
- |
TIMESTAMP '2001-09-29 03:00:00' - TIMESTAMP '2001-09-27 12:00:00' |
@ 1 day 15 hours |
In the date/time functions of Table 3-3-28 the use of the DATE and TIMESTAMP data types are interchangeable.
Table 3-3-28 Date/Time Functions
Function |
Return Type |
Description |
Example |
Result |
ADD_MONTHS(DATE, NUMBER) |
DATE |
Add months to a date; see Section 3.5.7.1 |
ADD_MONTHS('28-FEB-97', 3.8) |
31-MAY-97 00:00:00 |
CURRENT_DATE |
DATE |
Current date; see Section 3.5.7.8 |
CURRENT_DATE |
04-JUL-07 |
EXTRACT(field FROM TIMESTAMP) |
DOUBLE PRECISION |
Get subfield; see Section 3.5.7.2 |
EXTRACT(hour FROM TIMESTAMP '2001-02-16 20:38:40') |
20 |
LAST_DAY(DATE) |
DATE |
Returns the last day of the month represented by the given date. If the given date contains a time portion, it is carried forward to the result unchanged. |
LAST_DAY('14-APR-98') |
30-APR-98 00:00:00 |
LOCALTIMESTAMP [ (precision) ] |
TIMESTAMP |
Current date and time (start of current transaction); see Section 3.5.7.8 |
LOCALTIMESTAMP |
04-JUL-07 15:33:23.484 |
MONTHS_BETWEEN(DATE, DATE) |
NUMBER |
Number of months between two dates; see Section 3.5.7.3 |
MONTHS_BETWEEN('28-FEB-07', '30-NOV-06') |
3 |
NEXT_DAY(DATE, dayofweek) |
DATE |
Date falling on dayofweek following specified date; see Section 3.5.7.4 |
NEXT_DAY('16-APR-07','FRI') |
20-APR-07 00:00:00 |
NEW_TIME(DATE, VARCHAR, VARCHAR) |
DATE |
Converts a date and time to an alternate time zone |
NEW_TIME(TO_DATE '2005/05/29 01:45', 'AST', 'PST') |
2005/05/29 21:45:00 |
ROUND(DATE [, format ]) |
DATE |
Date rounded according to format; see Section 3.5.7.5 |
ROUND(TO_DATE('29-MAY-05'),'MON') |
01-JUN-05 00:00:00 |
SYS_EXTRACT_UTC(TIMESTAMP WITH TIME ZONE) |
TIMESTAMP |
Returns Coordinated Universal Time |
SYS_EXTRACT_UTC(CAST('24-MAR-11 12:30:00PM -04:00' AS TIMESTAMP WITH TIME ZONE)) |
24-MAR-11 16:30:00 |
SYSDATE |
DATE |
Current date and time |
SYSDATE |
06-AUG-07 10:06:27 |
TRUNC(DATE [, format ]) |
DATE |
Truncate according to format; see Section 3.5.7.7 |
TRUNC(TO_DATE('29-MAY-05'), 'MON') |
01-MAY-05 00:00:00 |
3.5.7.1 ADD_MONTHS
The ADD_MONTHS functions adds (or subtracts if the second parameter is negative) the specified number of months to the given date. The resulting day of the month is the same as the day of the month of the given date except when the day is the last day of the month in which case the resulting date always falls on the last day of the month.
Any fractional portion of the number of months parameter is truncated before performing the calculation.
If the given date contains a time portion, it is carried forward to the result unchanged.
The following are examples of the ADD_MONTHS function.
SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL; add_months -------------------- 13-OCT-07 00:00:00 (1 row) SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL; add_months -------------------- 28-FEB-07 00:00:00 (1 row) SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL; add_months -------------------- 29-FEB-04 00:00:00 (1 row)
3.5.7.2 EXTRACT
The EXTRACT function retrieves subfields such as year or hour from date/time values. The EXTRACT function returns values of type DOUBLE PRECISION. The following are valid field names:
YEAR
The year field
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL; date_part ----------- 2001 (1 row)
MONTH
The number of the month within the year (1 - 12)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL; date_part ----------- 2 (1 row)
DAY
The day (of the month) field (1 - 31)
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL; date_part ----------- 16 (1 row)
HOUR
The hour field (0 - 23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL; date_part ----------- 20 (1 row)
MINUTE
The minutes field (0 - 59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL; date_part ----------- 38 (1 row)
SECOND
The seconds field, including fractional parts (0 - 59)
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL; date_part ----------- 40 (1 row)
3.5.7.3 MONTHS_BETWEEN
The MONTHS_BETWEEN function returns the number of months between two dates. The result is a numeric value which is positive if the first date is greater than the second date or negative if the first date is less than the second date.
The result is always a whole number of months if the day of the month of both date parameters is the same, or both date parameters fall on the last day of their respective months.
The following are some examples of the MONTHS_BETWEEN function.
SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL; months_between ---------------- 2 (1 row) SELECT MONTHS_BETWEEN('15-OCT-06','15-DEC-06') FROM DUAL; months_between ---------------- -2 (1 row) SELECT MONTHS_BETWEEN('31-JUL-00','01-JUL-00') FROM DUAL; months_between ---------------- 0.967741935 (1 row) SELECT MONTHS_BETWEEN('01-JAN-07','01-JAN-06') FROM DUAL; months_between ---------------- 12 (1 row)
3.5.7.4 NEXT_DAY
The NEXT_DAY function returns the first occurrence of the given weekday strictly greater than the given date. At least the first three letters of the weekday must be specified - e.g., SAT. If the given date contains a time portion, it is carried forward to the result unchanged.
The following are examples of the NEXT_DAY function.
SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY') FROM DUAL; next_day -------------------- 19-AUG-07 00:00:00 (1 row) SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON') FROM DUAL; next_day -------------------- 20-AUG-07 00:00:00 (1 row)
3.5.7.5 NEW_TIME
The NEW_TIME function converts a date and time from one time zone to another. NEW_TIME returns a value of type DATE. The syntax is:
NEW_TIME(DATE, time_zone1, time_zone2)
time_zone1 and time_zone2 must be string values from the Time Zone column of the following table:
Time Zone |
Offset from UTC |
Description |
AST |
UTC+4 |
Atlantic Standard Time |
ADT |
UTC+3 |
Atlantic Daylight Time |
BST |
UTC+11 |
Bering Standard Time |
BDT |
UTC+10 |
Bering Daylight Time |
CST |
UTC+6 |
Central Standard Time |
CDT |
UTC+5 |
Central Daylight Time |
EST |
UTC+5 |
Eastern Standard Time |
EDT |
UTC+4 |
Eastern Daylight Time |
GMT |
UTC |
Greenwich Mean Time |
HST |
UTC+10 |
Alaska-Hawaii Standard Time |
HDT |
UTC+9 |
Alaska-Hawaii Daylight Time |
MST |
UTC+7 |
Mountain Standard Time |
MDT |
UTC+6 |
Mountain Daylight Time |
NST |
UTC+3:30 |
Newfoundland Standard Time |
PST |
UTC+8 |
Pacific Standard Time |
PDT |
UTC+7 |
Pacific Daylight Time |
YST |
UTC+9 |
Yukon Standard Time |
YDT |
UTC+8 |
Yukon Daylight Time |
Following is an example of the NEW_TIME function.
SELECT NEW_TIME(TO_DATE('08-13-07 10:35:15','MM-DD-YY HH24:MI:SS'),'AST', 'PST') "Pacific Standard Time" FROM DUAL; Pacific Standard Time --------------------- 13-AUG-07 06:35:15 (1 row)
3.5.7.6 ROUND
The ROUND function returns a date rounded according to a specified template pattern. If the template pattern is omitted, the date is rounded to the nearest day. The following table shows the template patterns for the ROUND function.
Table 3-3-29 Template Date Patterns for the ROUND Function
Pattern |
Description |
CC, SCC |
Returns January 1, cc01 where cc is first 2 digits of the given year if last 2 digits <= 50, or 1 greater than the first 2 digits of the given year if last 2 digits > 50; (for AD years) |
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y |
Returns January 1, yyyy where yyyy is rounded to the nearest year; rounds down on June 30, rounds up on July 1 |
IYYY, IYY, IY, I |
Rounds to the beginning of the ISO year which is determined by rounding down if the month and day is on or before June 30th, or by rounding up if the month and day is July 1st or later |
Q |
Returns the first day of the quarter determined by rounding down if the month and day is on or before the 15th of the second month of the quarter, or by rounding up if the month and day is on the 16th of the second month or later of the quarter |
MONTH, MON, MM, RM |
Returns the first day of the specified month if the day of the month is on or prior to the 15th; returns the first day of the following month if the day of the month is on the 16th or later |
WW |
Round to the nearest date that corresponds to the same day of the week as the first day of the year |
IW |
Round to the nearest date that corresponds to the same day of the week as the first day of the ISO year |
W |
Round to the nearest date that corresponds to the same day of the week as the first day of the month |
DDD, DD, J |
Rounds to the start of the nearest day; 11:59:59 AM or earlier rounds to the start of the same day; 12:00:00 PM or later rounds to the start of the next day |
DAY, DY, D |
Rounds to the nearest Sunday |
HH, HH12, HH24 |
Round to the nearest hour |
MI |
Round to the nearest minute |
Following are examples of usage of the ROUND function.
The following examples round to the nearest hundred years.
SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL; Century ------------- 01-JAN-1901 (1 row) SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL; Century ------------- 01-JAN-2001 (1 row)
The following examples round to the nearest year.
SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL; Year ------------- 01-JAN-1999 (1 row) SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL; Year ------------- 01-JAN-2000 (1 row)
The following examples round to the nearest ISO year. The first example rounds to 2004 and the ISO year for 2004 begins on December 29^{th} of 2003. The second example rounds to 2005 and the ISO year for 2005 begins on January 3^{rd} of that same year.
(An ISO year begins on the first Monday from which a 7 day span, Monday thru Sunday, contains at least 4 days of the new year. Thus, it is possible for the beginning of an ISO year to start in December of the prior year.)
SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL; ISO Year ------------- 29-DEC-2003 (1 row) SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL; ISO Year ------------- 03-JAN-2005 (1 row)
The following examples round to the nearest quarter.
SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL; Quarter -------------------- 01-JAN-07 00:00:00 (1 row) SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL; Quarter -------------------- 01-APR-07 00:00:00 (1 row)
The following examples round to the nearest month.
SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL; Month -------------------- 01-DEC-07 00:00:00 (1 row) SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL; Month -------------------- 01-JAN-08 00:00:00 (1 row)
The following examples round to the nearest week. The first day of 2007 lands on a Monday so in the first example, January 18^{th} is closest to the Monday that lands on January 15^{th}. In the second example, January 19^{th} is closer to the Monday that falls on January 22^{nd}.
SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL; Week -------------------- 15-JAN-07 00:00:00 (1 row) SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL; Week -------------------- 22-JAN-07 00:00:00 (1 row)
The following examples round to the nearest ISO week. An ISO week begins on a Monday. In the first example, January 1, 2004 is closest to the Monday that lands on December 29, 2003. In the second example, January 2, 2004 is closer to the Monday that lands on January 5, 2004.
SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL; ISO Week -------------------- 29-DEC-03 00:00:00 (1 row) SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL; ISO Week -------------------- 05-JAN-04 00:00:00 (1 row)
The following examples round to the nearest week where a week is considered to start on the same day as the first day of the month.
SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL; Week -------------------- 08-MAR-07 00:00:00 (1 row) SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL; Week -------------------- 01-MAR-07 00:00:00 (1 row)
The following examples round to the nearest day.
SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL; Day -------------------- 04-AUG-07 00:00:00 (1 row) SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL; Day -------------------- 05-AUG-07 00:00:00 (1 row)
The following examples round to the start of the nearest day of the week (Sunday).
SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL; Day of Week -------------------- 05-AUG-07 00:00:00 (1 row) SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL; Day of Week -------------------- 12-AUG-07 00:00:00 (1 row)
The following examples round to the nearest hour.
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL; Hour -------------------- 09-AUG-07 08:00:00 (1 row) SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL; Hour -------------------- 09-AUG-07 09:00:00 (1 row)
The following examples round to the nearest minute.
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL; Minute -------------------- 09-AUG-07 08:30:00 (1 row) SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL; Minute -------------------- 09-AUG-07 08:31:00 (1 row)
3.5.7.7 TRUNC
The TRUNC function returns a date truncated according to a specified template pattern. If the template pattern is omitted, the date is truncated to the nearest day. The following table shows the template patterns for the TRUNC function.
Table 3-3-30 Template Date Patterns for the TRUNC Function
Pattern |
Description |
CC, SCC |
Returns January 1, cc01 where cc is first 2 digits of the given year |
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y |
Returns January 1, yyyy where yyyy is the given year |
IYYY, IYY, IY, I |
Returns the start date of the ISO year containing the given date |
Q |
Returns the first day of the quarter containing the given date |
MONTH, MON, MM, RM |
Returns the first day of the specified month |
WW |
Returns the largest date just prior to, or the same as the given date that corresponds to the same day of the week as the first day of the year |
IW |
Returns the start of the ISO week containing the given date |
W |
Returns the largest date just prior to, or the same as the given date that corresponds to the same day of the week as the first day of the month |
DDD, DD, J |
Returns the start of the day for the given date |
DAY, DY, D |
Returns the start of the week (Sunday) containing the given date |
HH, HH12, HH24 |
Returns the start of the hour |
MI |
Returns the start of the minute |
Following are examples of usage of the TRUNC function.
The following example truncates down to the hundred years unit.
SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL; Century ------------- 01-JAN-1901 (1 row)
The following example truncates down to the year.
SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL; Year ------------- 01-JAN-1999 (1 row)
The following example truncates down to the beginning of the ISO year.
SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL; ISO Year ------------- 29-DEC-2003 (1 row)
The following example truncates down to the start date of the quarter.
SELECT TRUNC(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL; Quarter -------------------- 01-JAN-07 00:00:00 (1 row)
The following example truncates to the start of the month.
SELECT TRUNC(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL; Month -------------------- 01-DEC-07 00:00:00 (1 row)
The following example truncates down to the start of the week determined by the first day of the year. The first day of 2007 lands on a Monday so the Monday just prior to January 19^{th} is January 15^{th}.
SELECT TRUNC(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL; Week -------------------- 15-JAN-07 00:00:00 (1 row)
The following example truncates to the start of an ISO week. An ISO week begins on a Monday. January 2, 2004 falls in the ISO week that starts on Monday, December 29, 2003.
SELECT TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL; ISO Week -------------------- 29-DEC-03 00:00:00 (1 row)
The following example truncates to the start of the week where a week is considered to start on the same day as the first day of the month.
SELECT TRUNC(TO_DATE('21-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL; Week -------------------- 15-MAR-07 00:00:00 (1 row)
The following example truncates to the start of the day.
SELECT TRUNC(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL; Day -------------------- 04-AUG-07 00:00:00 (1 row)
The following example truncates to the start of the week (Sunday).
SELECT TRUNC(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL; Day of Week -------------------- 05-AUG-07 00:00:00 (1 row)
The following example truncates to the start of the hour.
SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL; Hour -------------------- 09-AUG-07 08:00:00 (1 row)
The following example truncates to the minute.
SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL; Minute -------------------- 09-AUG-07 08:30:00 (1 row)
3.5.7.8 CURRENT DATE/TIME
Postgres Plus Advanced Server provides a number of functions that return values related to the current date and time. These functions all return values based on the start time of the current transaction.
● CURRENT_DATE
● LOCALTIMESTAMP
● LOCALTIMESTAMP(precision)
LOCALTIMESTAMP can optionally be given a precision parameter which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.
SELECT CURRENT_DATE FROM DUAL; date ----------- 06-AUG-07 (1 row) SELECT LOCALTIMESTAMP FROM DUAL; timestamp ------------------------ 06-AUG-07 16:11:35.973 (1 row) SELECT LOCALTIMESTAMP(2) FROM DUAL; timestamp ----------------------- 06-AUG-07 16:11:44.58 (1 row)
Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp. Other database systems may advance these values more frequently.
3.5.8 Sequence Manipulation Functions
This section describes Postgres Plus Advanced Server’s functions for operating on sequence objects. Sequence objects (also called sequence generators or just sequences) are special single-row tables created with the CREATE SEQUENCE command. A sequence object is usually used to generate unique identifiers for rows of a table. The sequence functions, listed below, provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.
sequence.NEXTVAL sequence.CURRVAL
sequence is the identifier assigned to the sequence in the CREATE SEQUENCE command. The following describes the usage of these functions.
NEXTVAL
Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute NEXTVAL concurrently, each will safely receive a distinct sequence value.
CURRVAL
Return the value most recently obtained by NEXTVAL for this sequence in the current session. (An error is reported if NEXTVAL has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed NEXTVAL since the current session did.
If a sequence object has been created with default parameters, NEXTVAL calls on it will return successive values beginning with 1. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE command.
Important: To avoid blocking of concurrent transactions that obtain numbers from the same sequence, a NEXTVAL operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the NEXTVAL later aborts. This means that aborted transactions may leave unused "holes" in the sequence of assigned values.
3.5.9 Conditional Expressions
This section describes the SQL-compliant conditional expressions available in Postgres Plus Advanced Server.
3.5.9.1 CASE
The SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages:
CASE WHEN condition THEN result [ WHEN ... ] [ ELSE result ] END
CASE clauses can be used wherever an expression is valid. condition is an expression that returns a BOOLEAN result. If the result is TRUE then the value of the CASE expression is the result that follows the condition. If the result is FALSE any subsequent WHEN clauses are searched in the same manner. If no WHEN condition is TRUE then the value of the CASE expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is NULL.
An example:
SELECT * FROM test; a --- 1 2 3 (3 rows) SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other (3 rows)
The data types of all the result expressions must be convertible to a single output type.
The following “simple” CASE expression is a specialized variant of the general form above:
CASE expression WHEN value THEN result [ WHEN ... ] [ ELSE result ] END
The expression is computed and compared to all the value specifications in the WHEN clauses until one is found that is equal. If no match is found, the result in the ELSE clause (or a null value) is returned.
The example above can be written using the simple CASE syntax:
SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other (3 rows)
A CASE expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
3.5.9.2 COALESCE
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.
COALESCE(value [, value2 ] ... )
It is often used to substitute a default value for null values when data is retrieved for display or further computation. For example:
SELECT COALESCE(description, short_description, '(none)') ...
Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. This SQL-standard function provides capabilities similar to NVL and IFNULL, which are used in some other database systems.
3.5.9.3 NULLIF
The NULLIF function returns a null value if value1 and value2 are equal; otherwise it returns value1.
NULLIF(value1, value2)
This can be used to perform the inverse operation of the COALESCE example given above:
SELECT NULLIF(value1, '(none)') ...
If value1 is (none), return a null, otherwise return value1.
3.5.9.4 NVL
The NVL function returns the first of its arguments that is not null. NVL evaluates the first expression; if that expression evaluates to NULL, NVL returns the second expression.
NVL(expr1, expr2)
The return type is the same as the argument types; all arguments must have the same data type (or be coercible to a common type). NVL returns NULL if all arguments are NULL.
The following example computes a bonus for non-commissioned employees, If an employee is a commissioned employee, this expression returns the employees commission; if the employee is not a commissioned employee (that is, his commission is NULL), this expression returns a bonus that is 10% of his salary.
bonus = NVL(emp.commission, emp.salary * .10)
3.5.9.5 NVL2
NVL2 evaluates an expression, and returns either the second or third expression, depending on the value of the first expression. If the first expression is not NULL, NVL2 returns the value in expr2; if the first expression is NULL, NVL2 returns the value in expr3.
NVL2(expr1, expr2, expr3)
The return type is the same as the argument types; all arguments must have the same data type (or be coercible to a common type).
The following example computes a bonus for commissioned employees - if a given employee is a commissioned employee, this expression returns an amount equal to 110% of his commission; if the employee is not a commissioned employee (that is, his commission is NULL), this expression returns 0.
bonus = NVL2(emp.commission, emp.commission * 1.1, 0)
3.5.9.6 GREATEST and LEAST
The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions.
GREATEST(value [, value2 ] ... ) LEAST(value [, value2 ] ... )
The expressions must all be convertible to a common data type, which will be the type of the result. Null values in the list are ignored. The result will be null only if all the expressions evaluate to null.
Note that GREATEST and LEAST are not in the SQL standard, but are a common extension.
3.5.10 Aggregate Functions
Aggregate functions compute a single result value from a set of input values. The built-in aggregate functions are listed in the following tables.
Table 3-3-31 General-Purpose Aggregate Functions
Function |
Argument Type |
Return Type |
Description |
AVG(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
NUMBER for any integer type, DOUBLE PRECISION for a floating-point argument, otherwise the same as the argument data type |
The average (arithmetic mean) of all input values |
COUNT(*) |
BIGINT |
Number of input rows | |
COUNT(expression) |
Any |
BIGINT |
Number of input rows for which the value of expression is not null |
MAX(expression) |
Any numeric, string, or date/time type |
Same as argument type |
Maximum value of expression across all input values |
MIN(expression) |
Any numeric, string, or date/time type |
Same as argument type |
Minimum value of expression across all input values |
SUM(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
BIGINT for SMALLINT or INTEGER arguments, NUMBER for BIGINT arguments, DOUBLE PRECISION for floating-point arguments, otherwise the same as the argument data type |
Sum of expression across all input values |
It should be noted that except for COUNT, these functions return a null value when no rows are selected. In particular, SUM of no rows returns null, not zero as one might expect. The COALESCE function may be used to substitute zero for null when necessary.
The following table shows the aggregate functions typically used in statistical analysis. (These are separated out merely to avoid cluttering the listing of more-commonly-used aggregates.) Where the description mentions N, it means the number of input rows for which all the input expressions are non-null. In all cases, null is returned if the computation is meaningless, for example when N is zero.
Table 3-3-32 Aggregate Functions for Statistics
Function |
Argument Type |
Return Type |
Description |
CORR(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Correlation coefficient |
COVAR_POP(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Population covariance |
COVAR_SAMP(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Sample covariance |
REGR_AVGX(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Average of the independent variable (sum(X) / N) |
REGR_AVGY(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Average of the dependent variable (sum(Y) / N) |
REGR_COUNT(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Number of input rows in which both expressions are nonnull |
REGR_INTERCEPT(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs |
REGR_R2(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Square of the correlation coefficient |
REGR_SLOPE(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Slope of the least-squares-fit linear equation determined by the (X, Y) pairs |
REGR_SXX(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Sum (X^{2}) – sum (X)^{2} / N (“sum of squares” of the independent variable) |
REGR_SXY(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Sum (X*Y) – sum (X) * sum (Y) / N (“sum of products” of independent times dependent variable) |
REGR_SYY(Y, X) |
DOUBLE PRECISION |
DOUBLE PRECISION |
Sum (Y^{2}) – sum (Y)^{2} / N (“sum of squares” of the dependent variable) |
STDDEV(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER |
Historic alias for STDDEV_SAMP |
STDDEV_POP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER |
Population standard deviation of the input values |
STDDEV_SAMP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER |
Sample standard deviation of the input values |
VARIANCE(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER |
Historical alias for VAR_SAMP |
VAR_POP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER |
Population variance of the input values (square of the population standard deviation) |
VAR_SAMP(expression) |
INTEGER, REAL, DOUBLE PRECISION, NUMBER |
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER |
Sample variance of the input values (square of the sample standard deviation) |
3.5.11 Subquery Expressions
This section describes the SQL-compliant subquery expressions available in Postgres Plus Advanced Server. All of the expression forms documented in this section return Boolean (TRUE/FALSE) results.
3.5.11.1 EXISTS
The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is TRUE; if the subquery returns no rows, the result of EXISTS is FALSE.
EXISTS(subquery)
The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.
The subquery will generally only be executed far enough to determine whether at least one row is returned, not all the way to completion. It is unwise to write a subquery that has any side effects (such as calling sequence functions); whether the side effects occur or not may be difficult to predict.
Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally uninteresting. A common coding convention is to write all EXISTS tests in the form EXISTS(SELECT 1 WHERE ...). There are exceptions to this rule however, such as subqueries that use INTERSECT.
This simple example is like an inner join on deptno, but it produces at most one output row for each dept row, even though there are multiple matching emp rows:
SELECT dname FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno); dname ------------ ACCOUNTING RESEARCH SALES (3 rows)
3.5.11.2 IN
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of IN is TRUE if any equal subquery row is found. The result is FALSE if no equal row is found (including the special case where the subquery returns no rows).
expression IN (subquery)
Note that if the left-hand expression yields NULL, or if there are no equal right-hand values and at least one right-hand row yields NULL, the result of the IN construct will be NULL, not FALSE. This is in accordance with SQL’s normal rules for Boolean combinations of null values.
As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.
3.5.11.3 NOT IN
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of NOT IN is TRUE if only unequal subquery rows are found (including the special case where the subquery returns no rows). The result is FALSE if any equal row is found.
expression NOT IN (subquery)
Note that if the left-hand expression yields NULL, or if there are no equal right-hand values and at least one right-hand row yields NULL, the result of the NOT IN construct will be NULL, not TRUE. This is in accordance with SQL’s normal rules for Boolean combinations of null values.
As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.
3.5.11.4 ANY/SOME
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ANY is TRUE if any true result is obtained. The result is FALSE if no true result is found (including the special case where the subquery returns no rows).
expression operator ANY (subquery) expression operator SOME (subquery)
SOME is a synonym for ANY. IN is equivalent to = ANY.
Note that if there are no successes and at least one right-hand row yields NULL for the operator’s result, the result of the ANY construct will be NULL, not FALSE. This is in accordance with SQL’s normal rules for Boolean combinations of null values.
As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.
3.5.11.5 ALL
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ALL is TRUE if all rows yield true (including the special case where the subquery returns no rows). The result is FALSE if any false result is found. The result is NULL if the comparison does not return FALSE for any row, and it returns NULL for at least one row.
expression operator ALL (subquery)
NOT IN is equivalent to <> ALL. As with EXISTS, it’s unwise to assume that the subquery will be evaluated cmpletely.