Let’s start with a simple but super important truth: data is often “raw” by itself. A messy stream of numbers, dates, or strings is tough to read. Imagine a report where dates are all over the place: some are written as 01/02/2023, others as 2023-02-01. That can trip up even the most careful analysts. That’s where the magic of data formatting comes in.
Data formatting lets you:
- Turn data into a user-friendly format, making it way easier to work with.
- Become a data lifehacker by quickly pulling out the info you need.
- Prep data for export or integration with other systems.
- Make reports and visualizations readable and accurate.
Picture this: you work at a university and need to make a report listing all students with their full name, date of birth, and faculty. Instead of gluing the data together by hand, you use a query with formatting that combines first and last name in one column, and shows the birth date in a clear way, like March 12, 2001.
Main Data Formatting Tasks
You can think of formatting like making a fruit salad. You don’t just toss a bunch of fruit in a bowl—you slice and arrange everything nicely on a plate. In SQL, this job breaks down into a few types of tasks:
String formatting
This can mean joining strings, changing case (making everything uppercase or lowercase), adding or removing spaces and characters.
Number formatting
Sometimes it’s handy to turn numbers into strings or round them to a certain number of decimal places.
Working with dates and time
Here you can automate stuff like getting the current date, formatting time data, or pulling out certain parts of a date (like just the year or just the month).
Pulling unique values
If your report needs to show only unique records (like unique cities or faculties), that’s part of data formatting too.
Formatting Tools in PostgreSQL
PostgreSQL gives you a big set of built-in functions for working with data. Here’s just a short list of what we’ll dig into in the next lectures:
String formatting
CONCAT(): joining strings together.UPPER(),LOWER(): changing text case.
Date and time formatting
NOW(): current date and time.CURRENT_DATE: current date (no time).DATE_PART(): pull out parts of a date, like year or month.
Type conversion
CAST(): change data type.- Syntax:
<value>::<type>.
Sorting data
ORDER BY: sort data by one or more columns.DISTINCT: pull out unique values.
Data Formatting Example: What Does It Look Like in Practice?
To get you interested, let’s check out a couple of examples. We’ll start simple.
Example 1: String formatting
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;
This query joins first_name and last_name into one column called full_name. For example, if a student’s name is "Otto" and last name is "Art", the result will be: "Otto Art".
Example 2: Date formatting
SELECT DATE_PART('year', date_of_birth) AS birth_year
FROM students;
Here we’re pulling just the birth year from the date_of_birth column. If the date of birth in the database is 2001-03-15, the result will be 2001.
Example 3: Type conversion
SELECT birth_year::text || ' year' AS formatted_year
FROM (
SELECT DATE_PART('year', date_of_birth) AS birth_year
FROM students
) subquery;
In this query, we first pull out the birth year, then turn it into a string and add the text " year". For example, the result will be: 2001 year.
Where Is This Used?
Reports for managers
Not everyone wants to see “raw” data. For example, in a company turnover report, it’s better to show amounts with thousands separators (1,000,000 instead of 1000000), and dates in a format like 21 FEB 2025.
Reports for clients
For example, an email to a client saying: "Dear [FirstName LastName], your order N123 was successfully delivered on October 12, 2023."
Integration with other systems
Some input forms need data in a certain format: dates might need to be ISO, strings with the right case, numbers rounded.
Analytics and visualization
Imagine you’re building charts or diagrams. Without formatting, your charts can look really off.
GO TO FULL VERSION