DEV Community

Cover image for My PostgreSQL Notes
Vinay Patil
Vinay Patil

Posted on

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).

sql shell in windows

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

postgres sql shell interface

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.

list of databases in postgres using \l

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.

create table insert rows in table in postgres

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 pgAdmin interface

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

postgresql interface

✨ "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 interface

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) .

postgresql interface

✨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.

postgresql interface

Importing files

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

postgresql interface

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.

postgresql interface

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.

postgresql interface

✨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.

postgresql interface

postgresql interface

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

postgresql interface

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.

postgresql interface

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.

postgresql interface

✨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 🙏

Top comments (0)