Friday, August 12, 2022
HomeData Science2 Should-Know PostgreSQL Features. Defined with examples | by Soner Yıldırım |...

2 Should-Know PostgreSQL Features. Defined with examples | by Soner Yıldırım | Aug, 2022


Defined with examples

(Picture by Brian Breeden on Unsplash)

Contemplating the large quantity of structured knowledge saved in relational databases, knowledge scientist and analysts are more likely to work together with a relational database virtually each day. SQL is what we use to make these interactions, which make it one of the crucial in-demand expertise in knowledge science.

There are numerous completely different relational database administration programs (RDBMS) that use SQL to work together with and handle knowledge in relational databases. One of many in style RDBMSs is PostgreSQL, which is open-source and has a robust popularity for reliability, function robustness, and efficiency.

On this article, we are going to study 2 PostgreSQL features, which simplify complicated operations and turn out to be useful in lots of circumstances. I name them as life-saver features.

This operate can be utilized for creating ordered tables with numbers and dates. A typical use case with dates is when you’ve got a beginning and ending dates for an occasion and must develop it as a calendar.

Let’s go over an instance. We’ve a reserving desk that accommodates the bookings at a resort. It consists of the reserving id, check-in and check-out dates. We need to develop it in order that will probably be simpler to calculate the occupancy.

SELECT
id,
generate_series(
checkIn,
checkOut,
INTERVAL '1 day'
) AS booked
FROM reserving

The question above will embody the try date which is definitely not thought to be booked. Thus, with a view to mark booked days precisely, we have to subtract someday from the try date earlier than producing the sequence.

Right here is how this may be achieved:

SELECT
id,
generate_series(
checkIn,
(checkOut - INTERVAL '1 day'),
INTERVAL '1 day'
) AS booked
FROM reserving

What this code does is illustrated beneath:

(picture by writer)

We will be part of the desk on the left to a calendar desk and that’s it! We’ve a calendar with booked days marked.

The to_char operate can be utilized for changing timestamps, intervals, or a numeric values to formatted strings. I discover it probably the most helpful when extracting a chunk of data from timestamps or dates.

Its syntax is as follows:

TO_CHAR(expression, format)

We will use it to extract month identify and month abbreviation utilizing the ‘MONTH’ and ‘MON’ codecs, respectively. Think about now we have a reserving desk which has a date column referred to as booked. We will use the to_char operate to extract these items as follows:

choose
booked,
to_char(booked, 'MONTH') AS month,
to_char(booked, 'MON') AS mon,
from reserving
(picture by writer)

We will additionally get the 12 months month data as a string:

choose
booked,
to_char(booked, 'YYYY-MM') AS yearmonth
from reserving

If the date is 2022–10–08, then 12 months month worth will probably be 2022–10.

There are additionally helpful codecs associated to days. As an illustration, we will extract the day identify and quantity.

choose
booked,
to_char(booked, 'DY') AS day_name,
to_char(booked, 'DD') AS day,
from reserving
(picture by writer)

There are different codecs that may be helpful for various duties. You may test the official documentation for different codecs supported by the to_char operate.

We’ve coated two necessary features of PostgreSQL. They’re primarily utilized in knowledge preprocessing, or making the uncooked knowledge higher match the duties you’ll do afterwards. These features really present that SQL is not only a question language however a extremely environment friendly knowledge evaluation and manipulation instrument as nicely.

You may turn into a Medium member to unlock full entry to my writing, plus the remainder of Medium. When you already are, don’t neglect to subscribe when you’d wish to get an electronic mail at any time when I publish a brand new article.

Thanks for studying. Please let me know in case you have any suggestions.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments