PostgreSQL Tutorial for beginners to Advance level.

Updated: Apr 26

#sql, #beginners, #postgres, #database, #AdvanceQuery

Hi All, Today I gonna try cloning dev.to DB at very small level. In this article, we will learn how to make relationship between tables and how to query within tables to get desired results.


I hope you must have a setup of PostgreSQl in your system.

At first we will create 'users' table in which we can save our user data.

create table users (
    id serial primary key not null,
    first_name varchar(255) not null,
    last_name text not null,
    age int,
    email text unique not null
)

/*
1) create table 'tableName' is a syntax for creating tables.

2) 'id' will be our unique key. Its like unique identification number of 
   user. It will advance in positive direction and cannot be null. To 
   fullfill this requirement we need below keywords together.
   'serial' will move from 1 to n position.
   'primary key' will sure that 'id' field will be unique always for this 
    table.
   'not null' also assures that this field cannot be null.

3) 'first_name' will be the first name of our users. It will have string 
    to save and it cannot be null.To fullfill we need below keywords.
    'varchar(255) to save string.
    'not null' to assure that this field will not be null.

NOTE : we are using 'snake_case' for first_name instead of 'camelCase'. To save 
       fieldname in 'camelCase' we need to give field name in double 
        quotes like
       "firstName", if we dont give name in double quotes then it became 
       'firstname'.
4) 'last_name' is similar to 'first_name' for saving last_name of the user.
   We use 'text' for saving strings which is similar to varchar without limit.

5) 'age' will be a integer value to save users age. 'int' keyword will be use for this.

6) 'email' will be a unique field for logging and communicating to user. 
   It cannot be null also.
   'text' for saving email in string format.
   'unique' to save unique emails only.
*/


Our 'users' table is created now but it is empty as of now, lets create some data in it.
insert into users (first_name,last_name,age,email) 
values ('Earnestine','Fahey',45,'Bernita17@gmail.com');

/*
   'insert into tableName(fields) values(data)' to insert data.

NOTE : We need not to specify 'id' field here because we have provided 'serial' property to it so that it will initialize and increase it automatically.

//Lets insert some more rows.
insert into users (first_name,last_name,age,email) values ('Michaela','Grant',24,'Abbie_Hintz@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Jessie','Murphy',9,'Ibrahim58@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',8,'Kayleigh_Jaskolski@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Eddie','Homenick',60,'Lea.Schuster@gmail.com');
insert into users (first_name,last_name,age,email) values ('Osbaldo','Mueller',23,'Jayden_Brekke@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Garth','Murray',50,'Mikel85@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Corene','Kilback',71,'Shyanne.Kling44@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Alexane','Fritsch',28,'Karli.Zemlak39@gmail.com');
insert into users (first_name,last_name,age,email) values ('Luz','Pouros',75,'Giovanny.Parisian@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Lavinia','Breitenberg',79,'Don55@gmail.com');
insert into users (first_name,last_name,age,email) values ('Euna','Schuppe',33,'Odessa95@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Della','Shields',38,'Brielle_Rippin58@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Loyce','Powlowski',68,'Ernest56@gmail.com');
insert into users (first_name,last_name,age,email) values ('Cecelia','Morissette',50,'Glennie_Moore39@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Jody','Swift',40,'Caden56@gmail.com');
insert into users (first_name,last_name,age,email) values ('Malachi','Marks',40,'Terence.Crona88@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Claud','Hegmann',29,'Madelynn_Lueilwitz65@gmail.com');

*/

In order to see our data, let hit the below query :

  select * from users;
  'select' means to select the fields from table.

NOTE: Here * can be replaced with fieldName such as first_name,last_name. For ease and visual reprenstation of all fields, I have used *.


Database Table Example

If we see the values in table, some users are might less than 12 years of age, which we dont want to register. We might want to set age limit greater than 15 years. For this, we first need to delete all users who have age less than 15 years and set constraint on age to accept age only 18+.

//delete users whose age is less than 18
  delete  from users where age < 18;

//Set constraints on age to accept only user who are 18+
  alter table users ADD CHECK (age >= 18);

//After this if we try to add user who has age less than 18 will not be allowed to register.E.g
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',8,'Kayleigh_Jaskolski@yahoo.com');

//However if user grown up and try to register then it will be able to register.
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',18,'Kayleigh_Jaskolski@yahoo.com');

