Quick start with Postgres ⚡

---

0. Introduction

RDMS are with us for a long time & provides an organised way to store and access information. In the realm of the relational database (SQL) , PostgreSQL and MySQL have many similarities.
I am not going to bore you with the specific differences between these two, but if you are curious to read about this, I am mentioning 2 articles below here, that clarifies both the pro and con of Postgres.
1. Dzone’s article
2. Uber’s article

1. Installation

I will discuss mostly installation in Linux, but I will also mention links for windows and mac installation.

Installation steps: 1. update the current system:
sudo apt update
2. Install the PostgreSQL & contrib package for additions tools.
sudo apt install postgresql postgres-contrib

great !! installation is done, and now you have Postgres in your system.

2. Create

PostgresSQL comes with by default user and DB named Postgres. so let’s create a user of your choice first.
sudo -u postgres createuser --login --pwprompt test_user
It will show a prompt for password like below:
Enter the password for new role: Enter it again.

Let’s create a database now:
sudo -u postgres createdb --owner=test_user test_db

now, this new “test_db” belongs to the “test_user” and it will be only accessed by test_user.
Now let’s restart the Postgres service:
sudo service postgresql restart

If you’re not currently using the user profile in which you want to use postgres, then either switch to that user profile example: “postgres” or “test_user” or use psql as that user without switching.

option 1: switching to a different user profile: sudo -i -u postgres
then
psql

option 2: or just use that user to open psql prompt:
sudo -u postgres psql

Great going !! now let’s create our first DB.
sudo create -u postgres createdb test_db
or if you want to create DB for another user, you can also do it.
sudo -u postgres createdb --owner=test_user test_db2
So far we have created a “user” & a “database” in postgreSQL.

Now understand how to create a table.
CREATE TABLE table_name ( column_name1 type (length of field) constraints, column_name2 type (length of field), column_name3 type (length of field) );

how to read the above code? here :
CREATE TABLE table_name ();

The above is the standard way to initialize a new table. inside that we have columns.

1. column_name : name of the column (ex: name, date, username, password, etc.)
2. type: type of column (ex: Interger, varchar,boolean, etc.) with field length (ex: varchar(250) ).
3. constraints: conditions for the column (ex: NOT NULL, check (col_name in (“4 wheeler”, “3 wheeler”, “16 wheeler”)), UNIQUE, etc.)

Let’s create a table now:
CREATE TABLE flights ( flight_id serial PRIMARY KEY, type varchar (50) NOT NULL, size varchar (25) NOT NULL, location varchar(25) check (location in ('delhi', 'jaipur', 'kolkata', 'gujarat', 'chennai', 'bengalore', 'chandhigarh', 'meghalaya')), flight_date date, flight_time timestamp );
The above command will create a table which you can check by using “\d”.

2. Insert

Now we have created the user, database & table in postgreSQL, let’s insert an entry.
INSERT INTO flights(type, size, location, flight_date, flight_time) VALUES('one-way', 'charter', 'delhi', '01-02-2022', '2015-08-07 05:00:01');
to check if the entered data is saved or not write :
SELECT * FROM flights;
And it will show the “flights” table with data in your terminal.


Awesome 🎊!! we have just learnt the basics of Postgres and also created & Inserted data in our Postgres database. to learn further about more Postgres topics in deep go to https://www.postgresqltutorial.com/.

Thanks for reading this, If you found this article useful read more at thevenice, and Let me know if find any improvisation in this article, I am Prakash Pawar and you can follow me on Twitter & Instagram. Thank you.

Tags:

#SQL, #Postgres, #DataBase, #PSQL