PostgreSQL

22/10/20 danny

Historique

PostgreSQL est un

SGBDR : Système de gestion de base de données relationnelle
RDBMS : Relational database management system

Installation

# Installation PostgreSQL 10
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib --yes

# Creation du compte
sudo -i -u postgres psql -c "ALTER USER postgres PASSWORD 'your-password';"

Principaux types de données

Nom	Description
boolean	Booléen (Vrai/Faux)
bigint	Entier signé sur huit octets
char[ (n) ]	Chaîne de caractères de longueur fixe
varchar [ (n) ]	Chaîne de caractères de longueur variable
date	Date du calendrier (année, mois, jour)
integer	Entier signé sur quatre octets
timestamp with time zone	Date et heure, avec fuseau horaire
text	Chaîne de caractères de longueur variable
numeric [ (p, s) ]	Nombre exact dont la précision peut être spécifiée
uuid	identifiant unique universel

Lignes de commandes

Connection en ligne de commande

psql --username postgres

Base de données

# Création d'une base de données movie_pg
CREATE DATABASE movie_pg

# Suppression de la base de données movie_pg
DROP DATABASE movie_pg

# Renommer une base de données
ALTER DATABASE "old_database_name" RENAME TO "new_database_name"

# Vérification, suppression et création
DROP DATABASE IF EXISTS movie_pg;
CREATE DATABASE movie_pg;

# Liste des database
SELECT datname FROM pg_database;

Domains

# Création du domain dom_char de type char(1)
CREATE DOMAIN dom_char AS char(1)

# Suppression du domaine dom_char
DROP DOMAIN dom_char

Séquences

# Création de la séquence
CREATE SEQUENCE movie_id_seq
INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807
START 1000 CACHE 1;

ALTER SEQUENCE movie_id_seq OWNER TO postgres;

# Suppression de la séquence
DROP SEQUENCE movie_id_seq;

Tables

# Création d'une table
CREATE TABLE movie
(
    id dom_pk PRIMARY KEY NOT NULL DEFAULT nextval('movie_id_seq'::regclass),
    name dom_lib
)

# Suppression d'une table
DROP TABLE movie;

Insertion

# Insertion d'enregistrements
INSERT INTO continent(name) VALUES('europe');

Select

SELECT * FROM movie;
SELECT * FROM movie ORDER BY id DESC;
SELECT * FROM movie ORDER BY name DESC;

Exemples de scripts

DATABASE

CREATE DATABASE movie_pg

DOMAINS

CREATE DOMAIN dom_boolean AS boolean;
CREATE DOMAIN dom_char AS char(1) DEFAULT null;
CREATE DOMAIN dom_comment AS varchar(200) DEFAULT null;
CREATE DOMAIN dom_comment_long AS varchar(400) DEFAULT null;
CREATE DOMAIN dom_comment_xlong AS varchar(1000) DEFAULT null;
CREATE DOMAIN dom_date AS date DEFAULT null;
CREATE DOMAIN dom_datetime AS timestamp with time zone DEFAULT null;
CREATE DOMAIN dom_float AS float DEFAULT null;
CREATE DOMAIN dom_fk AS integer DEFAULT null;
CREATE DOMAIN dom_integer AS integer DEFAULT null;
CREATE DOMAIN dom_bigint AS bigint DEFAULT null;
CREATE DOMAIN dom_lib AS varchar(50) DEFAULT null;
CREATE DOMAIN dom_lib_long AS varchar(100) DEFAULT null;
CREATE DOMAIN dom_lib_xlong AS varchar(200) DEFAULT null;
CREATE DOMAIN dom_lib_short AS varchar(20) DEFAULT null;
CREATE DOMAIN dom_long AS bigint DEFAULT null;
CREATE DOMAIN dom_text AS text DEFAULT null;
CREATE DOMAIN dom_numeric AS numeric(15,2) DEFAULT null;
CREATE DOMAIN dom_pk AS integer DEFAULT null;
CREATE DOMAIN dom_uuid AS uuid DEFAULT null;

TABLES

CREATE SEQUENCE movie_id_seq
INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807
START 1000 CACHE 1;

ALTER SEQUENCE movie_id_seq OWNER TO postgres;

CREATE TABLE movie
(
    id dom_pk PRIMARY KEY NOT NULL DEFAULT nextval('continent_id_seq'::regclass),
    name dom_lib
);

INSERTION

INSERT INTO movie(name) VALUES('Iron Man');
INSERT INTO movie(name) VALUES('The Incredible Hulk');
INSERT INTO movie(name) VALUES('Iron Man 2');
INSERT INTO movie(name) VALUES('Thor');
INSERT INTO movie(name) VALUES('Captain America: The First Avenger');
INSERT INTO movie(name) VALUES('Marvel''s The Avengers');
INSERT INTO movie(name) VALUES('Iron Man 3');
INSERT INTO movie(name) VALUES('Thor: The Dark World');
INSERT INTO movie(name) VALUES('Captain America: The Winter Soldier');
INSERT INTO movie(name) VALUES('Guardians of the Galaxy');
INSERT INTO movie(name) VALUES('Avengers: Age of Ultron');
INSERT INTO movie(name) VALUES('Ant-Man');
INSERT INTO movie(name) VALUES('Captain America: Civil War');
INSERT INTO movie(name) VALUES('Doctor Strange');
INSERT INTO movie(name) VALUES('Guardians of the Galaxy Vol. 2');
INSERT INTO movie(name) VALUES('Spider-Man: Homecoming');
INSERT INTO movie(name) VALUES('Thor: Ragnarok');
INSERT INTO movie(name) VALUES('Black Panther');
INSERT INTO movie(name) VALUES('Avengers: Infinity War');
INSERT INTO movie(name) VALUES('Ant-Man and the Wasp');
INSERT INTO movie(name) VALUES('Captain Marvel');
INSERT INTO movie(name) VALUES('Avengers: Endgame');
INSERT INTO movie(name) VALUES('Spider-Man: Far From Home');

Utilisation de pg-promise

Tester le résultat de requêtes.

existsUsername(username) {
  return this.db.oneOrNone('SELECT * FROM users WHERE username = $1 LIMIT 1', 
username, a => !!a);
}


existsUsername(username){
  this.db.one('SELECT EXISTS(SELECT 1 FROM users WHERE username = $1)', username)
         .then(data => {
           if(data.exists == true){
             return true;
           } else {
             return false;
           }
         });
}

Await vs async

function findItem() {
  return new Promise(function (resolve, reject) {
    let table = 'continent';
    let keyd = 'AF';
    db.one("SELECT count(id) as count FROM continent")
      .dthen(record => {
        console.log('- Execute findItem ' + JSON.stringify(record));
        let exist = (record.count != 0);
        resolve(exist);
      })
      .catch((err) => {
        console.log('- Error on Execute findItem ' + ' -> ' + 'data' + ' : ' + err);
        reject(false);
      });
  });
}

async function findItem() {
  let sql =
    'SELECT count(id) as count FROM continent';
  try {
    const result = await db.one(sql);
    console.log('0000:' + JSON.stringify(result));
    return result;
  }
  catch (err) {
    console.log('4444:' + err);
    return null;
  }
};


let test = findItem();
test
  .then(res => { console.log('0001:res' + res) })
  .catch(err => { console.log('0002:err' + err) })