Mastering PostgreSQL: A Beginner's Guide

What is PostgreSQL

PostgreSQL is a free and open-source object-relational database management system. It is one of the most popular and trustable database systems that is used by most organizations, especially startups, for their backend development. PostgreSQL supports most of the backend frameworks such as Flask, Django, node js, and java springboot.

The advantages of PostgreSQL is that:

• It's open source
• It's robust and powerful enough to work on high-performance tasks
• It's backed by a strong community of developers.

How to install PostgreSQL

Before you start working with PostgreSQL, you need to download it first. The installation process may vary depending on your operating system, but it supports major operating systems.

You can download PostgreSQL Here. In this article, we are going to download it on Ubuntu, which is a Linux distribution sub-system.

To download, run this command in your terminal:

sudo apt install -y postgresql-common

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

After installation, you have to check its status using this command

sudo systemctl status postgresql

If everything is installed correctly, you will see a message that PostgreSQL is running.

Creating your first PostgreSQL database

Open your terminal and follow these steps as you create your first database

Step 1. Connect to the server

Before connecting to the server, switch to the PostgreSQL user using the following command:

sudo -i -u postgres

psql

To connect to the PostgreSQL server, you need to create a user with password using the following command:

CREATE USER myuser WITH PASSWORD 'mypassword';

Replace "myuser" with what you want to be your username and "mypassword" with your actual password.

Step 2. Create a database

Create a database using the following command. Replace "sampledb" with the name of your database:

CREATE DATABASE sampledb;

The terminal should look like this;

Step 3. Switch to the created database

Switch to the database using the following command:

\c sampledb

Step 4. Create a table

Let's create a simple table named students with columns for student ID, student name, student program, and student stream using this SQL command:

CREATE TABLE students(std_id INT PRIMARY KEY,std_name VARCHAR(240) NOT NULL, std_program VARCHAR(240) NOT NULL,std_stream VARCHAR(120) NOT NULL);

Step 5. Insert data into the table

Use the commands below to add data to the table:

INSERT INTO students(std_id, std_name, std_program, std_stream) VALUES(1, 'Kiprop Too', 'BENG', 'North');

INSERT INTO students VALUES(2, 'Kiprono Too', 'BTECH', 'West');

INSERT INTO students VALUES(3, 'Kiprono Nano', 'BTECH','East');

Step 6. Query data from the table

Use the command below to retrieve data from the Students table:

SELECT * FROM students;

Here is the output:

Step 7. Update data in the table

We are going to update the stream in column 3 from East to South:

UPDATE  students SET std_stream='South' WHERE std_id=2;

The output should look like this:

Step 8. Delete data from the table

Let's delete the last column from the table using this command:

DELETE FROM students WHERE  std_id=3;

The output should look like this:

Constraints in PostgreSQL

Constraints are rules that are declared in tables or columns of a database that are used to validate the data entered before being stored in the database.

Examples are:

• UNIQUE: This constraint makes sure that a value is unique, for example, an email.

• FOREIGN KEY: Foreign key is used to link a table with another table in one-to-many relationships or many-to-many relationships

• PRIMARY KEY: This is used to show something that uniquely identifies our data.

• NOT NULL: This makes sure that a value is not omitted or should not be blank.

Here is an example of FOREIGN KEY constraint being applied

CREATE TABLE students(std_id INT PRIMARY KEY,std_name VARCHAR(240) NOT NULL, std_program VARCHAR(240) NOT NULL,std_stream VARCHAR(120) NOT NULL);

Learn more about constraints here

Indexing for performance in PostgreSQL.

Indexing is something that improves the overall performance of a database. It is a shortcut for retrieving data quickly from a database. Picture this: it's like a table of contents in a book where you can locate what you want without flipping through the pages. Common types of indexing are B-tree index and Hash index.

Let's see examples of B-tree and Hash index indexes.

Hash index

-- Create a hash index on a single column

CREATE INDEX idx_column_name ON table_name USING hash (column_name);

B-tree index

-- Create a B-tree index on a single column

CREATE INDEX idx_column_name ON table_name (column_name);

-- Create a B-tree composite index on multiple columns

CREATE INDEX idx_multi_column ON table_name (column1, column2);

Here is an article that explains more about indexing.

Restoring and backing up data in PostgreSQL.

Regular backups are important for safeguarding data in your database.

Here's a simple command to back up data:

pg_dump -U your_username -d sampledb > backup.sql

Rstore data:

psql -U your_username -d new_database < backup.sql

Conclusion

PostgreSQL is a very useful database management tool which is used by both startups and well established companies for their backened.

This article covered the basic setup of PostgreSQL as well as how to perform CRUD functionalities. As you continue using PostgreSQL, learn more about advanced queries, indexing, and constraints to improve your proficiency. You can reach out to me via X Here for more interaction.

Happy coding!!