DEV Community

boopalan
boopalan

Posted on Edited on

postgress basics

Today without any decent knowledge in database we cannot
step in/completer even a small mini progjects, so companies will expcet
more deeper knowlege in the database.
The story of the database begin from the Telephone directory,

Telephone directory

In early day to get some ones phone number we use telephone directory, it helps to find out a right one's number what we want's form the thousands of numberes, We can idendify number by name, location and with some more additional informations for out actual requirement.

Yellow pages

It is helps for to get the business peoples numberes, finding some one's number for our requirements from thousands of numbere is not easy task, we do not who was doing which kind of work/business, instead of searching the business requirement from the whole telephone directory, we can get the required business numbers from the yellow pages directly, it act as a business advardiesment for the business peopels

one of the common thing from this two ways are saving the data in the method of rows and columns.

Arrival of personal computers

After the development of personal computers, we can able to see the many producets rais to saving data in our personal computers, from those all products Microsoft excel sheet is one of them. Microsoft excel is the one of the extended version of this feature for personal computer to saving data in row and columns order, micorsoft access is specifically designed for using it as an database.

excel rapidly developed with many features like spreadsheets, formulas, graphs and many more features but on the other hand micorsoft access is developed only for saving and retriving the data faster. it focus the speed and reliability and additional features for the business data storage management systems or it is commonly called as database management systems.

throughout this DBMS we can save data in multiple tables, tables and sheets are nearly look like similar maner, for the business requirement we can create a individual and multiple tables for saving the data, instead of saving the whole data in a complex single sheet.

Even though we have use multiple tables, we unable to resolve the relations between the multiple tables, to solve this issue later they move on the RDBMS. it resolve the issue of connecting multiple tables in a single database.

By using the RDBMS we can connect multiple tables, through out this method we can establish the relation ship between the more then one table, helps to track and resolve the business dependencies eventhoug in the complex table structure.

nowadays for development we mostly use the RDBMS.

Some of the RDBMS database are SQLite, MYSQL, Oracle Database and more.

What is SQL?

SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases.

It serves as the universal language for communicating with databases, allowing users to:

  • Query data (retrieve specific information)

  • Insert, update, and delete records

  • Create and modify database structures (tables, schemas)

SQL acts as a bridge between users/applications and the database management system (DBMS).

Most major database systems support SQL — including MySQL, PostgreSQL, Oracle, SQLite, etc., each databases may have slight variations.

NOTE: SQL is not the database itself — it's the language used to interact with it

Where we can use the SQLite?

  • it is serverless and no configuration needed database.

  • It is light weight and super fast database, we can choose it for the smaller application, most of the android's offline first apps are uses the SQLite database locally.

  • Perfect when we need a reliable, local, single-user or for small-team database management.

what is NOSQL?

it does not use the SQL, it does not store the data in the rows and colums structured formate, it save the data in the key value pair or in the JSON formate. It is flexible or schema-less
scalable alternative to relational databases

It sacrifices some consistency and complex querying ability in exchange for speed and flexibility.

Some of the examples for the NOSQL's are MongoDB, Apache Cassendra, Redis, Neo4j and more

which database we choose in here to study?

A single databsase cannot be solutions for all the poblems

Based one the busines requirement we choose the databases.
For example bancking and financial service are mostly depends on the the SQL. social medias, Iot services, real-time analytices are mostly like to the NOSQL.

At a fresher here we are going to choose which DB to study, here I am going to study the Postgresql, because it has several advances comparing to the other sql databases.
later we can be able to easily pick NOSQL and most of the other sql database will have similar concepts may little variations but it does not lead us to study from scratch.

what is PostgreSQL?

PostgreSQL is an open-source and Advance Object-Relational DataBase Management System(ORDBMS), it has excellent SQL standard and best fo the complex queries and more.

History

in the early development Postgresql is called in name of INteractive GRaphics and REsearch. Later they improved it to the next stage with many features and they called it and the POSTGRESQL.

what is the differrence between the Postgres and other databases

what is the differences betweent the ORDBMS and RDBMS

