DEV Community

boopalan
boopalan

Posted on

postgress basics

Today without the decent knowledge in the database we cannot
step in even in a small mini progjects, so companies will expcet
more deper 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 number what we want form the thousands of numberes, We can idendify number by name, location and with some more additional informations.

Yellow pages

It is helps for to get the business numberes, finding some for our requirement from the thousands of numbere is not easy, we do not who was doing which kind of work, instead of searching the business requirement from the whole telephone directory, we can get the required business numbers from the yellow pages directly

Arrival of personal computers

one of the common thing from this two ways is saving the data in the method of rows and columns. After the development fo the personal computers, we can see the Microsoft excel sheet is the one of the extended version of this feature for personal computer to saving data, 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 one the other hand micorsoft access is developed for only saving and retriving the data faster. it focus the speed and reliability and additional features for the business data storage management systems.

throughout this DBMS we can save data multiple tables, tables and sheets are nearly look like similar, for the business requirement we can create a individual 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 and 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 one of the common term to define the relation ship establishing universal language between the users requirement and the communications between the database. It acts as a standard language to communication, this standard is accepted by many companies and they developed there vendor software to support this SQL.

Where we us the SQLite?

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

TODO

log file? need to study more on it, topics like log file reading and more
it is related to big data, relation ship between the data science and ML

what is NOSQL?

it does not use the SQL, it does not store the data in the structured formate, it save the data in the key value pare or in the JSON formate. It is flexible or schema-less

Graph based modes like a location, time based factores we can recommend them suggessions, it acts the nodes and it connect the similer and suggessions for the users to make them sustain more time in there platoform.

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

which database we want to study?

Based one the busines requirement we can choose the DB. We cannot use the only one DB for all the business requirements, for example bancking anf financila service are mostly depends on the the SQL. social medias, Iot services, real-time analytices are mostly depends on the NOSQL.

At a fresher which DB you want to choose 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 to the postgres

what is PostgreSQL?

It is Advance Object-Relational DBMS, it has excellent SQL standard and best fo the complex queries and more.

what is the differrence between the POstgres and other databases

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.

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

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

CHAR(n) - Fixed length text, having limit upto n

VARCHAR(n) - variable length with n limit

TEXT - Unlimited variable length

CHAR (without length) - it will allow as to store only one letter

SMALLINT - small whole numbers ( -32,768 to 32,767 ), we can use it for storing age, and small counts

INTEGER - it allow as to store store values in between -2 to 2 billion

BIGINT - some time integers requires to store more then billion values, in swituations like storing population exeds the count.

DECIMAL(p,s) - some times values need to store in the decimal points

NUMERIC(p,s) - it is similar to the DECIMAL

REAL - Scientific measurements needs Approximate numbers.

DOUBLE PRECISION - Scientific calculations

SERIAL - Auto-incrementing integer

BIGSERIAL - Auto-incrementing big integer

DATE YYYY-MM-DD

TIME HH:MM:SS

TIMESTAMP YYYY-MM-DD HH:MM:SS

TIMESTAMPTZ YYYY-MM-DD HH:MM:SS TZ

INTERVAL 1 year 2 months

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

INET - IP address 192.168.1.1

INET - IP network 192.168.1.0/24

MACADDR - MAC address 08:00:2b:01:02:03

POINT - 2D point (x,y)

CIRCLE - Circle <(x,y),r>

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 
select release_date, movie_name, ticket from movie
Enter fullscreen mode Exit fullscreen mode

both methods are equal

* is an universal selector

this kind of questions must be clear only if we practice contineously
update tablename
update table tablenaem

create table name
create tablename

select movie_name, release_date, ticket*4  as family_price from movie;
Enter fullscreen mode Exit fullscreen mode

here the value of the ticket is taken from the table and multiplied by 4 and return then to us, It does not update the values in the table

Top comments (0)