DEV Community

Pawan Kukreja
Pawan Kukreja

Posted on

Advanced Features of PostgreSQL (Part 01)

Introduction:

We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data. We will discuss some PostgreSQL extensions aswell.
Some examples from this chapter can also be found in advanced.sql in the tutorial directory. This file also contains some sample data to load.

Views:

Suppose the combined listing of weather records and city location
is of particular interest to your application, but you do not want to type the query each time you need
it. You can create a view over the query, which gives a name to the query that you can refer to like an
ordinary table.

Views allow you to encapsulate the details of the structure of your tables, Making liberal use of views is a key aspect of good SQL database design.
Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.

Foreign Keys:

You want to make sure that no one can insert rows in the weather table that do not have a matching entry in
the cities table. This is called maintaining the referential integrity of your data. In simplistic database
systems this would be implemented (if at all) by first looking at the cities table to check if a matching
record exists, and then inserting or rejecting the new weather records.

Transactions:

It is a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps
are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction
from completing, then none of the steps affect the database at all.
When the transaction is done and acknowledged by the database system, it has been permanently recorded and not being lost if ever crash in system. Another important property of transactional databases is closely related to the notion of atomic updates. when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others.
In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands.
If partway through the transaction, we decide we do not want to commit we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay