Functions

The following table describes the IBM® Netezza® SQL functions and analytic functions that display in the nzsql command help.

Table 1. Netezza SQL functions
Function Description Syntax
AVG Returns the average of the expression. AVG(column reference | value expression | *) over(window_spec)
COUNT Returns the number of rows in the query. COUNT(column reference | value expression | *) over(window_spec)
CURRENT CATALOG Returns the current catalog name (database name) CURRENT_CATALOG
CURRENT DATE Returns the current date CURRENT_DATE
CURRENT PATH Returns the current PATH value CURRENT_PATH
CURRENT SCHEMA Returns the current schema name (user name) CURRENT_SCHEMA
CURRENT TIME Returns the current local time CURRENT_TIME
CURRENT TIMESTAMP Returns the current date and time CURRENT_TIMESTAMP
CURRENT TRANSACTION PATH Returns the current path value after changing the path within a transaction or stored procedure. CURRENT_TX_PATH
CURRENT TRANSACTION SCHEMA Returns the name of the current schema inside a transaction block or procedure body. The value is null unless a schema is set using SET SCHEMA inside the transaction block or stored procedure. CURRENT_TX_SCHEMA
CURRENT USER Returns the current user name CURRENT_USER
DATE PART Extracts subfield from date/time value or extracts subfield from interval value. Similar to EXTRACT DATE_PART('text', timestamp)

DATE_PART('text', interval)

DATE TRUNC Truncates the date to a specified precision DATE_TRUNC(text, timestamp)
DENSE RANK Calculates the rank of a row in an ordered group of rows. DENSE_RANK() over(window_spec)
EXTRACT Extracts the subfield from date/time value or the subfield from interval value EXTRACT(identifier from timestamp)

EXTRACT(identifier from interval)

FIRST VALUE Returns the first value in an ordered set of values. FIRST_VALUE(column reference | value expression | *) over(window_spec)
LAG Provides access to more than one row of a table at the same time without a self-join at a given offset before that position. LAG(value_expression [, offset [, default]]) over(window_spec)
LAST VALUE Returns the last value in an ordered set of values. LAST_VALUE(column reference | value expression | *) over(window_spec)
LEAD Provides access to more than one row of a table at the same time without a self-join at a given offset beyond that position. LEAD(value_expression [, offset [, default]]) over(window_spec)
LOWER Converts a string to lowercase LOWER(string)
MAX Returns the maximum value of the expression. MAX(column reference | value expression | *) over(window_spec)
NOW Returns the current date and time (equivalent to current_timestamp) NOW()
POSITION Locates the specified substring POSITION(substring in string)
RANK Calculates the rank of a value in a group of values. RANK() over(window_spec)
ROW NUMBER Assigns a unique number to each row to which it is applied. ROW_NUMBER() over(window_spec)
STDDEV Returns the standard deviation of the expression, which is the square root of the variance. STDDEV(column reference | value expression | *) over(window_spec)
STDDEV POPULATION Returns the population standard deviation, this is the same as the square root of the var_pop function. STDDEV_POP(column reference | value expression | *) over(window_spec)
STDDEV SAMPLE Returns the sample standard deviation, this is the same as the square root of the var_samp function. STDDEV_SAMP(column reference | value expression | *) over(window_spec)
SUBSTRING Extracts a substring from a string SUBSTRING(string [from integer] [for integer])
SUM Returns the sum of the expression. SUM(column reference | value expression | *) over(window_spec)
TIMEOFDAY Returns high-precision date and time TIMEOFDAY()
TIMESTAMP Converts a date to a timestamp

Combines date and time into a timestamp

TIMESTAMP(date)

TIMESTAMP(date, time)

TO CHAR Converts the following data types into string:
  • timestamp
  • int4/int8
  • real/double precision
  • numeric
to_char(timestamp, text)

to_char(int, text)

to_char(double precision, text)

to_char(numeric, text)

TO DATE Converts a string to a date to_date(text, text)
TO NUMBER Converts a string to a numeric to_number(text, text)
TO TIMESTAMP Converts a string to a timestamp to_timestamp(text, text)
TRIM Removes the longest string that contains only the characters (a space by default) from the beginning/end/both ends of the string TRIM([leading | trailing | both] [characters] from string)
UPPER Converts a string to uppercase text UPPER(string)
VARIANCE Returns the variance of the expression. VARIANCE(column reference | value expression | *) over(window_spec)
VARIANCE POPULATION Returns the population variance. VAR_POP(column reference | value expression | *) over(window_spec)
VARIANCE SAMPLE Returns the sample variance. VAR_SAMP(column reference | value expression | *) over(window_spec)