[PostgreSQL] Quelques infos en vrac ..

 

Quelques infos en vrac ..

 

 


Structure de Postgresql (Fait depuis Draw.io)

Le schéma par défaut s’appelle Public.
Il est possible de créer plusieurs schéma au sein même d’une bdd.

 


/!\ Infos utiles

. Lors de la première connexion a Postgres, la DB par défaut est celle de Postgres.

. Le prompt:

. Un utilisateur ne peux créer/renommer/supprimer une database.

 


Créer, modifier, supprimer une base de données

Création de la DB

CREATE DATABASE mabasededonnees;

 

Modification du nom de la DB

ALTER DATABASE mabasededonnees RENAME TO ma_base_de_donnees;

 

Supprimer la DB

DROP DATABASE ma_base_de_donnees;

 

Pour lister les db’s

\l
\l+

 

Se connecter a une autre db depuis posgresql

\c nom_de_la_db

 

Se connecter a une autre db avec un autre utilisateur depuis posgresql

\c nom_de_la_db nom_du_user

 


Créer, modifier, supprimer un schéma

Création d’un schéma

CREATE SCHEMA monschema;

 

Modification du nom d’un schéma

ALTER SCHEMA monschema RENAME TO mon_schema;

Pour lister les schémas

\dnS
\dnS+

 

Pour connaître le schéma en cours

show search_path;

ou

select current_schema();

 

Pour changer le schéma par défaut

set search_path to mon_schema;

 

Supprimer un schéma vide

DROP SCHEMA mon_schema;

 

Supprimer un schéma et toutes ces tables
/!\ Il faut re-séter un schéma ou quitter la session pour retrouver un schéma par défaut (Public).

DROP SCHEMA mon_schema CASCADE;

 


Créer, modifier, supprimer une table

Création de la table

/!\ La table est constitué de colonne et est un pré-requis a la création.

CREATE TABLE matable (
colonne_Nom VARCHAR(20) NOT NULL,
colonne_Prenom VARCHAR(20) NOT NULL,
colonne_Email VARCHAR(30) NOT NULL,
Primary Key (colonne_Email));

 

Pour lister les tables

\dt
\dt+

Voir la description d’une table

\d nom_de_la_table

 

Modification du nom de la table

ALTER TABLE matable RENAME TO ma_table;

 

Supprimer une table

DROP TABLE ma_table;

/!\ Info pour la création d’une table

 


Insérer des données dans une table

