My PostgreSQL Notes

Hello everyone...👋
Today I am just sharing what I learned from my PostgreSQL course, it may help beginners to understand postgresql database.
i will try to keep it short and limited to postgresql only so sql and rdbms is not going to be covered.

What is PostgreSQL ?

PostgreSQL is a powerful and versatile open-source database system that acts like 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 biggest IT organizations like:

• Apple
• Instagram
• Reddit
• Spotify
• NASA and many more like this.

Installation of PostgreSQL

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

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

Connecting to server

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

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

now we are connnected to server and we will try some things like we would do in sql.

Creating database in postgres SQL shell
First job now is to create our own database mybooks in postgres using our normal sql command create database.
and \l for list of databases.

as you can see we have all these by default databases + our mybooks database.
Now we are currently in 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.

Graphical interface of postgres PgAdmin 4

Postgres also has graphical interface for managing users, databases, passwords, sessions, and tables, etc.
for first timer(like me) its 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 which 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.

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

Importing files

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

Just make sure the column names in csv file and in table are written in same format, we can also import particular columns from csv to database if we dont 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 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 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 setup.

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 🙏