PostgreSQL, a powerful open-source relational database management system, offers a rich set of functions that extend its capabilities. These functions enable users to perform various tasks, such as data manipulation, custom calculations, and procedural programming, making PostgreSQL highly versatile and suitable for diverse database applications.
In this blog, let us discuss the mathematical and string functions of PostgreSOL in detail.
Mathematical Function:
* CEIL
* FLOOR
* RANDOM
* SET SEED
* ROUND
* POWER
CEIL
The CEIL function, short for "ceiling," is a mathematical operation in programming that takes a number as input and returns the smallest integer value that is greater than or equal to the given number. This ensures that the resulting value is always a whole number, rounding up when necessary.
Syntax: CEIL(number)
Example: Select Column_name,ceil(sales)from Table_name
FLOOR
The FLOOR function, a mathematical operation in programming, accepts a number as input and produces the largest integer value that is equal to or less than the given number. It ensures that the result is always a whole number by rounding down when necessary, maintaining the integer property.
Syntax: FLOOR(number)
Example: Select Column_name,floor(sales) from Table_name
RANDOM
The RANDOM function is a handy tool in programming that generates a random number within a specified range. When used, it produces a random decimal value between 0 and 1. This allows developers to introduce variability and randomness in their applications, simulations, or any scenario requiring unpredictable outcomes.
Syntax: RANDOM()
It will generate a random number between 0 and 1
Example: Select random ( )
This query will return a random decimal value between 10 and 20
Select random( ) * (20-10) + 10;
This query will return a random integer
SET SEED
In PostgreSQL, if you set the seed using the set seed() function, the random() function will generate a predictable sequence of random numbers based on that seed. This means that calling random() multiple times with the same seed will produce the same sequence of random numbers. It allows for replicable results and is useful for scenarios where repeatable random sequences are desired, such as in testing or simulations.
Syntax: SETSEED(seed)
Example: SELECT SETSEED(0.5);
ROUND
The ROUND function in programming is used to round a number to a specified number of decimal places. By providing the desired decimal place value as an argument, the ROUND function ensures that the resulting number is rounded and limited to the specified decimal precision.
Syntax: round(number)
Example: SELECT Column_name,ROUND (Column_name) FROM Table_name
POWER
The POWER function in programming calculates the result of raising a given number (m) to the power of another number (n). It computes m raised to the nth power, yielding the value of m multiplied by itself n times.
Syntax: POWER(number)
Example: SELECT Column_name, power(Column_name,2) FROM Table_name;
STRING FUNCTION
* LENGTH
* UPPER & LOWER
* Replace
LENGTH
The LENGTH function in programming is used to determine the size of a given string by counting the number of characters it contains. It provides the length of the specified string, measuring the number of characters present within it, which aids in various string manipulation and analysis tasks.
Syntax: Length(string)
Example: SELECT Column_name, Length (Column_name) as charecter FROM Table_name
UPPER & LOWER
The UPPER and LOWER functions in programming allow for the conversion of all characters in a given string to uppercase or lowercase, respectively. These functions transform the specified string to either all uppercase or all lowercase characters, ensuring consistent case formatting for comparison, display, or manipulation purposes, among others.
Syntax: upper( )
Syntax: lower( )
Example: SELECT upper(string) from Table_name;
Example: SELECT lower(string) from Table_name;
REPLACE
The REPLACE function in programming is used to modify a string by replacing all instances of a specified substring with a new value. By utilizing the REPLACE function, developers can easily update and transform strings, replacing specific patterns, words, or characters with desired alternatives, resulting in customized and modified text output.
Syntax: Replace(string);
Example: select Column_name, replace(Column_name, ’value’’, 'replace_value') as new_contry from Table_name;