Numeric Function in PostgreSQL
A vast range of mathematical and statistical calculations on numerical data types are made possible by numerical functions, which are essential tools in database systems.PostgreSQL, a strong relational database, supports several numeric types and has many functions and operators for manipulation.Understanding these characteristics is essential for correct data processing, especially in high-accuracy applications.
Numeric Data Types in PostgreSQL
To meet processing needs, PostgreSQL supports numerous numeric data types with different storage, ranges, and precision.
Integer Types: PostgreSQL has several integer types for storing whole integers, each with different storage space, value range, and performance. The SMALLINT type stores integers from -32768 to +32767 in 2 bytes, saving disc space when its range is sufficient. INTEGER, sometimes known as INT4, is a 4-byte integer that accepts values from -2147483648 to +2147483647. It balances value range, storage footprint, and operational performance, making it the standard integer storage option.
Arbitrary Precision Numbers (NUMERIC / DECIMAL): Both NUMERIC and DECIMAL data types in PostgreSQL support arbitrary precision numbers. NUMERIC values produce precise results for addition, subtraction, and multiplication, making them ideal for storing money and other quantities. Precision and scale define these types. Precision is the entire count of significant digits before and after the decimal point, whereas scale is the fractional component to the right.
Floating-Point Types (REAL / DOUBLE PRECISION): REAL and DOUBLE PRECISION are variable-precision, inexact numeric types in PostgreSQL that follow IEEE Standard 754 for Binary Floating-Point Arithmetic. REAL utilises 4 bytes of storage and has at least 6 decimal digits, often 1E-37 to 1E+37. The DOUBLE PRECISION type uses 8 bytes and has at least 15 decimal digits with a range of 1E-307 to 1E+308.
Monetary Type (MONEY): PostgreSQL takes integer, floating-point, and currency input . The output format is usually locale-specific. To avoid complications while adding MONEY data into a database, especially during dump restoration, the lc_monetary parameter must be consistent due to locale sensitivity. NUMERIC, INT, and BIGINT values can be cast to MONEY for conversions. Due to their inexact nature, floating-point numbers should not be used for money, thus cast them to NUMERIC first and then to MONEY to avoid rounding problems.
Code Example:
DROP TABLE IF EXISTS numeric_demo;
CREATE TABLE numeric_demo (
id SERIAL PRIMARY KEY,
small_val SMALLINT,
int_val INTEGER,
numeric_val NUMERIC(10,2),
real_val REAL,
double_val DOUBLE PRECISION,
money_val MONEY
);
INSERT INTO numeric_demo
(small_val, int_val, numeric_val, real_val, double_val, money_val)
VALUES
(32000, 2000000000, 12345.67, 3.14159, 1E+100, '12345.67');
SELECT * FROM numeric_demo;
SELECT '₹' || TO_CHAR(money_val::NUMERIC, 'FM999,999,999,990.00') AS money_in_rupees
FROM numeric_demo;
SELECT '₹' || TO_CHAR(numeric_val, 'FM999,999,999,990.00') AS numeric_in_rupees
FROM numeric_demo;Output:
DROP TABLE
CREATE TABLE
INSERT 0 1
id | small_val | int_val | numeric_val | real_val | double_val | money_val
----+-----------+------------+-------------+----------+------------+------------
1 | 32000 | 2000000000 | 12345.67 | 3.14159 | 1e+100 | $12,345.67
(1 row)
money_in_rupees
-----------------
₹12,345.67
(1 row)
numeric_in_rupees
-------------------
₹12,345.67
(1 row)Built-in Numeric Functions and Operators
A full range of mathematical operators and functions for numeric types are offered by PostgreSQL.
Mathematical Operators: PostgreSQL has many mathematical operators for arithmetic and bitwise operations on numeric data types. Addition (+), subtraction (-), multiplication (*), and division (/). Divides integral types like smallint, integer, and bigint to zero, discarding fractions. Other crucial operators are modulo (%), exponentiation (^), square root (|/), and cube root (||/). Also available is the absolute value operator (@).
PostgreSQL supports bitwise operators like AND (&), OR (|), exclusive OR (#), NOT (~), shift left (<<), and shift right (>>) for integral data types. Multiplication, division, modulo, exponentiation, addition, and subtraction follow normal precedence rules in expressions with several operators. Parentheses can change default precedence rules. User-defined operators can be used in PostgreSQL with a syntax comparable to built-in operators. Operators are listed in the pg_operator system catalogue.
Mathematical Functions: PostgreSQL has many mathematical functions for numeric data types beyond basic arithmetic operators. These functions often take numeric arguments and return the same data type. Double precision functions’ precision and behaviour, especially in edge circumstances, may be affected by the host system’s C library implementation.
Random Number Functions: PostgreSQL’s built-in mathematical functions generate random numbers. Random(), the main function, returns a random floating-point value from 0.0 to 1.0. Random_normal() draws a random value from a normal distribution and can take parameters for the mean (defaulting to 0.0) and stddev (defaulting to 1.0).
Trigonometric and Hyperbolic Functions: PostgreSQL’s built-in mathematical functions include robust trigonometric and hyperbolic functions, mostly for double precision. These capabilities are necessary for engineering and scientific computations.
Data Type Formatting Functions: The sophisticated Data Type Formatting Functions in PostgreSQL convert numeric types (integers, floating-point values, and decimals), date/time types, and intervals into formatted texts and vice versa. Commonly, these functions take two arguments: the value to format and a template text that determines the output or input format.
User-Defined Numeric Functions
SQL, PL/pgSQL, C, PL/Python, and V8 can write custom PostgreSQL functions. This flexibility helps complex numerical approaches without built-in functions.
PL/pgSQL Functions: PL/pgSQL functions define variables, IF/ELS and CASE statements, and loops. For example, PL/pgSQL allows recursive factorial functions. For PL/pgSQL functions, the execution cost is set to 100 by default.
C Language Functions: Functions in the C language have a default cost of 1 and typically provide faster execution. More complicated functions, such custom output functions for user-defined data types like complex numbers, can be made with their help.
PL/V8 (JavaScript) Functions: When it comes to mathematical operations, PL/V8 (JavaScript) functions can be 10–20 times faster than their SQL counterparts.
Volatility Categories: The performance of the query optimiser depends on whether the volatility of the functions being defined is VOLATILE, STABLE, or IMMUTABLE. The optimiser can pre-evaluate them at query planning time because IMMUTABLE functions never change the database and always return the same result for the same parameters. For numerical functions with constant inputs, this is quite advantageous. While VOLATILE functions can change the database and provide different results on subsequent calls, STABLE functions only return the same result within a single statement.
Aggregate Numeric Functions
PostgreSQL aggregate numeric functions are useful tools for calculating a summary from input numbers. These functions are essential for statistical analysis and data summarisation. COUNT, SUM, AVG, MIN, and MAX are common numeric aggregate functions. Except for COUNT(*), most aggregate functions only examine non-null input while calculating. SUM on all NULL values returns NULL, not zero, although COALESCE can transform NULL results to a default like zero.
The GROUP BY clause aggregates rows by one or more columns and applies the aggregate function to each group, returning a single summary row per group. In version 9.4, PostgreSQL introduced an optional FILTER clause with aggregates that allows rows to be conditionally included in an aggregation, making it a clearer and faster alternative to CASE WHEN expressions.
For advanced statistical computations, PostgreSQL offers ordered-set and hypothetical-set aggregates. Percentile_cont, percentile_disc, rank, dense_rank, percent_rank, and cume_dist require an ORDER BY clause within a WITHIN GROUP construct since their outcomes depend on input value ordering.
Extended PostgreSQL lets users construct custom aggregate functions with CREATE AGGREGATE. State transition functions (SFUNC) update an internal state for each input tuple, and optionally a final function (FINALFUNC) computes the aggregate’s outcome from the accumulated state. They can be written in SQL or procedural languages. PostgreSQL also offers parallel aggregation, where background workers provide partial results, which the master backend collects and re-aggregates to produce the final result, improving performance for huge datasets. All basic SQL aggregate methods can be used as window functions to calculate across a “window” of related rows without collapsing the output into one row per group.
Performance Tuning
It’s critical to comprehend how query performance is affected by numeric functions.
- An essential tool for examining the execution strategy and projected expenses of queries containing numerical operations and functions is the EXPLAIN command.
- For user-defined functions, the query planner uses the COST parameter to help choose the optimal execution plan.
- The optimiser in PostgreSQL has the ability to “peek” into the code of SQL functions and optimise their execution by using indexes and possibly collapsing repeated calculations.Compared to “black boxes” in other languages, this is a major advantage.
To conclude, PostgreSQL handles numerical data well and adaptably. Its wide library of built-in functions and operators, numerous numeric types, and custom functions and aggregates lets developers precisely execute complex computations and optimise efficiency for a variety of application requirements.