Shiva Kumar 's Blog

Start small,Think Big

Postgresql Basic Tutorial (Part I)

Creating a database in PostgreSQL

Dropping a database

Connecting to database

The above command will give you access to the database once you are inside you can run the following commands. To get help on certain commands.

\l -> returns list of database
\dt -> returns list of tables
\du  -> returns list of roles
\q -> this will quit psql and back to shell
\? -> returns all the commands


This will create a table called users and locations, along with column name and type of column respectively.
‘name’ column has a data type varchar(100), that can store arbitrary character string up to 100 characters.
Then there are int and date which accepts data of type integer and date.

This will delete the table

Populating

Insert statement is used to populate data in the table. You can list the columns in a different order and you can skip some columns if they are not required.

Retrieving

This will retrieve all the rows from the table.

This will retrieve only name,email and age columns from all the rows.

We can also do some data manipulation while retrieving the data. Something like

‘as’ keyword relabels the row in the output result, which is also optional.
We will get back to avg keyword later

where clause can be used to extract the exact data from the row

This will return all the records from the users table whose age is greater than 60. We can also add multiple conditions to the query like return users whose age is greater than 70 and born on 1939-02-19(here format is important i.e YY-MM-DD)

Adding more data to show to show how distinct works

DISTINCT will remove duplicate rows from results. Below query will return just Gotham and Krypton even though we have 2 records of birth_place as gotham

JOINS

Till now we were retrieving data from a single table.
In Relational database, we can retrieve data from multiple tables using joins.

This will join the two table and result will contain the data from both tables where birth_place is equal to the city. This will work since the column name in both tables are different. What if columns have the same name? We need to mention the table name which will be like(this is considered as a best practice to ignore the column name duplication). This is also called inner joins

What if we want to scan through all the record in the left table and match the column value equal in the right table but also return who’s value does not match(columns values will be empty). This is called left outer joins

Right outer joins merge both the table who’s conditions are satisfied and also return the values from the right table and returns empty from the value for the left table(reverse of left outer join)

Full outer join = Left outer join + Right outer join

Aggregate Functions

Aggregate functions compute result in multiple rows like max, min, avg, sum

let’s calculate the average age of all superheroes. We have used an avg function previously.

We can also perform multiple aggregate function on single query. Like below

Subqueries are query inside a query output of one query to another query

This query first returns the minimum age from users and gives input to query where we want age greater than the minimum age

We can subquery another table and that result can be given as input to another table

Updating

Updating a column can be simply done with an update command on a specific table and column. We can also update multiple columns

Deletion

Deleting a row is similar to update where you specify the row you want to delete from a table with a where clause

This will delete all the rows (never do that until and unless if you sure about it)

In part 2 we will get into advanced topics like Views, Foreign Keys, Transactions, Inheritance


Leave a Reply

Your email address will not be published. Required fields are marked *