Miu Tech

  PostgreSQL   ARRAY_AGG   function is used to concatenate the input values including null into an array. To understand the  ARRAY_AGG  function, consider the table  COMPANY  having records as follows − testdb # select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich - Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South - Hall | 45000 7 | James | 24 | Houston | 10000 ( 7 rows ) Now, based on the above table, suppose you want to use the ARRAY_AGG, you can do so by using the following command − testdb =# SELECT ARRAY_AGG ( SALARY ) FROM COMPANY ; The above given PostgreSQL statement will produce the following result − array_agg ---------------------...

  PostgreSQL   SUM   function is used to find out the sum of a field in various records. To understand the  SUM  function consider the table  COMPANY  having records as follows − testdb # select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich - Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South - Hall | 45000 7 | James | 24 | Houston | 10000 ( 7 rows ) Now, based on the above table, suppose you want to calculate the total of all the salary, then you can do so by using the following command − testdb # SELECT SUM(salary) FROM company; The above given PostgreSQL statement will produce the following result − sum -------- 260000 (1 row) You can take the sum of variou...

  PostgreSQL   AVG   function is used to find out the average of a field in various records. To understand the  AVG  function, consider the table  COMPANY  having records as follows − testdb # select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich - Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South - Hall | 45000 7 | James | 24 | Houston | 10000 ( 7 rows ) Now, based on the above table, suppose you want to calculate the average of all the SALARY, then you can do so by using the following command − testdb =# SELECT AVG ( SALARY ) FROM COMPANY ; The above given PostgreSQL statement will produce the following result − avg ------------------ 37142.8571428...

  PostgreSQL   MIN   function is used to find out the record with minimum value among a record set. To understand the  MIN  function, consider the table  COMPANY  having records as follows − testdb # select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich - Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South - Hall | 45000 7 | James | 24 | Houston | 10000 ( 7 rows ) Now, based on the above table, suppose you want to fetch minimum value of salary, then you can do so by simply using the following command − testdb =# SELECT MIN ( salary ) FROM company ; The above given PostgreSQL statement will produce the following result − min ------- 10000 (1 row) You can find a...

  PostgreSQL   MAX   function is used to find out the record with maximum value among a record set. To understand the  MAX  function, consider the table  COMPANY  having records as follows − testdb # select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich - Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South - Hall | 45000 7 | James | 24 | Houston | 10000 ( 7 rows ) Now, based on the above table, suppose you want to fetch maximum value of SALARY, then you can do so by simply using the following command − testdb =# SELECT MAX ( salary ) FROM COMPANY ; The above given PostgreSQL statement will produce the following result − max ------- 85000 (1 row) You can find a...

  PostgreSQL   COUNT   function is the simplest function and very useful in counting the number of records, which are expected to be returned by a SELECT statement. To understand the  COUNT  function, consider the table  COMPANY  having records as follows − testdb # select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich - Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South - Hall | 45000 7 | James | 24 | Houston | 10000 ( 7 rows ) Now, based on the above table, suppose you want to count the total number of rows in this table, then you can do it as follows − testdb =# SELECT COUNT (*) FROM COMPANY ; The above given PostgreSQL statement will produce the following re...

  PostgreSQL   functions , also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code. Functions can be created in a language of your choice like SQL, PL/pgSQL, C, Python, etc. Syntax The basic syntax to create a function is as follows − CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; [...] BEGIN < function_body > [...] RETURN { variable_name | value } END; LANGUAGE plpgsql; Where, function-name specifies the name of the function. [OR REPLACE] option allows modifying an existing function. The function must contain a return statement. RETURN clause specifies that data type you are going to return from the functio...