Tutorial PostgreSQL. Introduction [part1]

in #utopian-io • 7 years ago (edited)

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) , you can update with
    sudo apt-get update

  • Third, you can 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, you can to change the password with:
    postgres=# ALTER ROLE postgres PASSWORD 'new';

To create the user, you can make the following:

  • You must create the user as root:
    #su postgres

    createuser -D -S -R -l new_user

  • After, you can 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 , you can assign the password:
    postgres=# ALTER USER new_user WITH ENCRYPTED PASSWORD 'newpass';

Creation of database

  • You must 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 can 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, you must enter as root:

    #su postgres

  • After, you can connect with the server PostgreSQL and Choose the database

    psql -d newdatabase -U new_user -W

  • Then, you can 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, you must enter as root:

    #su postgres

  • After, you can 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 data to the table

  • First, we enter as root:

    #su postgres

  • After, you can connect with the server PostgreSQL and Choose the database

    psql -d newdatabase -U new_user -W

  • You can execute the registration with INSERT INTO. With this operation, you can insert a single row .

    INSERT INTO person (name, password,email) VALUES ('ana', '123','[email protected]');

Updating Data in the table

  • First, we enter as root:

    #su postgres
  • After, you can connect with the server PostgreSQL and Choose the database
    psql -d newdatabase -U new_user -W
  • UPDATE with

    UPDATE person SET password='updatepass' where email='[email protected]';


-We check the changes with
SELECT * FROM person;

Delete Data of the table

You can use this SQL to remove the data in the table:
(Where email is "[email protected]")

DELETE FROM person where email='[email protected]';

Grant privileges to the user created.

  • You can proceed to grant 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

#tutorial #postgres #introduction
7 years ago in #utopian-io by
$0.00
    4 votes
    Reply 6
    Sort:  
  • Trending
    • [-]
     7 years ago (edited) 

    Your contribution cannot be approved because it does not follow the Utopian Rules.

    • The contribution must add value to the Open Source project. Moderators may reject (at their discretion) submissions that offer little to no added value to the project.

    Thanks you for your posting but this is too general tutorial about postgreSQL. There are so many similar tutorials out there.

    You can contact us on Discord.
    [utopian-moderator]

    $0.94
    • Past Payouts $0.94
    • - Author $0.71
    • - Curators $0.23
    2 votes
    Reply
    [-]
     7 years ago  

    Hey @dakeshi, I just gave you a tip for your hard work on moderation. Upvote this comment to support the utopian moderators and increase your future rewards!

    [-]
     7 years ago  

    Thanks for the suggestion, but based on other tutorials approved in utopian I thought it would be convenient to do this. Remember that it is the introduction for readers to have a notion of how to work. To then do advanced PostgreSQL tutorials

    $0.00
      Reply
      [-]
       7 years ago  

      Hello, I'm @dakeshi's supervisor!

      Sometimes, even though we don't want it to happen, low quality tutorials or tutorials of generally low value get approved for vastly different reasons. While this tutorial may be a great tool for someone to start with PostgreSQL, there's a lot of tutorials you can access by using external resources, like google & youtube, which means that this tutorial is not of great value to the project.

      Thank you for your understanding and I hope you can contribute some more advanced PostgreSQL tutorials to us!

      [-]
       7 years ago  

      Thank you very much for your answer. I'm more calm :). I can contact you to receive some observations of future tutorials that I want to do please

      $0.00
        Reply
        [-]
         7 years ago  

        The truth, I have not seen another tutorial that of some knowledge of PostgreSQL. For that reason, I wanted to start from scratch

        $0.00
          Reply