Functions

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

Table 1. Netezza Performance Server 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)