Features ORDBMS RDBMS
Core Concept Stores data in flat, two-dimensional tables of rows and columns. Stores data in tables, but also allows objects, classes, and inheritance.
Data Types Limited to basic types: Numbers, Text (Strings), Dates, and Booleans. Allows User-Defined Types (UDTs) and complex objects
Inheritance Not supported. Supports table and type inheritance (like OOP languages)
Data Integrity Relies on Primary Keys and Foreign Keys to link tables. Uses keys, but can also use pointers/references (like Object IDs) to link rows, similar to object-oriented programming.
Complex Data Hard to store complex data (like images, audio, or a whole "Customer Profile"). It is usually stored as a BLOB (Binary Large Object) outside the logic of the DB. Can store complex data natively. You can create a custom Customer_Type that includes their name, photo, and address as a single unit.
Functions & Behavior Data is "dumb." It only holds values. Logic lives in the application (e.g., Python or Java code). Supported. You can create a Person type, and then create a Student type that inherits Person's traits but adds a "Major".

Differences between the PostgreSQl and MySQL

Feature MySQL PostgreSQL
Best Known For Speed, reliability, and ease of use for standard web apps. Advanced features, complex queries, and data integrity.
Database Type Primarily a traditional RDBMS, but getting more object features. A true ORDBMS (Object-Relational Database).
Open Source License Owned by Oracle. Uses GPL (General Public License) with commercial licenses available. Completely free and open-source. Uses a very permissive PostgreSQL License (similar to MIT/BSD).
Complex Queries Good for simple SELECT, INSERT, UPDATE, DELETE statements. Superior for highly complex analytical queries, window functions, and Common Table Expressions (CTEs).
Data Integrity Historically allowed "sloppy" data (e.g., truncating text or accepting invalid dates) unless strict mode is enabled. Extremely strict. It will reject any invalid data to protect your database at all costs.
JSON Support Stores JSON as a string. You can search it, but it is not highly optimized. Treats JSON as a first-class citizen. You can index JSON data and query it incredibly fast (often used as a NoSQL alternative).
Speed Extremely fast for heavy read-only operations (SELECT queries). Extremely fast for heavy write operations, massive datasets, and complex joins.
Case Sensitivity Table names are tied to the operating system. On Linux, Users and users are different tables. Not case-sensitive for table names. Users and users are treated as the same thing (unless quoted).

why PostgreSQL is call ORDBMS?

PostgreSQL is called an Object-Relational Database Management System (ORDBMS) because it combines the classic features of a Relational Database (tables, rows, SQL, and relationships) with Object-Oriented Programming concepts (custom data types, objects, and methods/functions).

In the computer programming world (specifically Object-Oriented Programming, or OOP), an "object" is a single unit that bundles together Data (Properties) and Behavior (Methods/Actions).

  • Custom Data Types
  • Inheritance (Parent and Child Tables)
  • Methods / Stored Procedures

Installation 

sudo apt update
Enter fullscreen mode Exit fullscreen mode

it helps to fix the dependenies issues,

sudo apt install postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

it will install the postgresql database

sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

to startup on the booting postgresql

sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

it will help to enable the postgresql always startup when the computer boots

Who can use Postgres database

sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode

