Some useful Postgres Functions & Clauses

PostgreSQL (or Postgres) is one of the most advanced and powerful object-relational databases out there. Postgres can handle big tables with a large number of rows easily.

It’s been a couple of years since I started using PostgreSQL as the main database. One thing which is clear to me is that if you use Postgres wisely, it does much of the back-end work at the database level.

Let’s look at some of the functions and clauses of Postgres which I personally find very useful.

1. SIMILAR TO

Suppose you want to run a LIKEquery, but you want to do it for multiple expressions. Consider the following query which will select all the companies which contain apple or google or microsoft in thecolumn_name:

SELECT * FROM companies 
WHERE (company_name LIKE '%apple%' OR company_name LIKE '%google%' OR company_name LIKE '%microsoft%');

You can rewrite the above LIKEquery using SIMILAR_TO so that you don’t have to write multiple OR conditions:

SELECT * FROM companies 
WHERE company_name SIMILAR TO '%(apple|google|microsoft)%';

2. DISTINCT ON

No doubt, DISTINCT is a very useful keyword in SQL which is used to selected distinct values from a column.

DISTINCT ON is perfect when you have groups of data that are similar and want to get a single record out of each group, based on a specific ordering.

Suppose we want to find the employee with the highest salary in each department. The naive solution would be to create a subquery that selects department and max salary from that department and then finding the employee based on the subquery:

SELECT  * FROM employees 
WHERE
(department, salary) IN (
    SELECT department, MAX(salary) FROM employees
    GROUP BY department
);

This can be done much easier by DISTINCT ON:

SELECT 
    DISTINCT ON (department) * FROM employees 
ORDER BY department, salary DESC;

3. Aggregate Functions

The aggregation functions are one of my favorites and I use them very often. Aggregate functions compute a single result from a set of input values. These are often used along with the GROUP BY.

• ARRAY_AGG

This query will return department and array of employee names for each department:

SELECT 
    department, ARRAY_AGG(emp_name) 
FROM employees
GROUP BY department;

• JSON_AGG

​This will return JSONof employees for each department

• STRING_AGG

STRING_AGG will return the string of employee names separated by the delimiter for each department:

SELECT 
    department, STRING_AGG(emp_name, ' | ') 
FROM employees
GROUP BY department;

4. EXTRACT

TheEXTRACT() function retrieves a field such as a year, month, and day from a date/time value.

Suppose you want to find the joining year for each employee, but you have the field with the joining_date, this query will do the job:

SELECT 
    emp_name, EXTRACT(YEAR FROM joining_date)
FROM employees;

You can extract a lot of stuff from the date/time value. More here.

5. CASE

The PostgreSQL CASE expression is similar to if/else statement in another programming language.

Suppose you want to tag the employees in 3 categories (Low, Medium, High) based on their salaries:

SELECT emp_name,
       CASE
           WHEN salary < 30000 THEN 'Low'
           WHEN (salary >= 30000 AND salary < 70000) THEN 'Medium'
           WHEN salary >= 70000 THEN 'High'
       END category
FROM employees;

We can also use CASE along with aggregate functions. Suppose you want to count the number of employees in each category:

SELECT
   COUNT(CASE WHEN salary < 30000 THEN 1 END) AS "# Low",
   COUNT(CASE WHEN (salary >= 30000 AND salary < 70000) THEN 1 END) AS "# Medium",
   COUNT(CASE WHEN salary >= 70000 THEN 1 END) AS "# High",
FROM employees;

6. CAST

CAST operator is used to convert one data type to another. Following is the syntax of CAST: CAST ( expression AS target_type );

The following query will convert the date string to date object:

SELECT 
    CAST ('2020-09-25' AS DATE),
    CAST ('25-SEPT-2020' AS DATE);

7. SUBSTRING

SUBSTRING function returns part of a string. The syntax is: SUBSTRING(string, start_postition, length).

SELECT SUBSTRING ('PostgreSQL', 1, 8); -- PostgreS 
SELECT SUBSTRING ('PostgreSQL', 8); -- SQL

8. String Concatenation Functions

String concatenation functions of Postgres can turn to be of great help.

• Concatenation Operator ||

Suppose you want to get the full names of the employees by combining the first name and last names:

SELECT 
    (first_name || ' ' || last_name) AS "Full Name" 
FROM employees;

If any of the value is NULL, it will return NULL.

• CONCAT

Postgres also have a CONCAT function that can be used to combine two or more strings. The syntax is CONCAT(string_1, string_2, ...)

The above query can be re-written as:

SELECT 
    CONCAT(first_name, ' ', last_name) AS "Full Name" 
FROM employees;

Unlike the concatenation operator ||, the CONCAT function ignores NULL arguments. So if the last_name is NULL, it will return first_name as full_name.

• CONCAT_WS

CONCAT_WS (Concat With Separator)concatenates strings into one separated by a particular separator. The syntax is: CONCAT_WS(separator, string_1, string_2, ...). It also ignores the NULL values.

The following query will concatenate the first_name and last_name separated by a pipe (|):

SELECT 
    CONCAT_WS('|', first_name, last_name) AS "Full Name" 
FROM employees;

These were some useful PostgreSQL functions, operators, and expressions. Though I have covered some of them which I ended up using the most, there are many such expressions in Postgres. Let me know which is your favorite function in the response section down here.

Feel free to connect me on LinkedIn or Twitter.

Please show your support by hitting the clap button 👏 if you find the article useful and interesting.