Dans l’exemple, insérer des données en respectant le nombre de colonne.
/!\ Un champ ne peut etre vide, il faut ajouter null.
/!\ La Primary Key ayant été sété dans la colonne email, elle devient donc une valeur unique. Il n’est pas possible d’avoir un doublon.
(ERROR: duplicate key value violates unique constraint “ma_table_pkey”
DETAIL: Key (colonne_email)=(jean.dupont@monemail.fr) already exists.

INSERT INTO ma_table (colonne_Nom,colonne_Prenom,colonne_Email) VALUES ('Jean','Dupont','jean.dupont@monemail.fr');

 


Vider une table de toutes ces donnees

TRUNCATE ma_table;

 


Copie de table

Copier une table avec ces clés mais sans ces données

CREATE TABLE copie_de_ma_table (LIKE ma_table INCLUDING ALL);

 

Copier une table sans ces clés mais avec ces données

CREATE TABLE copie_2_de_ma_table AS SELECT * FROM ma_table;

 


Modifier une table

Ajouter une colonne (sans constraint)

ALTER TABLE ma_table ADD COLUMN colonne_Pays VARCHAR(20);

 

Ajouter la constraint d’une colonne

ALTER TABLE ma_table ALTER COLUMN colonne_Pays SET NOT NULL;

 

Supprimer la constraint d’une colonne

ALTER TABLE ma_table ALTER COLUMN colonne_Pays DROP NOT NULL;

 

Renommer une colonne

ALTER TABLE ma_table RENAME COLUMN colonne_Pays to colonne_Location;

 

Modifier le type de données d’une colonne

ALTER TABLE ma_table ALTER COLUMN colonne_Location TYPE CHAR(10);

 

Supprimer une colonne

ALTER TABLE ma_table DROP COLUMN colonne_Location;

 


 

 


 

 


 

 


 

 


 

 


Rappel des commandes interne

Lister les commandes

\h

 

Aide générale

\?

 

Lister les db’s

\l
\l+

 

Db en cours d’utilisation

\conninfo
SELECT CURRENT_DATABASE();

 

Liste les rôles des db’s

\dg
\du

 

Liste les schémas

\dn
\dn+
\dnS+
\dt
\di

 

Pour lister les tables

\dt
\dt+

 

Voir la description d’une table

\d nom_de_la_table

 

Liste les tables, vues et séquences et leur droits

\dp

 

Lister l’historique de toutes les commandes passées

\s

 

Changer de db, db/user

\c nom_de_la_base
\c nom_de_la_base nom_du_user

 

Importer et exécuter un fichier

\i nom_du_fichier

 

Quitter psql

\q

 

Appeler une commande shell (limité a certaines commandes (dir, pwd, df…), la commande cd s’exécute directement)

\! commande_système

 

Modifie le répertoire courant par répertoire.

\cd nom_du_répertoire

 

Lister les rôles et configs par base

\drds : liste config/role et par base

 

Liste les tablespaces (objets globaux : pgdefault, pgglobal)

\db
\db+

 

Lister les extensions installées

\dx
\dx+

 

 


 

 


Exporter le contenu d’une requête vers un fichier pst

begin;
\o /tmp/le_fichier_de_sorti.csv
\f ' ; '
\a
\i /tmp/la_requete.sql
commit

Installation de PostgreSQL sur Ubuntu

$ sudo apt-get install postgresql postgresql-contrib

Connexion a postgres

# su - postgres

postgres@lnx:~$

Puis

psql nom-de_la_base

ou

$ sudo -i -u postgres

postgres@lnx:~$

Puis

psql nom-de_la_base

ou

psql -U postgres -h localhost -p 5432 -d postgres

Se connecter a la db (le prompt changera pour le nom de la db)

postgres@lnx:~$ psql db_de_test
psql (9.5.12)
Type "help" for help.

db_de_test=#

Création d’une table

Dans l’exemple, je crée une table d’utilisateurs avec id, prénom, nom et adresse émail

db_de_test=# CREATE TABLE utilisateurs (id int, prenom varchar, nom varchar, adresse_email varchar);

cela renverra

CREATE TABLE

 

Puis nous renseignons cette table avec les valeurs suivantes

db_de_test=# INSERT INTO utilisateurs VALUES (1, 'Pierre', 'doras', 'pierre.doras@domaine.tld');

cela renverra

INSERT 0 1

 

db_de_test=# INSERT INTO utilisateurs VALUES (2, 'Moussa', 'toure');

cela renverra

INSERT 0 1

 

db_de_test=# INSERT INTO utilisateurs VALUES (3, 'Lei', 'Wu', 'lei.wu@domaine.tld');

cela renverra

INSERT 0 1

 

Enfin, nous listons la table pour voir si tout est correct.

db_de_test=# SELECT * FROM utilisateurs;
id | prenom | nom   | adresse_email 
----+--------+-------+--------------------------
 1 | Pierre | doras | pierre.doras@domaine.tld
 2 | Moussa | toure | 
 3 | Lei    | Wu    | lei.wu@domaine.tld
(3 rows)

Modifier une ligne du schéma utilisateurs de la db db_de_test

On s’aperçoit que Moussa n’a pas d’adresse émail, on peut modifier la ligne avec la commande update.

db_de_test=# update utilisateurs set adresse_email = 'moussa.toure@domaine.tld' where id = 2 ;

Ce qui renvoi

UPDATE 1

 

Si on relance un select * from utilisateurs, la ligne est bien modifié.

 

Il est aussi possible de changer toute ou une partie des champs, voici une requête pour cela, imaginons que que Pierre Doras décide de changer de sexe, se marie et change de nom (soyons fou !)

2 façons d’écrire cette requête.

avec un saut de ligne pour une meilleure compréhension

db_de_test=# UPDATE utilisateurs
SET prenom = 'Solange',
 nom = 'Eustache',
 adresse_email = 'solange.eustache@domaine.tld'
WHERE id = 1;

Ou

UPDATE utilisateurs SET prenon = 'Solange', nom = 'Eustache', adresse_email = 'solange.eustache@domaine.tld' WHERE id = 1;

 

Le schéma est bien modifié.

db_de_test=# select * from utilisateurs;
 id | prenom | nom | adresse_email 
----+---------+----------+------------------------------
 3 | Lei | Wu | lei.wu@domaine.tld
 2 | Moussa | toure | moussa.toure@domaine.tld
 1 | Solange | Eustache | solange.eustache@domaine.tld
(3 rows)

Begin, commit et rollback

/!\ /!\ /!\ Important /!\ /!\ /!\

Il est conseillé d’utiliser la commande begin; avant chaque instruction.

Celle ci permet d’exécuter les commandes, de voir les erreurs et de rollback si besoin ou de valider avec la commande commit;

Pour reprendre la doc officiel

BEGIN initie un bloc de transaction, c'est-à-dire que toutes les instructions apparaissant après la commande BEGIN sont exécutées dans une seule transaction jusqu'à ce qu'un COMMIT ou ROLLBACK explicite soit exécuté. Par défaut (sans BEGIN), PostgreSQL™ exécute les transactions en mode « autocommit », c'est-à-dire que chaque instruction est exécutée dans sa propre transaction et une validation (commit) est traitée implicitement à la fin de l'instruction (si l'exécution a réussi, sinon une annulation est exécutée).

