Beginner's Guide - Database Schemas in PgAdmin4

What is PostgreSQL ?

PostgreSQL is a powerful, open source Object Related Data Base Management System (ORDBMS) used for transactional and analytical workloads.  It incorporates and expands upon the SQL programming language designed to securely store and efficiently scale even the most complex data workloads.            

It began its journey in 1986 as part of POSTGRES project at the University of California, Berkeley. With over 38 years of continuous development, Postgres has evolved into one of the most reliable and feature- rich databases available today.  Whether you choose to build a web application, managing geospatial data or running enterprise level software, PostgreSQL offers the features and stability needed.

In database jargon , PostgreSQL uses client/server model.  This consists of two program processes.

1.       A server process which manages database files , accepts connections to DB from client applications and performs actions on behalf of the client. This database server program is called Postgres.

2.       The user’s client application (front end) that performs database operations. The client application can be diverse with any kind of tool such as text-oriented tool, graphical application, a web server.

Concept of Postgres Architecture:

What is a Cluster in Postgres?

·       A server by default runs on single port (eg: 5432) but can sometimes have multiple progress instances

 (running on different ports)

·       A single Postgres server or instance can contain multiple databases. Cluster means collection of databases hosted together .

·       Each database can have multiple application schemas – eg., employee schema, accounts schema etc.…  

·       Each schema can contain multiple tables , indices , views , etc.. with different types of objects

Hierarchy in Postgres :

There is a cluster with group of databases

each DB have multiple schemas

and each schema we have multiple objects.

This database fits into overall schema shows here the

relationship between clusters, databases, schemas and tables.

https://www.postgresql.org/docs/current/tutorial-arch.html

What is Postgres Schema ?

Schemas are collection of database objects that are logically grouped.  This objects include tables, views, indexes, data types, functions, operators and more .  The Important feature of schemas is  that , objects  with same name can exist in different schemas without conflict.

The prerequisites : Before getting into access with schema creations , you should have your environment set up correctly. 

Step 1: PostgreSQL with its administrative tool pg Admin 4 to be installed

https://www.w3schools.com/postgresql/postgresql_install.php

Step 2 : Initiate server configuration

Step 3:  Access the Server

Firstly, create the new database . 

Step 4 : Create new Schema

In Pg SQL , creating a new schema is straight forward.

So, along with the default public schema ,

Now we created a new schema with name.

Step 5:   Create tables for the sales_schema_1

Step 6:   Insert values to the Table

           Insert scripts in PgSQL allows seamless addition of data into tables using structured SQL commands, enabling efficient management of records.

Managing the scripts made easier with Pg Admin interface.

Next, in (step 6.2) , insert the values according to requirement .

To view the data in ( step 6.3)

Postgres provides several SQL statements to help manage the schemas

Pg Admin 4 provides user friendly interface for managing Pg SQL databases.

We  can also create new schemas  by expanding the schema tree and perform various operations such as  insert , update , drop or delete SQL command and schema statements.

Step 7 : Altering Table

The process to Alter an existing table from the schema we created is possible in Postgres.

Made changes to the table by adding a new column in fig (Step 7 )

Step 8 : Update the existing table

This command updates the Quantity to 5

where the product name is Television

To view the Product table now, lets run this select all from table command

By following these steps we can add multiple number of rows and columns to the existing table

Step 9 : To add multiple rows at a time

By using insert into statement with multiple values sets specified within a single command we can able to achieve the result . Each set of values should be separated by commas. However , Postgres automatically generates auto-increment values to the Serial numbers

To view the entire table with the multiple rows added

again the select all statement . Lets see ..

Step 10 : Row Drop

To drop a single row we typically use delete statement

To view the table

Step 11 : Column Drop

To remove the column from the table we have to use drop command

Alter statements are generally used to modify the existing table like add, delete.

Step 12 : Drop Table

Beware of Drop table statement , deleting the table will result in loss of information.

Let see the syntax

Lets Display the sales schema product table

Yes, The table is now does not exist .

Conclusion :

• Creating Schemas and tables in PostgreSQL allows us to build organized and efficient data.

• There are various benefits , like, Schemas can be used to isolate and segregate data and objects, providing level of security and access control. 

• Schemas also support modular design approach promoting reusability of database components.

• Tables defining in the structure  for data storage, including columns,  data types , constraints, indexes., ensures consistency and integrity of stored data.

• Data integrity rules are applied by following constraints methods like, primary key , foreign key and unique.

• This steps would help in improving performance of query optimization.

References :

https://www.w3schools.com/postgresql/postgresql_create_table.php