Tutorial PostgreSQL. Introduction [part1]

PostgreSQL

Introduction

On this occasion, I think it is very important to share a series of tutorials about the administrator of the PostgreSQL database. Because in my opinion it is one of the best database administrators in relation to relational DB. In this first part of the tutorial, I would like to focus more than anything on the first steps to start with this important tool. Teaching the installation and some features that will help people get started. As I go through the whole series, I will teach more complex functionalities that will help with the real development of a project. I hope you like it.

What Will I Learn?

• Understanding PostgreSQL.
• Instalation PostgreSQL 9.6 (Debian Jessie 8).
• Creation of users.
• Creation of database.
• You will learn : Create, Delete Table.
• You will learn : Insert, Update, Select, Remove register.
• Grant privileges to the user created.

Difficulty

• Basic

Understanding PostgreSQL.

PostgreSQL is a relational database manager, PostgreSQL's source code is available under a liberal open source license, is considered one of the most advanced database managers today; the first version of the code was public on August 1, 1996, released under the PostgreSQL License and developed by PGDG "PostgreSQL Global Development Group". Now are in your version 10.3 but for this tutorial we will used the version 9.6, It has support for different OS like:

• BSD
• macOS
• Linux
• Solaris
• Windows

In this opportunity we will use the operating system: Debian Jessie 8.

Installation PostgreSQL 9.6 (Debian Jessie 8).

• First you have to import repository signing key, and update the package lists, with the following command:
  
  
  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

• Second, as sudo (superuser) , update with:
  
  sudo apt-get update

• Third, proceed to install as sudo:
  
  sudo apt-get install postgresql-9.6

Creation of users.

Before of to create users, if you want change the default password of the user Postgres:

• You can use the following command as root:
  # su postgres
  and after psql postgres

• After, proceed to change the password with:
  postgres=# ALTER ROLE postgres PASSWORD 'new';

For to create the user, you can make the following:

• Proceed to create the user as root:
  
  
#su postgres

createuser -D -S -R -l new_user

  

• After, you connect with the server PostgreSQL to enter the password of created user:
  
  
psql postgres


• We check the user created:
  

postgres=# SELECT usename, passwd FROM pg_shadow;


• After of the connection , assign the password:
  

postgres=# ALTER USER new_user WITH ENCRYPTED PASSWORD 'newpass';


Creation of database

• You proceed to create database as root:
  
  
#su postgres

• After, you can create the database (first, enter the template of the database "template0", you can assign the user created previously in this case is "new_user", the coding of the database "UTF-8" and after the name of the database) :
  
  
createdb -Ttemplate0 -O new_user -EUTF-8 newdatabase


• After, you connect with the server PostgreSQL to check the changes
  
  
postgres@erickaackseriam:~$ psql postgres

• We check the changes
  
  
postgres=# SELECT datname FROM pg_database;

• Then to exit the connection:
  
  
postgres=# \q


Create Table to database

For example, we will to make a table with the name "user":

• First, we enter as root:
  
  
#su postgres


• After, you connect with the server PostgreSQL and Choose the database
  
  
psql -d newdatabase -U new_user -W

  

• Then, create the table with the following command
  

 CREATE TABLE person (
id SERIAL PRIMARY KEY NOT NULL,
name varchar(30) NOT NULL,
password varchar(30) NOT NULL,
email varchar(30) NOT NULL);

In this case the table will have the name "user", in the parentheses are the columns of the table, then you enter the datatype (varchar, int). In this case:

Id:

has the datatype SERIAL as primary key and NOT NULL.br>

name:

has the datatype varchar with a length of 30.

password:

has the datatype varchar with a length of 30.

email:

has the datatype varchar with a length of 30.

• To show the table created


\dt


Delete Table

• First, we enter as root:
  
  
#su postgres


• After, you connect with the server PostgreSQL and Choose the database
  
  
psql -d newdatabase -U new_user -W


• Then, remove the table with the following command
  

DROP TABLE person;

Inserting register to the table

• First, we enter as root:
  
  
#su postgres


• After, you connect with the server PostgreSQL and Choose the database
  
  
psql -d newdatabase -U new_user -W


• Proceed to enter the register with INSERT INTO. With this operation, you can insert a single row .
  
  
INSERT INTO person (name, password,email)
VALUES ('ana', '123','email@mail.com');

  

UPDATE register in the table

• First, we enter as root:
  
  
#su postgres

  After, you connect with the server PostgreSQL and Choose the database
  
psql -d newdatabase -U new_user -W

• Proceed to UPDATE register with
  
  
UPDATE person SET password='updatepass'
where email='email@mail.com';


-We check the changes with

SELECT * FROM person;


Grant privileges to the user created.

• Proceed to asign privileges to the table created. with:

(For example for all the privileges of the table)


GRANT ALL PRIVILEGES ON DATABASE newdatabase TO new_user;


---

Posted on Utopian.io - Rewarding Open Source Contributors

---