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) })