Commandes de base pour PostgreSQL

C’est toujours pratique d’avoir sous la main une liste de commandes de base de postgresql pour les tâches quotidiennes.

Lister les bases de données

psql=> \l
                              List of databases
     Name     |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges
--------------+----------+----------+------------+------------+-----------------------
 test         | test     | UTF8     | fr_FR.UTF8 | fr_FR.UTF8 |
(...)

Lister les tables

psql> \dt *

... 
public | metrics | table | charles
public | news    | table | charles
...

 

Créer une base de données postgres

psql> CREATE DATABASE sport WITH ENCODING 'UTF8' OWNER=charles ;

Syntaxe complète

CREATE DATABASE db_name
    OWNER =  role_name
    TEMPLATE = template
    ENCODING = encoding
    LC_COLLATE = collate
    LC_CTYPE = ctype
    TABLESPACE = tablespace_name
    CONNECTION LIMIT = max_concurrent_connection
  • db_name: le nom de la base de données. Ce nom est unique sur le serveur.
  • role_name: le rôle propriétaire de la base. Le rôle par défaut est celui de l’utilisateur qui exécute la commande.
  • template: le modèle de base pour la création de votre base. Par défaut, PostgreSQL utilise le template « template1 »
  • encoding: l’encodage de la base. Par défaut, c’est celui du template.
  • collate: définit les règles de collation. Ces règles agissent sur le tri (ORDER BY) des chaînes de caractères. Par défaut c’est celui du template qui est utilisé.
  • ctype: définit les classes de caractères (chiffre, minuscule, majuscule, …).  Par défaut, celui du template est utilisé.
  • tablespace_name: définit le tablespace , c’est à dire l’espace de stockage, où la base va être stockée. Par défaut, celui du template est utilisé.
  • max_concurrent_connection: définit le nombre de connexion simultanées sur la base. Par défaut, -1 est utilisé, qui signifie pas de limite.

 

Lister les contraintes d’une table

 

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.table_name='meals';

 

 constraint_name | table_name | column_name | foreign_table_name | foreign_column_name
-----------------+------------+-------------+--------------------+---------------------
 meals_pkey      | meals      | id          | meals              | id

Sauvegarder une base de données

 

$ pg_dump -U charles sport > sport.dump.sql

 

Importer un dump

 

$ psql -U charles sport < sport.dump.sql

 

Sauvegarder toutes les bases de données postgresql

 

$ pg_dumpall > all.dump.sql

 

Sauvegarder une table en particulier

 

$ pg_dump --table matches -U charles sport -f sport.matches.dump.sql

 

Supprimer toutes les tables d’une base

 

psql -U <user> -t -d <database> -c "SELECT 'DROP TABLE ' || n.nspname || '.' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables
psql -U <user> -d <database> -f /tmp/droptables

 

Exporter une requête en .csv

 

psql> COPY (SELECT * FROM ingredients) TO '/tmp/export.csv' WITH CSV HEADER;

 

Pour plus d’informations, la documentation officielle en français.