Suppression d’une db

/!\ La db et tout ce qu’elle contient sera intégralement supprimé.

taper la commande

postgres@lnx:~$ dropdb db_de_test;

Ce qui renverra en voulant se connecter

postgres@lnx:~$ psql db_de_test

psql: FATAL: database “db_de_test” does not exist


Lister les db

# psql -l
List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
------------+----------+----------+-------------+-------------+-----------------------
 db_de_test | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | 
 postgres   | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | 
 template0  | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +
            |          |      |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +
            |          |      |             |             | postgres=CTc/postgres
(4 rows)

Pour lister le schéma d’une table

db_de_test=# select * from utilisateurs;
id | prenom | nom | adresse_email 
----+--------+-------+--------------------------
 1 | Pierre | doras | pierre.doras@domaine.tld
 2 | Moussa | toure | moussa.toure@domaine.tld
 3 | Lei    | Wu    | lei.wu@domaine.tld
(3 rows)

Pour créer un user

db_de_test=# CREATE ROLE "nom_du_user" WITH LOGIN;

Lister les schéma

db_de_test=# \dn
 List of schemas
 Name   | Owner 
--------+----------
 public | postgres
(1 row)

Ou lister les schemas/permissions

db_de_test=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)

Lister les utilisateurs

db_de_test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 bef       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

ou lister un utilisateur

db_de_test=# \du bef
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 bef       |            | {}

Lister schema/relation/table

db_de_test=# \d

Pour afficher l’historique

db_de_test=# \s

Pour lister une table

db_de_test=# select * from utilisateurs;
id  | prenom  | nom      | adresse_email 
----+---------+----------+------------------------------
 3  | Lei     | Wu       | lei.wu@domaine.tld
 2  | Moussa  | toure    | moussa.toure@domaine.tld
 1  | Solange | Eustache | solange.eustache@domaine.tld
(3 rows)

Créer une requête dans un fichier et l’importer

Sortir de la db, éditer un fichier et y renseigner sa requête

postgres@lnx:~$ vim test_requete

Puis pour l’exécution il faudra se reconnecter a la db et lancer la commande suivante (ne pas oublier de faire un begin; avant)

db_de_test=# \i /var/lib/pgsql/test_requete

 

 

 

No votes yet.
Please wait...

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.