Common Functions in PostgreSQL

Welcome to Common PostgreSQL Functions! This is an interactive course for intermediate SQL users who know the basics of SQL. We assume that you can write a SELECT query and know how to use GROUP BY, HAVING, and ORDER BY clauses. Cool. But why stop learning now? You have so many possibilities!

If you're a complete beginner, don't worry. Just start with the SQL Basics in PostgreSQL course and come back here later.

After learning the basics of SQL, the next step to proficiency in SQL is to learn built-in functions that come with your database. A function is a predefined piece of code that performs a specific task. You likely know aggregate functions like COUNT(), SUM(), or AVG() that compute simple statistics.

Database engines have many more built-in functions. Functions extend the functionality of basic SQL and allow you to perform frequently-used operations on data. This enables you to work more effectively, write more sophisticated SQL queries, and take full advantage of the opportunities offered by databases.

In this course, you will learn about functions in PostgreSQL (version 9 and up). PostgreSQL, also known as Postgres, is one of the most popular free and open-source database management systems.

As indicated by popularity rankings, PostgreSQL is still gaining more fans. Last year it gained more users than one of the market leaders—Microsoft SQL Server. Interest in Postgres is also reflected in the growing number of job offers for developers and analysts.

You will learn about:

• Text functions that work with text data. You will learn the following functions:
  • CONCAT() allows you to merge multiple texts into one.
  • LENGTH() finds the length of a text.
  • LOWER(), UPPER(), and INITCAP() put text into lowercase, UPPERCASE, or Init Caps.
  • TRIM(), LTRIM(), and RTRIM() remove unnecessary characters from the text.
  • SUBSTRING() finds parts of a text.
  • REVERSE() reverses the text.
  • And many other functions.
• Numeric functions that work with numeric data.
  • You will learn about arithmetic in databases, that computations in computers are not always as precise as on paper, and how to avoid common mistakes when doing computations in a database.
  • ROUND(), TRUNC(), and CEIL() are functions that round numbers to a specific number of digits.
  • ABS() computes the absolute value of a number.
• Date and time functions. You will learn the following:
  • How you can store date and time data in PostgreSQL, how to find the current date and time, and how to convert date and time between time zones.
  • How interval arithmetic works in PostgreSQL.
  • DATE_TRUNC() helps you group data by year, month, week, quarter, and other periods of time.
  • DATE_PART() extracts part of the date (year, month, day, hour, minute, etc.) from a date.
  • How to display date and time in a given format.
• And other interesting functions:
  • COALESCE() and NULLIF() make working with NULLs easier.
  • GREATEST() and LEAST() find the maximum or minimum value within a number of expressions in one row.

The course is interactive. You learn the functions through practice by writing real SQL queries. You solve the exercises directly in your web browser. Our platform runs your query and verifies your solution. So, you don't need to install any software on your computer.

Note: This course does not teach you how to write your own functions (user-defined functions) in PostgreSQL. If you want to create your own functions, we recommend our Writing User-Defined Functions in PostgreSQL course.

What Do You Need to Take This Course?

• A web browser and an internet connection.
• Basic understanding of SQL, such as SELECT, WHERE, JOIN, GROUP BY, and ORDER BY.
• If you're a beginner, we recommend our SQL Basics in PostgreSQL course.

This Course Will Teach You How To:

• Get to know the most common PostgreSQL text functions.
• Learn about arithmetic in databases and how to avoid common arithmetic errors.
• Use PostgreSQL numeric functions.
• Learn PostgreSQL date and time functions.
• Group data by year, month, quarter, or week in PostgreSQL.
• Replace NULL with meaningful values in PostgreSQL.

Plus, you'll get to practice your skills on over 143 interactive exercises.

Who Should Take This Course?

• Anyone who wants to learn about common PostgreSQL functions.
• Beginning database analysts.
• Beginning developers.
• Students taking classes in relational databases.
• Business database users who want to improve their skills.