PostgreSQL Functions

• Aggregate functions
• Scalar Functions

Aggregate Functions:

Examples:

pearl=# select * from siva ;
pearl=# select max(age) from siva;
pearl=# select min(age) from siva;
pearl=# select avg(salary) from siva;
pearl=# select count(id) from siva;
pearl=# select sum(salary) from siva;
pearl=# select * from siva order by salary;
pearl=# select * from siva order by salary desc;
pearl=# select age from siva group by age;
pearl=# select age from siva group by age having age=27;

Scalar Functions:

date functions:

pearl=# select age(timestamp '2001-04-10', timestamp '1957-06-13');
pearl=# select age(timestamp '2001-04-10');
pearl=# select clock_timestamp();
pearl=# select current_date;
pearl=# select current_time;
pearl=# select current_timestamp;
pearl=# select date_part('hour', timestamp '2001-02-16 20:38:40');
pearl=# select date_part('month', interval '2 years 3 months');
pearl=# select date_trunc('hour', timestamp '2001-02-16 20:38:40');
pearl=# select date_trunc('hour', interval '2 days 3 hours 40 minutes');
pearl=# select localtime;
pearl=# select localtimestamp;
pearl=# select make_date(2013, 7, 15);
pearl=# select make_time(8, 15, 23.5);
pearl=# select make_timestamp(2013, 7, 15, 8, 15, 23.5);
pearl=# select now();
pearl=# select statement_timestamp();
pearl=# select timeofday();
pearl=# select transaction_timestamp();
pearl=# SELECT to_date('20170103','YYYYMMDD');
pearl=# SELECT TO_TIMESTAMP('2017-03-31 9:30:20','YYYY-MM-DD HH:MI:SS');

numeric functions:

pearl=# select abs(5);
pearl=# select abs(-5);
pearl=# select ceil(7.43);
pearl=# select ceiling(7.43);
pearl=# select floor(7.43);
pearl=# select greatest(4,3,5,6,2,8,1);
pearl=# select least(4,3,5,6,2,8,1);
pearl=# select mod(15,4);
pearl=# select pi();
pearl=# select power(4,4);
pearl=# select pow(4,4);
pearl=# select round(6.68456);
pearl=# select round(6.68456,2);
pearl=# select trunc(123.9);
pearl=# select sqrt(81);

string functions:

pearl=# select ascii('A');
pearl=# select char_length('muthu');
pearl=# select length('siva');
pearl=# select concat('muthu','siva');
pearl=# select concat_ws(',','first_name','second_name');
pearl=# select left('muthusiva',5);
pearl=# select right('muthusiva',4);
pearl=# select lower('MUTHU');
pearl=# select upper('muthu');
pearl=# select ltrim(' siva');
pearl=# select rtrim('siva ');
pearl=# select trim(' siva ');
pearl=# select trim(leading 'x' from 'xxxsivaxxx');
pearl=# select trim(both 'x' from 'xxxsivaxxx');
pearl=# select trim(trailing 'x' from 'xxxsivaxxx');
pearl=# SELECT REGEXP_SPLIT_TO_ARRAY('muthu siva', E'\s+');
pearl=# SELECT REGEXP_SPLIT_TO_TABLE('muthu siva', E'\s+');
pearl=# select repeat('siva',3);
pearl=# select replace('muthu siva','muthu','pearl');
pearl=# select reverse('avisuhtum');
pearl=# select rpad('muthu',10,''); pearl=# select lpad('muthu',10,'');
pearl=# select substring('iammuthusiva',4);
pearl=# select substring('iammuthusiva' from 4);
pearl=# select substring('iammuthusiva',4,5);

conversion functions:

pearl=# select to_char(current_timestamp, 'HH12:MI:SS');
pearl=# select to_char(interval '15h 2m 12s', 'HH24:MI:SS');
pearl=# select to_char(125, '999');
pearl=# select to_date('05 Dec 2000', 'DD Mon YYYY');
pearl=# select to_number('12,454.8-', '99G999D9S');
//visite https://www.postgresqltutorial.com/postgresql-to_number/
pearl=# select to_timestamp('05 Dec 2000', 'DD Mon YYYY');