0. Introduction
RDMS are with us for the long time & provides as an organized way to store and access information. In relational
databases realm, PostgreSQL and MySQL has 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 pro and con about postgres.
1. Dzone's article
2. Uber's article
1. Installation
I will discuss moslty about 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 !! installtation is done, now you have postgres in your system.
2. Create
PostgresSQL comes with by default user and db named as 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 password for new role:
Enter it again:
Lets 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
Now if you're not currently using user profile in which you want to use postgres, then either switch to the that
user profile example: "postgres" or "test_user" or use psql as that user without switching.
option 1: switching to 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 lets 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 above code ? here :
CREATE TABLE table_name (); 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
);
Above command will create a table which you can check by using "\d".
2. Insert
Now we have created user, database & table in postgreSQL, let's insert a 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 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, Let me know if find any improvisation in this article, I am Prakash Pawar and you can follow me on twitter & Instagram . Thank you.
Top comments (1)
the great things about postgresql is a rich ecosystem. a lot of interesting OSS born like timescaledb, etc. a lot of product also support postgresql wire protocol.
i think this blog post can show the benefit of postgresql as the platform and ecosystem. it can introduce timescaledb for timeseries, redshift for analytics etc.
and it's basically SQL ! great one. keep posting