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
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.
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
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')),
Above command will create a table which you can check by using "\d".
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