PostgreSQL – Vade-mecum SQL – 8/8 – Fonctions shell

Nous allons maintenant nous intéresser à ce qu’il est possible de faire en ligne de commande.

Comme la partie SQL est déjà traitée, nous allons voir ici les interactions entre les données de la BDD et l’environnent de la BDD (le système d’exploitation et les fichiers) et plus largement nous attarder sur les utilitaires qui peuvent être utilisés avec PostgreSQL.

Principes de bases

Je vous invite à bien intégrer la partie sur l’utilisation de PSQL au début de ce cours.

PostgreSQL – Tout comprendre – 3/3 – Connexion et structuration

« Piper les données »

Prononcez « païpé », à l’anglaise.

Lorsqu’il est nécessaire de traiter les données dans un outil avant de les utiliser avec une fonction shell, il faut indiquer dans le script « l’envoi » des données. Ceci se fait via l’utilisation du caractère barre verticale « | » appelé « pipe » en anglais (tuyau). Ce caractère se place entre les deux parties du script et met à disposition de la deuxième partie du script, les données issues de la première.

Travailler avec le SHELL

Nous ne travaillerons pas avec du pétrole mais plutôt avec l’interface en ligne de commande.

PostgreSQL

Voici quelques commandes utilisables avec PSQL.

Copier de la donnée

Il est possible de copier le contenu d’une table vers un fichier CSV et inversement.

-- Copier une table dans un fichier CSV
COPY "mon_schema".ma_table TO 'C:\chemin\fichier_de_sortie.csv' DELIMITER ',' CSV HEADER;


-- Copier un CSV vers une table
COPY "mon_schema".ma_table FROM 'C:\chemin\fichier_d_entree.csv' DELIMITER ',' CSV HEADER;

COPY "mon_schema".ma_table (colonne_1, colonne_2) FROM 'C:\chemin\fichier_d_entree.csv' DELIMITER ',' CSV HEADER;

Sous Windows (comme sur Linux mais les linuxien connaissent bien les problèmes de droit) il se peut que vous obteniez une erreur de ce type :

ERREUR: n’a pas pu ouvrir le fichier « C:\chemin\vers\mon\fichier.csv » pour une lecture : Permission denied

Windows

Il s’agit d’un problème de droit. Vous devez appliquer au fichier qui doit être lu les bons droits afin que l’utilisateur système puisse le lire. Pour cela, faites un clic droit sur le fichier puis « propriétés ». Dans l’onglet « Sécurité » modifiez les autorisations et ajoutez « Tous le monde » en lecture.

Voir les droits

Voici comment voir les droits appliqués sur les tables, schéma et cie en ligne de commande

-- Voir les droits appliqués sur un objet (table, schéma...)
\dp mon_objet

-- Droit appliqués par défaut sur un objet (table, schéma...)
\ddp mon_objet

Voici comment lire le résultat :

-- Le résultat
role_1=xxx/role_2+role_3=yyy/role_4+=zzz/role_5

Avec xxx, yyy et zzz les éléments suivants (différents suivant les objets) :

• r : SELECT (« read »)
• w : UPDATE (« write »)
• a : INSERT (« append »)
• d : DELETE
• D : TRUNCATE
• x : REFERENCES
• t : TRIGGER
• X : EXECUTE
• U : USAGE
• C : CREATE
• c : CONNECT
• T : TEMPORARY
• * : Possibilité de GRANT pour les privilèges précédents

Avec pour les utilisateurs :

• role_1 et role_3 : utilisateur ayant les privilèges
• role_2, role_4 et role_5 : utilisateur ayant accordé les privilèges
• : aucun utilisateur (cas du +=zzz) = public = tous les autres utilisateurs

Gérer les médias

Si vous avez bien suivi, il existe un format de colonne de type binaire. Ce format de donnée permet de stocker à peu près tout et n’importe quel document (vidéo, image, document pdf…).

J’ai consacré un article entier à la gestion de l’import de données binaires, vous en apprendrez plus par ici :

Importer des données binaires dans PostgreSQL

Les utilitaires

Plutôt que de lister les utilitaires et leurs options, j’ai préféré faire des articles dédiés pour chacun d’eux.

Sauvegarder

Utilisation de pg_dump, pg_dumpall, pg_restore, de la restauration fichier et un peu de PostGIS.

raster2pgsql

Voici un utilitaire qui permet d’importer des rasters dans PostgreSQL avec l’extension PostGIS.

osm2pgsql

Voici un utilitaire qui permet d’importer des données OSM dans PostgreSQL avec l’extension PostGIS.

Pour aller plus loin

Ce cours est maintenant terminé. Bravo, si vous avez suivi jusque là, vous devez être maintenant parfaitement autonome sur PostgreSQL.

Voici quelques liens pour aller plus loin :

• Documentation PostgreSQL (EN)
• Documentation PostgreSQL (FR)
• Documentation PostGIS (EN)
• Documentation PostGIS (FR)
• De bons tutoriels : www.postgresqltutorial.com
• Une excellente liste d’utilitaires pour PostgreSQL
• Un formateur de texte SQL

---

Besoin de revoir certaines parties ?

Sommaire général

Voici le sommaire général du cours :

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL tuto