Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 3.5 Functions and Operators

Previous PageTable Of ContentsNext Page

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 10n, 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 29th of 2003. The second example rounds to 2005 and the ISO year for 2005 begins on January 3rd 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 18th is closest to the Monday that lands on January 15th. In the second example, January 19th is closer to the Monday that falls on January 22nd.

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 19th is January 15th.

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 (X2) – 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 (Y2) – 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.

Previous PageTable Of ContentsNext Page