However, we can add or drop column even after table creation.

alter table users drop column age;
alter table users add column age int default 18;

WHERE keyword

With 'where' we can set condition in our select query to return only those values which satisfied the condition given.For example :

select * from users where id = 43;
select * from users where id=45 or id=2;
select * from users where id=45 and first_name = 'Eddie';
select * from users where id in (42,43,44); //Array
select * from users where age > 38; 

UPDATE ROW

To update any user data, we can use update as follows:

update users set age = 20 where id = 1;

update users set age = age + 1, last_name =  last_name || ' tom' where id = 1;

NOTE: To concatenate String in postgresql we can use '||'.

Now we are done with 'users' basic details, we can add if we want more but this tutorial I am advancing further with this only.We now need a 'posts' table where user's post detail can be saved in DB. Lets start with it.

Foreign Key Concept

Before moving further, we must keep in mind one thing in order to create 'posts' table that it must be linked with 'users', so that we know which user has posted that particular post. To achieve this we are going to use references which are also called foreign key concept to link both the tables.Let look how we can achieve this.

Its also called "one to many relationship"

create table posts(
        id serial primary key,
        title text not null,
        body text default '...', //default will set '...' by default if 
                                         //no value is given.
        "creatorId" int references users(id) not null
//with references we are attaching users's table primary key 'id' to 'creatorId' 
//so both are tied together and our application can identify which user has created the post.
        )

NOTE: we are using double quotes for showing the camelCase in postgresql.

Insert with references

insert into posts (title,body,"creatorId")
 values (
'global extend supply-chains', //title
'Voluptas natus iste hic nihil ut. Ipsam delectus sed ut et blanditiis non et. Consequuntur cumque beatae quae sint voluptas nulla voluptate delectus quo. Sit libero neque est distinctio enim neque et consequuntur aut. Odio fuga voluptatem non minus.

Vero nisi non saepe occaecati illo. Delectus neque ut sint eius rerum. Expedita quo voluptas porro aut libero itaque.

Aliquid nulla similique aperiam atque assumenda ea sint cum. Est qui repellendus quia aliquid accusantium. Ea dolores perferendis libero ipsam qui. Autem itaque excepturi adipisci asperiores eaque eaque doloribus consequatur dolorum.',//body
48); // ###references ID which is userID


//Other Posts with references.

