SQL Tutorial: Functions for Manipulating Data in PostgreSQL

Key Takeaways

welcome to Postgres sequel functions for manipulating data my name is Brian piccolo and I am a senior director of digital strategy I will be your instructor for this course you've learned some sequel fundamentals in your previous coursework now we are going to continue to expand your sequel knowledge by teaching you how to use built-in functions and operators to extend the capabilities of your Postgres sequel database you'll be using the secure database which is a well-known example database that models a fictional DVD rental store the database is highly normalized which allows for great example queries and provides a great representation of Postgres sequel data types and custom functions before we get started let's review the topics that will be covered in this course first in this chapter you'll learn about some common data types and their properties and characteristics next you'll dive into some built-in functions and operators and learn how to use them to manipulate date and time data you'll then explore some frequently used functions for parsing and manipulating text data types and finally you'll get an introduction into full text search using Postgres sequel extensions let's get started Postgres sequel has a robust set of native datatypes some of the most common include text data types like char var char and txt numeric data types like int and decimal date and time data types such as date time stamp and interval and finally arrays important to have an understanding of the properties and characteristics of the various data types anytime you are working with a relational database like Postgres sequel text data types like char and varchar' allow for a fixed or varying number of characters and string data think about categorical data like the title column from the film table text data types typically represent character and string data but can be an unlimited length for example the text from the description column in the film table will explore how to extract create sub strings and manipulate text datatypes later on in this course numeric data types like int and decimal allow you to store integers like payment ID and floating-point numbers with varying Precision's like amount from the payment it will learn more about Postgres sequel date and time and array datatypes later in this chapter when you are working with existing databases there will be times when you will need to determine the data types of columns that you'll be working with for example if we look at the results from a simple select query on the film table you might hypothesize that all the columns in this query have a text data type but would you be right let's take a closer look Postgres sequel stores information about all database objects in a system database called information schema by querying certain tables in this database you can determine information about the database including data types of columns the following query will return the column name and the data type for the columns we looked at in the previous slide when you execute this query you will get a result that looks like the following if we look closely at these results we see that the title and description columns are indeed text data types specifically varchar' and text but we see that special features is actually an array you'll learn more about arrays later in this chapter now it's your turn let's take a closer look at the customer table of our DVD rental