sudo : is helps we are give the permission for this software as the super user for doing things
-i : is the start a login shell [ Loading the User's Environment automatically ]
-u : is now act as the user

postgres : here postgres is the by default admin user for the postgresql database

here up to now we are just login as in the name of the postgres user name not yet in the database just we are switch by default user to the postgres user

suppose if we want go back we can simple enter the exit for postgres user to the normal user again

NOTE: here anyone can use the postgres database but at now or at the installation we have to use it in the default postgres user

how to use postgres

now we are actually going inside or connecting in to the postgresql database, if we give the psql

normally we can do similar kind of thing like whenever if the give the python in the terminal it will opens the python interpreter

for the security reasons by default other then postgres user database does not allows any one to login in the database.

NOTE: can we change or add another user to login in the database

What is the psql ?

psql is the interactive command-line client for the PostgreSQL server,

some of the helping commands

\h - help

\l - list

\r - stop | recover suppose if we entered an wrong command

name | Owner | Encoding | Collate | Ctype | Access previlages |

name of the database

Owner who is the owner of the database

to quit for the \l
esc → :q

to get all the databases form the postgress ? 

\l
Enter fullscreen mode Exit fullscreen mode

here we can ablet to see list of items in the name columns? this all are the default databases in the postgress

we will see about it later in this discussion

how to create a database ? 

create database database;
Enter fullscreen mode Exit fullscreen mode

it is insencitieve

CREATE DATABASE DADABASE;
Enter fullscreen mode Exit fullscreen mode

NOTE: whenever if we create or execute a command or query in the database
when have to use ; to define the commend is ending at that point.

CREATE DATABASE movies;
Enter fullscreen mode Exit fullscreen mode

we can able to see all the database name;

suppose or in by mistake if you create a database with wrong name how you will delete the database

DROP DATABASE movies;
Enter fullscreen mode Exit fullscreen mode

finally the database was deleted or removed from out databases

NOTE : it is not recomenede way to remove the do it

How to connect our database? 

\C DADABASENAME
Enter fullscreen mode Exit fullscreen mode

or

\c databasename
Enter fullscreen mode Exit fullscreen mode

it will connect to that particular database what we are looking to connect
when we given as the databasename

To see the version ?

do you think databases have version, just like version in software postgress also have versions, knowing version is importent to moving the development project to the production stage.

to get version of the postgress, we can use

select version();
Enter fullscreen mode Exit fullscreen mode

to esc form version

esc :q
Enter fullscreen mode Exit fullscreen mode

Getting current date using postgress 

select curent_data;
Enter fullscreen mode Exit fullscreen mode

it will get the current date for this query

to create a table in the database

what is the minimum requirement to create or ensure it is a table ?

In terms of table prespective, how we can ensure that what we are going to store in the table, for table we are creating it to save some kind of data, without need of saving date we do not need to creating table.

suppose if we try to create a table without defining the
any columns what will happen

DB=# create table TABLE
DB-#
Enter fullscreen mode Exit fullscreen mode

when we just give this line create table TABLE
without semi-colon

you can able to see that in the next line equal(=) sign changes into minus(-),

what was happening in the next line, was - indicate that postgress was actually waiting for your next command for what you will going to given as the next inputs, it will wait for until we ending the line using ;

to end the session just we can simple given the ;

DB=# create table TABLE
DB-#;
SOME ERROR
DB=#
Enter fullscreen mode Exit fullscreen mode

now, you can see an error with invalid sql error

what will be happen if we try to connect a database that does not exists? 

\c collagexyz;
Enter fullscreen mode Exit fullscreen mode

to connect to the table we created

be sure database was actually created before

\c collage;
Enter fullscreen mode Exit fullscreen mode

so postgress expect to user to create a at least one column in the table.

we want to define the value type in the columns it will helps to process the data faster and quiceker,
for a business perspective we can not compliment anything in the perforamnce

create table movie(id int);
Enter fullscreen mode Exit fullscreen mode

Types in PostgreSQL

s.no TYPE usage
1 CHAR(n) Fixed length text, having limit upto n
2 VARCHAR(n) variable length with n limit
3 TEXT Unlimited variable length
4 CHAR (without length) it will allow as to store only one letter
s.no TYPE usage
1 SMALLINT small whole numbers ( -32,768 to 32,767 ), we can use it for storing age, and small counts
2 INTEGER it allow as to store store values in between -2 to 2 billion
3 BIGINT some time integers requires to store more then billion values, in swituations like storing population exeds the count.
4 DECIMAL(p ,s) some times values need to store in the decimal points
5 NUMERIC(p ,s) it is similar to the DECIMAL
6 REAL Scientific measurements needs Approximate numbers.
7 DOUBLE PRECISION - Scientific calculations
8 SERIAL Auto-incrementing integer
9 BIGSERIAL Auto-incrementing big integer
s.no TYPE formate
1 DATE YYYY-MM-DD
1 TIME HH:MM:SS
1 TIMESTAMP YYYY-MM-DD HH:MM:SS
1 TIMESTAMPTZ YYYY-MM-DD HH:MM:SS TZ
1 INTERVAL 1 year 2 months
1 TIME WITH TZ HH:MM:SS TZ

BOOLEAN - instead of saving the TRUE, FALSE we can also use as a values NULL

JSON - JSON data

JSONB - Binary JSON

s.no Type usage formate
1 INET IP address 192.168.1.1
2 INET IP network 192.168.1.0/24
3 MACADDR MAC address 08:00:2b:01:02:03
4 POINT 2D point (x,y)
5 CIRCLE Circle <(x,y),r>
6 BOX Rectangle (x1,y1),(x2,y2)

anytype[] - Array of typed values

ENUM - helps to store string options in the column
('pending', 'processing', 'shipped', 'delivered')

MONEY - Currency amount

Let we see how to create table inside

can we create tables directly in the postgress ?

just now we saw how to create databases in the postgress but can we create tables in the
postgress, not the strecture of the tables we can only create tables inside database

suppose if we create tables without entering any database, it will show error

ensure that you are inside the database!


movies=#
Enter fullscreen mode Exit fullscreen mode

now we can see that postgres=# is changed movies=#

this indicates we are inside the movies tables

create table movie
(
movie_name varchar(80),
release_data date,
ticket int
);
Enter fullscreen mode Exit fullscreen mode

things we do not want to

create table movie
(
movie_name varchar(80),
release_data date,
ticket int,
);
Enter fullscreen mode Exit fullscreen mode

movie is the name of the table

create table movie here we are going to create a
table using the table keywords

and here we naming table as movie

movie_name, release_data, ticket
this all are the name of the columns and
we define the the value type of the columns

varchar is define we are actually going use string
value is a type for the movie_name column,
as well as we define the colmn type respectively for the release_date, ticket as the type of date and int

suppose if we try to create a table without defining the any columns what will happen

SELECT current_database();
Enter fullscreen mode Exit fullscreen mode

Insert data into the tables 

insert into movie values ('syperman', '2021-07-15', '120');
Enter fullscreen mode Exit fullscreen mode

inseting more datas

insert into movie values ('superman', '2022-05-02', '120');
Enter fullscreen mode Exit fullscreen mode 
insert into movie values ('flash', '2022-05-02', '120');
Enter fullscreen mode Exit fullscreen mode

here we are actually inserted data in a particular order, what will
happen if we inserted in a wrong formate ?

Suppose if we want to customize the data means how to it?

What will happen if we does not give any one value for this column

can we change the order of the insertion values

insert into movie (release_date, movie_name, ticket) values ( '2024-01-11', 'batman', 120);
Enter fullscreen mode Exit fullscreen mode 
insert into movie values ('superman', '2022-05-02', '120');
Enter fullscreen mode Exit fullscreen mode

this type of inerstion is positional arguments

insert into movie (release_date, movie_name, ticket) values ( '2024-01-11', 'batman', 120);
Enter fullscreen mode Exit fullscreen mode

then what is name of this type of insertion

how to select the values for the database tables

getting the values in the tables

* is the universal selector it will get all the values in the table

select * from movie;
Enter fullscreen mode Exit fullscreen mode

while quering the data incase you are typed wrongly column name to correcting if you get some help form prostgres we can do it easily to given an hand for the write the columns name we can use \d details of the table

\d TABLENAME
Enter fullscreen mode Exit fullscreen mode 
\d movie
Enter fullscreen mode Exit fullscreen mode 


select release_data, movie_name, ticket from movie;
Enter fullscreen mode Exit fullscreen mode

both methods are equal
at first attempt we are using universal selector *, at second attempt we are get the value of each by using it's column name

suppose if we do not want any columns we can skip it

select release_data, movie_name from movie;
Enter fullscreen mode Exit fullscreen mode 


select movie_name, ticket from movie;
Enter fullscreen mode Exit fullscreen mode 


select  ticket from movie;
Enter fullscreen mode Exit fullscreen mode

Interview questions to revision

1) what is postgreSQL?
2) DBMS vs RDBMS ?
3) SQL vs NOSQL ?
5) how do you find PostgreSQL version,
6) how to create Database ?
7) how to create Tables ?
8) PostgreSQL vd MySQL ?
9) what is sudo?
10) What is apt ?
11) Why PostgreSQL is ORDBMS ?

Top comments (0)