insert into posts (title,body,"creatorId") values ('efficient implement platforms','Quam aliquid odit inventore est ipsa. Nulla odio accusantium repellendus in inventore repellat laudantium ea atque. Quas aliquid et et totam. Dolor corporis sequi sequi dolorem hic. Voluptas veniam quia.

Eveniet non vitae quia sed. Alias sunt ut consequatur aut quas eius eum facilis. Molestiae quaerat optio quia aut quia nobis.

Consequatur ut iure sunt quam eum deleniti et. Minima non perspiciatis dolore nesciunt ut sit. Aliquid dignissimos tempora enim.',56);
insert into posts (title,body,"creatorId") values ('world-class reinvent web-readiness','Sit magnam vero qui. Eos quibusdam accusamus exercitationem qui et. Ratione rerum ut consequatur quibusdam id omnis quia consequatur. Quae et rem veniam beatae aut ipsum quas laudantium ex.

Sit ullam eos. Aut quae ut ipsam doloremque dolor et praesentium omnis. Et ea et beatae repudiandae neque et modi earum sed.

Impedit nesciunt dolorem maiores ut iste. Quod aliquam asperiores reprehenderit ut ad aperiam et architecto est. Eaque blanditiis quasi ut enim blanditiis qui.',53);
insert into posts (title,body,"creatorId") values ('seamless repurpose e-commerce','Est et modi sed consequatur beatae aut ad. Excepturi voluptate amet odit occaecati minus enim. Maiores non consectetur ipsam.

Delectus quod aperiam velit cum quibusdam aut totam sit impedit. Aut quis veniam enim maiores ut. Quos tempora ducimus delectus qui. Quas minima ipsum.

Necessitatibus quis labore occaecati consequatur alias autem et. Sed et nulla et vitae quidem itaque odio hic nesciunt. Ut qui itaque pariatur beatae voluptatem laborum.',56);
insert into posts (title,body,"creatorId") values ('frictionless incubate e-commerce','Deserunt facilis alias animi qui voluptatum. Amet dolor iusto fuga tempora. Et deleniti odit et. Est autem vel harum et quis laudantium. Quam quia at vel non eveniet dignissimos impedit. Dolor necessitatibus blanditiis maxime ea.

Aperiam ab aliquam ad impedit dolorem perspiciatis asperiores. Quia qui quo est eveniet eligendi maxime sequi sed reprehenderit. Officia perferendis qui animi et odit repellendus beatae. Enim qui modi omnis impedit.

Qui voluptas sequi aut assumenda. Nemo a expedita dolore. Eum quis sed ut eos. Aut nulla officia eos rerum sed optio tempore.',43);
insert into posts (title,body,"creatorId") values ('frictionless grow synergies','Esse et quos magni. Qui laborum fugiat itaque in ipsum. Ex officiis reprehenderit et quae ratione sit. Error illum nam beatae similique. Eligendi voluptatibus sint nesciunt aut iure.

Consectetur sequi ut. Possimus sit expedita omnis at. Et aut dignissimos facere non ducimus itaque. Non expedita quas sit odit. Quo aperiam eum assumenda. Consequuntur deserunt voluptate repudiandae autem officia harum totam enim minus.

Esse autem perferendis. Et illum non perspiciatis sit ex. Sed animi at optio totam nobis quas dignissimos dolorem ut.',48);
insert into posts (title,body,"creatorId") values ('leading-edge disintermediate e-commerce','Delectus et accusantium nostrum. Est et illo rerum voluptatum autem. Explicabo qui et pariatur ut. Explicabo non sit sint autem ut. Quaerat numquam rerum.

Ut odit tempora explicabo totam similique enim consequatur. Qui tempora deserunt iusto praesentium. Atque illum facilis et accusamus sint. Facilis deserunt ipsam culpa laboriosam tenetur libero. Aspernatur itaque doloribus. Assumenda qui voluptatem sequi.

Eveniet eum dolor corrupti facilis. Repellendus cum sapiente tempora illo eos. Sint omnis saepe quisquam et omnis tenetur. Voluptas quod voluptatem.',52);
insert into posts (title,body,"creatorId") values ('cutting-edge integrate methodologies','Adipisci quia veniam. Eos minus reiciendis. Cum aut nesciunt ipsa accusantium et minima aut quasi similique. Natus illo debitis saepe ut est. Sapiente nostrum sunt.

Consequuntur tenetur quidem reiciendis. Voluptatem corporis nulla asperiores ea ut quo. Sint consequatur aperiam voluptatem blanditiis earum reiciendis dolor id id. Et fugit et sed reprehenderit. Dolores mollitia pariatur molestiae et tempora ratione.

Deleniti unde sequi nesciunt. Officia itaque ratione harum sit quisquam ut illo ullam accusantium. Est illo et mollitia quo repellat.',51);

insert into posts (title,body,"creatorId") values ('seamless visualize methodologies','Est voluptatem dolorem unde omnis ipsam sunt ipsum. Ab optio esse. Quos sed sint officiis dicta sit dolorem eum. Porro itaque delectus cumque error. Voluptatem earum error nisi aut qui sunt eos.

Dolores id quia nesciunt deleniti. Consequatur sint sit provident omnis eveniet at. Sed odio suscipit minus non minima perspiciatis. Voluptate distinctio illum aliquid.

Voluptas esse omnis ea. Iusto adipisci animi id. Adipisci ut aliquam commodi qui consequatur nam minima.',45);
insert into posts (title,body,"creatorId") values ('compelling evolve synergies','Nesciunt quia quis voluptatum vel molestiae velit. Iste optio mollitia. Est a sapiente ut facere veniam quia illum molestias. Impedit fugit delectus. Voluptatem harum fugiat quis est. Minima sint qui.

Quia dicta voluptas adipisci odio beatae rem minus id. Explicabo sapiente nam quidem qui ut nisi. Autem laboriosam dicta consequatur illo sunt eos et. Tempore aut quia impedit quia neque voluptas qui sed commodi. Cumque placeat magni quia blanditiis molestiae. Occaecati aut quia perspiciatis omnis modi asperiores est possimus sit.

Sed enim consequatur non repellendus est. Laborum veniam eaque unde quae in dolore. Modi qui ut omnis exercitationem suscipit nisi. Suscipit eligendi maiores et consequatur voluptas neque sapiente. Voluptas aut aut architecto ut nisi et expedita. Cumque voluptate commodi.',47);
insert into posts (title,body,"creatorId") values ('end-to-end utilize solutions','Omnis ut vitae nihil perspiciatis sequi consequatur. Facere deleniti voluptatem et. At aut inventore porro rerum. Tempora aut est repudiandae rerum est non nam possimus iste.

Aut vel rerum repellat alias. Laudantium et reprehenderit neque vel repudiandae excepturi sed saepe rem. Quis iste reprehenderit dolores. Minus delectus nihil.

Perspiciatis porro et dignissimos soluta molestiae. Deleniti mollitia neque architecto recusandae dicta vero voluptas ullam voluptas. Asperiores magnam et enim nihil.',51);


Now as we have successfully created posts for some of our user. Now we want to see data which combines both posts and users data together. For this we will be using 'join'.

Join can be of many types: 1) Inner Join 2) Left Join 3) Right Join 4) Outer Join 5) cross join 6) natural join

Inner Join

With the help of Inner Join keyword we can get the from both the table on which join has been called. For example

select * from users u inner join posts p on u.id = p."creatorId";
//Inner Join defines the type of Join
//ON decides the condition.
//This command join left table with right table.
//In this query, we are matching users table id with posts table "creatorId" as //we have a foreign key relationship in between them.
// Here we takes users as 'u' , posts as 'p'.

select u.id, p.id,first_name,title from users u left join posts p on u.id = p."creatorId";

select u.id, p.id,first_name,title from users u inner join posts p on u.id = p."creatorId";

//Difference between left and inner join is, left join will show you values of //left table even condition not satisfy.

Check Post of particular user with all details.

select u.id, p.id post_id,first_name,title from users u inner join posts p on u.id = p."creatorId" where u.id = 45;

Check post from particular user with particular title.

select u.id, p.id post_id,first_name,title from users u inner join posts p on u.id = p."creatorId" where p.title like '%killer%';

NOTE : --- word% to find after the word.
       --- %word% to find in between.
       --- %word find before the word
       --- To get Case insensitive we can use 'ilike'


Before moving further lets create our new table 'comments' who can handle comments of post.

create table comments(
            id serial primary key,
            message text not null,
            post_id int references posts(id) not null,
            creator_id int references users(id) not null
        )

//Insert comments
insert into comments (message,post_id,creator_id) values ('Odit cupiditate aliquid aut quaerat tempora ut.',93,43);
insert into comments (message,post_id,creator_id) values ('Hic et et quas doloribus id repellat aut asperiores.',100,49);
insert into comments (message,post_id,creator_id) values ('Officiis magnam dolorum qui nihil et qui rerum consequatur accusantium.',94,50);
insert into comments (message,post_id,creator_id) values ('Adipisci blanditiis quis.',103,48);
insert into comments (message,post_id,creator_id) values ('Repellendus id natus.',94,55);
insert into comments (message,post_id,creator_id) values ('Quas corrupti mollitia dolorem explicabo.',97,50);
insert into comments (message,post_id,creator_id) values ('Commodi iusto est et.',103,51);
insert into comments (message,post_id,creator_id) values ('Vero maxime inventore delectus voluptatum sapiente doloremque distinctio alias.',106,52);
insert into comments (message,post_id,creator_id) values ('Ipsum maiores maiores sit distinctio nobis velit ut et.',106,46);
insert into comments (message,post_id,creator_id) values ('Quo illo sit quasi temporibus.',104,52);
insert into comments (message,post_id,creator_id) values ('Animi aperiam sed voluptas.',108,47);
insert into comments (message,post_id,creator_id) values ('Et quaerat quod quisquam quaerat consectetur fugiat accusantium.',95,44);
insert into comments (message,post_id,creator_id) values ('Amet eum sit consequatur accusantium sapiente temporibus a.',99,55);
insert into comments (message,post_id,creator_id) values ('Et aut fuga quod.',105,52);
insert into comments (message,post_id,creator_id) values ('Eveniet temporibus dolorem in cumque placeat et vitae.',95,53);
insert into comments (message,post_id,creator_id) values ('Explicabo velit quae sapiente adipisci omnis et consequatur accusantium.',98,45);
insert into comments (message,post_id,creator_id) values ('Ab dolorem cum aspernatur quia.',102,47);
insert into comments (message,post_id,creator_id) values ('Saepe debitis sint sunt.',101,53);
insert into comments (message,post_id,creator_id) values ('Exercitationem non voluptatem sed nostrum libero.',99,57);


Now lets make a multiple inner joins call upon comments,posts,users table.

//Basic Call
select * from comments;

//Lets find post title where comment has been given.
select c.id, c.message,c.creator_id commentor_id,p.title,p.id post_id from comments c
inner join posts p on c.post_id = p.id;

//Lets find post creator Id on which comment has been given.
select c.message,
       p.title,p.id,
       u.id user_id_for_post,u2.id user_id_for_comment 
from comments c
inner join posts p on c.post_id = p.id
inner join users u on p."creatorId" = u.id
inner join users u2 on c.creator_id = u2.id;

Till now we have done 1 to many relationships, Now we are going to do Many to Many relationships.

create table favorites (
            user_id int references users(id),
            post_id int references posts(id),
            primary key (user_id,post_id)   --- Composite keys
        )

//favorites is same as like or loved anything you can say.
// It is many to many relationship.
//Because a single user can favorited multiple post and vice-versa.

create table friends (
            user_id1 int references users(id),
            user_id2 int references users(id),
            primary key (user_id1,user_id2) //composite keys
        )

//Same for friends table.
//One user can be friends with other user.


First of all lets delete entire data of our tables and create new data for aligning all tables.

TRUNCATE TABLE users,posts,comments,favorites,friends RESTART IDENTITY;

//Truncate can be used to delete tables data.
//RESTART IDENTITY is used to start primary key from start.


Use Data and paste it in your postgresql.


Count number of comments on particular post

select  count(*) from comments c 
    inner join users u on c.creator_id = u.id
    where c.post_id = 7;

Order By,Limit, Offset

Order By Whenever we want our results from query to follow a particular order based on any field, we can use 'order by' for this.

select * from users order by age;

//Above query will give us results based on age in increasing order, If we want //to change the order we can use **asc or desc** at the end to change order.

select * from users order by age asc;

select * from users order by age desc;

Limit Whenever we want to limit the result of our query we can use 'limit' for this.

select * from users order by age limit 10;

offset If we want to set pagination in our app, then we can use 'limit' and 'offset' together for showing this functionality like below example.

select * from users order by age offset 20 limit 10;

//offset will start reading query from that particular point in our query, it 
//will start reading from '20' upto limit '10'
More Advance Queries

1) Find a post of id=4 with its post_title, post_writer,comments_writer and comments_message.


2) Find a details about post with its post_title, post_writer,comments_writer , comments_message and user_id who favorites it.

select p.title,u.first_name post_writer,c.message,u2.first_name comment_writer,

f.user_id from posts p

inner join users u on p."creatorId" = u.id

inner join comments c on p.id = c.post_id

inner join users u2 on c.creator_id = u2.id

left join favorites f on f.post_id = p.id

where p.id = 7;


3) Find user who has most friends , show their friends list and count of their friend list.


select user_id1, array_agg(user_id2), count(*) from friends

group by user_id1 order by count(*) desc;


NOTE : Group By is little tricky, it will work only with some agg. functions.


4)Find post which is most popular.

select max(p.title),f.post_id,count(*) from favorites f

inner join posts p on p.id = f.post_id

group by post_id

order by count(*) desc;


5) Find user who has no friends.

select * from users u

left join friends f on f.user_id1 = u.id or f.user_id2 = u.id

where f.user_id1 is null;


6)Who has written no post.

select * from users u

left join posts p on u.id = p."creatorId"

where p."creatorId" is null;


7) Total no of post written by unique user.

select count(distinct "creatorId") from posts;


8)Find Post written by our Friends.

select * from posts p

inner join friends f

on (p."creatorId" = f.user_id1 or p."creatorId" = f.user_id2)

and (f.user_id1 = 1 or f.user_id2 = 1)

where p."creatorId" != 1


Note : Here we are trying to get post from friends as our id = 1, so we want post creator id != 1.


Till now, this is it guys. If i get a chance to post some more queries i will update this post.

59 views

Subscribe to Our Newsletter

© 2023 by BLIO

  • YouTube
  • Instagram
  • reddit-logo-newnew
  • medium-logo
  • LinkedIn
  • github-logo-new
  • Twitter
  • tiktok-logo-newnew
  • Facebook