PostgreSQL

 It is open source , many companies use that, specially startups use that instead of using Oracle which is paid

it is reliable, feature robustness and performance

It is world's Most advanced Open Source Relational Database

Postgres is Object Relational Database Management System--> means Table inheritance and Function Overloading 

It runs on All OS Unix, Linux and others

It supports storing text, image, sound, video 

In PostgreSql table can inherit properties of Parent Table

We can install additional extensions to add additional functionality to postgreSql

Port is 5432

To show database in CLI

\l

Create Database

create database database_name

Connect to Database

\c database_name (here c is for connect)

Delete Database

drop Database_name

To Exit

\q

Install PgAdmin4

Connect to Database by Hostname , username/password

Once you connect you can see number of databases present (here 28)

Create Table with Primary Key

create table table_name(id integer not null primary key, fname text not null, lname text not null, age integer not null )

To Describe table

\d

Drop Table

drop table table_name

Create table from PgAdmin4 GUI

To See table Properties in PGadmin4

Right Click on table and select Properties

Properties can help you in seeing columns Refer below image

What is Schema

Schema can be collection of Tables, Views

You can create your own schema as well

How to Execute Query on PGAdmin4

Under Tools--> Query Tool

How to View Data of Tables 

Right Click on Table--> View Data--> All rows

PostgreSql Use Treminal in PgAdmin to describe Table

Click on terminal and type \d table_name

\d is for describing table

To See all tables in Postgres

In Terminal

\dt 

Sorting Data

Select * from users order by creation_time DESC;

PostgreSQL – UUID Data Type

UUID is an abbreviation for Universal Unique Identifier defined by RFC 4122 and has a size of 128-bit. It is created using internal algorithms that always generate a unique value.

PostgreSQL has its own UUID data type and provides modules to generate them.

UUID is automatically generated we don't need to provide it

CREATE TABLE contacts (

    contact_id uuid DEFAULT uuid_generate_v4 (),

    first_name VARCHAR NOT NULL,

    last_name VARCHAR NOT NULL,

    email VARCHAR NOT NULL,

    phone VARCHAR,

    PRIMARY KEY (contact_id)

);

Note: while inserting into above table we will insert value for columns other than UUID

we can also have integer array in Posgres

integer[]

Select 2nd Salary from Arrays

Select from 2d Array

Select from Array with where Clause

We can create Enum if we want to create data of our own type

Observe newly created Type will be shown in Types on Left Side

How to create a record with Type(we created above)

Insert record

IF we try to insert value other than Enum values we will get Error

Since good was not part of Enum we will get Error