PostgreSQL Functions

In general terms, a function is a set of statements that take inputs, do some specific computation and produces output.

Types of Functions: Single Row Functions: These are functions that operate on individual records and give output per record.
Multiple Row Functions: These functions focus on obtaining summary information on average for a group of records.

Single Row Functions :

String Functions :

These functions are used to manipulate strings.

Function Return Type Description Example Result
string || string Text String Concatenation SELECT 'Post||'greSQL' PostgreSQL
char_length(string) Int Number of character in the string SELECT char_length('Jack') 4
Position(substr in string) Int Location of specified string SELECT position('hn' in John) 3

Number Functions:

These functions work on numeric data.

Function Return Type Description Example Result
|/ (number) Int Square root of the given number Select |/(25) 5
power(a,b) Int Return a^b SELECT power(2,3) 8
Mod(y,x) Int Return the reminder after dividing y to x SELECT mod(25,5) 0

Date Functions :

These functions work on data of date or time stamp type.

Function Return Type Description Example Result
now() Timestamp with timezone current date and time Select now() 2019-10-11 15:05:38.710882 +5:30
justify_days(interval) Interval Adjust interval so 30 days time period are represented as months SELECT justify_days(interval '35 days') 1 month 5 days
Extract(fields from source) Double precision Retrives subfields such as year or hour from date/time value SELECT extract(Year from hire_date) from employees 1 Year

Data Type Formatting or Conversion Functions: :
Function Return Type Description Example
to_char(timestamp, text) text Converting timestamp to string to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text) text Converting interval to string to_char(interval '15h 12m 36s', 'HH12:MI:SS')
to_char(int, text) text Converting integer to string to_char(125, '999')
to_char(double precision, text) text Converting real/double precision to string to_char(128.8::real, '999D9')
to_char(numeric, text) text Converting numeric to string to_char(-125.8, '999D99S')
to_char(text, text) date Converting string to date to_date('05 DEC 2018', 'DD MON YYYY')
to_number(text, text) numeric Converting string to numeric to_number('12,454.8-', '99g999D9S')
to_timestamp(text, text) timestamp with timezone Converting string to timestamp to_timestamp('05 DEC 2018', 'DD MON YYYY')

Multiple Row Functions :

Multiple Row function is also called group function or aggregate functions..

Function Return Type Description
max(col_name) numeric Max function is used to return the Maximum value of a given column
min(col_name) numeric Min function is used to return the Minimum value of a given column
sum(col_name) numeric Sum function is used to return the sum(total) of a given column.
count(col_name) numeric Count function is used to return the number of records present in a given column
avg(col_name) numeric Avg function is used to return the Average value of a given column

Visit :


* You must be logged in to add comment.