PostgreSQL Functions You Need to Know

1. DOW (Day of Week)

The DOW function is used to extract the day of the week from a date. It's typically available in PostgreSQL and similar systems. Days are represented as integers (0 for Sunday through 6 for Saturday)

Example:

SELECT EXTRACT(DOW FROM date_column) AS day_of_week

FROM table_name;

This returns the day of the week for the date_column.

2. EXTRACT

The EXTRACT function retrieves subparts of a date or timestamp. It's versatile and can extract values like year, month, day, hour, minute, etc.

Example:

SELECT EXTRACT(YEAR FROM date_column) AS year,

EXTRACT(MONTH FROM date_column) AS month,

EXTRACT(DAY FROM date_column) AS day

FROM table_name;

This returns the year, month, and day of the date_column.

3. COALESCE

The COALESCE function can be used to return 0 for NULL values in a column by specifying 0 as the fallback value.

Example:

SELECT COALESCE(column_name, 0) AS result

FROM table_name;

• If column_name contains a NULL value, the function returns 0.

• If column_name has a non-NULL value, it returns that value

  
  
  
  

4. GREATEST

The GREATEST function in PostgreSQL can be used to find the largest value across multiple columns of a table.

Syntax:

SELECT GREATEST(column1, column2, column3, ...) AS greatest_value

FROM table_name;

Sample Table: scores

id	score_math	score_science	score_english
1	85	90	88
2	75	80	70
3	95	89	92

Query:

SELECT id, GREATEST(score_math, score_science, score_english) AS Highest_score

FROM scores;

Result:

id	Highest_score
1	90
2	80
3	95

5. LEAST

The LEAST function in PostgreSQL can be used to find the least value across multiple columns of a table.

Syntax:

SELECT LEAST(column1, column2, column3, ...) AS least_value

FROM table_name;

Sample Table: scores

id	score_math	score_science	score_english
1	85	90	88
2	75	80	70
3	95	89	92

Query:

SELECT id, LEAST(score_math, score_science, score_english) AS lowest_score FROM scores;

Result:

id	lowest_score
1	85
2	70
3	89

6. ROUND

The ROUND function rounds a numeric value to the specified number of decimal places.

Example:

SELECT ROUND(column_name, 2) AS rounded_value

FROM table_name;

This returns the column with decimals rounded to two places.

7.DISTINCT ON

The DISTINCT ON clause allows you to select distinct rows based on a subset of columns.

Example:

SELECT DISTINCT ON (department) department, salary

FROM employees

ORDER BY department, salary DESC;

This returns the highest salary for each department.

8.TO_CHAR

The TO_CHAR function is used to convert a timestamp or number into a formatted string.

SELECT TO_CHAR(date_column, 'YYYY-MM-DD') AS formatted_date,

TO_CHAR(decimal_number, '999,999.99') AS formatted_number

FROM table_name;

• formatted_date: Converts the date_column into YYYY-MM-DD format.

• formatted_number: Converts the number into a comma-separated format with two decimal places.

  
  

9.LEAD and LAG

The LEAD and LAG window functions access subsequent or previous rows within a result set.

Example:

SELECT name, salary,

LAG(salary) OVER (ORDER BY salary) AS previous_salary,

LEAD(salary) OVER (ORDER BY salary) AS next_salary

FROM employees;

This shows the previous and next salaries relative to the current row.

10. DATE_TRUNC

The DATE_TRUNC function in PostgreSQL is used to truncate a date or timestamp to a specific precision, such as year, month, week, day, etc. It’s helpful when you want to group or aggregate data by a particular time unit

Example:

SELECT DATE_TRUNC('month', order_date) AS month,

COUNT(*) AS total_orders

FROM orders

GROUP BY DATE_TRUNC('month', order_date)

ORDER BY month;

This query aggregates the number of orders per month.

Supported Precision Values are

1. year

2. quarter

3. month

4. week

5. day

6. hour

7. minute

8. second

PostgreSQL offers a rich library of functions that empower developers and database administrators to perform complex tasks with ease. From data manipulation and aggregation to handling null values and categorizing data, functions like COALESCE, GREATEST, LEAST, DATE_TRUNC and many others serve as invaluable tools in your SQL toolkit.

We've explored some of the most commonly used and powerful functions in this blog. Keep exploring, experimenting, and mastering new functions to unlock the full potential of PostgreSQL. Happy querying!