Hello everyone...👋 Today I am just sharing what I learned from my PostgreSQL course, which may help beginners to understand the Postgresql database. I will try to keep it short and limited to Postgresql only so SQL and RDBMS are not going to be covered.

What is PostgreSQL?

PostgreSQL is a powerful and versatile open-source database system that acts like a digital notebook for your data. It helps you organize and manage information efficiently using tables, rows, and columns. With PostgreSQL, you can add, search, and update data, while ensuring its security and integrity. and for your information, it is used by one of the biggest IT organizations like:

• Apple

• Instagram

• Reddit

• Spotify

• NASA and many more like this.

Installation of PostgreSQL

The easiest installation way to install Postgresql is from its own website https://postgresql.org. Download the installer from the website and do next-next, you already know the drill Remember the password that you entered while installing because you will need it while connecting to the database.

for me, it took too much time to unpack files maybe because my system is slow.

Connecting to the server

Just open SQL shell from Windows apps ( we will first see how it works on the command line interface and then move to the graphical interface).

leave other fields empty for now and enter the password you will see you are connected to the Postgres server and now can run commands.

now we are connected to the server and we will try some things as we would do in SQL.

Creating a database in Postgres SQL shell

The first job now is to create our own database 'mybooks' in Postgres using our normal SQL command create a database. and \l for a list of databases.

as you can see we have all these by default databases + our mybooks database. Now we are currently in the Postgres database(default) so we will have to shift from Postgres database to our mybooks data using

\c table_name

as you can see we just created a table and inserted some rows which is pretty similar to SQL.

The graphical interface of Postgres PgAdmin 4

Postgres also has a graphical interface for managing users, databases, passwords, sessions, tables, etc. for first timers (like me) it's quite confusing to look at but can be easily learned.

Postgres's PGadmin has one of the easiest ways to use databases we can create databases, and tables from options, edit values, and all.

✨ "i" or "?" for more information on operations.

Here I created a sample table with 3 columns, there are many features that we will try next.

PostgreSQL Schema

In PostgreSQL, a schema is like a special container or folder that helps keep things organized in a database. It's like having different sections or compartments for tables, views, functions, and other important stuff. By using schemas, we can group these things together and make sure they don't get mixed up or have the same names. It's just like having separate folders for different types of toys or books, so you can find and manage them easily. For e.g a company TVS has different units (marketing, HR).

✨Public is the default schema in every database and is accessible to all schemas

connecting tables

We connected two tables using the foreign key and primary key for products and their categories. you can learn more about it in basic sql here this things are too easy and with so many options to choose from.

if validation is being used the category_id in the product table will only take the category_id available in the category table.

Importing files

many types of files can be imported and exported in the pgAdmin as we imported CSV files into the database.

Just make sure the column names in CSV file and in the table are written in the same format, we can also import particular columns from CSV to the database if we don't need every column in that csv.

View in PgAdmin

In PostgreSQL, a view can be thought of as a special kind of table which is a copy of certain fields of a table. It's like creating a virtual table using the data already in the database. By saving a particular query as a named object, I can use it just like a regular table to easily access information without needing to rewrite the query every time.

here I created a view for my two tables with join.

✨please remember to use 'AS' for similar column names.

Noticed some interesting features

Noticed some interesting features in PgAdmin while learning 👇 ⭐️You can generate ER diagram for any table with a bunch of variations.

⭐️You can download your data in CSV and also can use a graph visualizer on table data.

Indexes in SQL

Table indexes are similar to book indexes which will point to the content in the table that helps improve the speed and efficiency of database searches by creating optimized pathways to locate data.

Roles

We can also create new roles in pgAdmin where we can manage which data is accessible to people in the organization.

Grant privileges and much more complex stuff, that I don't understand yet.

✨There is also a password and connection limit for the user role we can set up.

There is a feature for backup and restore from backup. we can mention if we have to backup everything or just data or user info and all that with tons of options.

This blog is created from my Twitter thread, so if you are on Twitter send me a 'hi👋' or something on meEngineervinay.

Thank you so much 🙏