www.expert-postgresql.fr

Pour obtenir une liste des objets (tables, vues, etc) pour tous les schémas présents dans une base PostgreSQL, on peut utiliser la vue correspondante dans les catalogues systèmes pg_catalog et information_schema.

Les vues définies dans information_schema sont définies au format SQL ANSI standard, et se retrouvent dans plusieurs moteurs de base de données (Sql server, MySql, PostgreSQL, etc). Les requêtes effectuée au travers du catalogue information_schema seront plus portable entre les différents moteurs de base de données. Par contre, si vous souhaitez afficher des informations spécifiques au moteur PosgreSQL, alors il faudra utiliser les vues de pg_catalog.

Pour obtenir une liste des objets (tables, vues, etc) pour tous les schémas présents dans une base PostgreSQL, on peut utiliser la vue correspondante dans le catalogue système pg_catalog de la base de données.

Par exemple, la requête suivante liste les tables présentes sur les schémas production et recette en utilisant la vue pg_catalog.pg_tables :

sql> SELECT * FROM pg_catalog.pg_tables
     WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 production | article   | system     |            | t          | f        | f           | f
 production | stock     | system     |            | t          | f        | f           | f
 recette    | article   | system     |            | t          | f        | f           | f
(3 rows)

Dans le catalogue pg_catalog, il y a plus d'une soixantaine de vues permettant de lister la composition de la base, tel que, par exemple, pg_indexes pour la liste des index, pg_user pour lister les utilisateurs, etc.

La clause WHERE de la requête précédente exclue les schémas systèmes pg_catalog et information_schema pour ne lister que le contenu des schémas utilisateurs.

Pour connaitre l'ensemble des vues disponibles de pg_catalog, on utilise la vue pg_catalog.pg_views :

sql> select viewname from pg_catalog.pg_views;
               viewname
---------------------------------------
 pg_shadow
 pg_roles
 pg_settings
 pg_file_settings
 pg_hba_file_rules
 pg_config
...
(128 rows)

Pour lister la composition d'un table, liste des colonne et le type des données, on utilise le catalogue information_schema avec sa vue columns.

Ici, par exemple, on liste la composition de la table stock avec le nom des colonnes et leurs type :

sql> SELECT table_name, column_name, data_type
     FROM information_schema.columns
     WHERE table_name = 'stock';
 table_name |  column_name   | data_type
------------+----------------+-----------
 stock      | stock_mvt_id   | bigint
 stock      | article_id     | bigint
 stock      | stock_mvt_qte  | integer
 stock      | stock_mvt_date | date
 stock      | stock_mvt_oper | character
(5 rows)

Pour lister l'ensemble des schémas, tables, colonnes et contraintes associées, on peut utiliser la requête suivante :

sql> SELECT 
     INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
     INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
     INFORMATION_SCHEMA.TABLES.TABLE_NAME,
     INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS 
     ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME NOT LIKE ('pg_%')
GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
    INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
    INFORMATION_SCHEMA.TABLES.TABLE_NAME,
    INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
ORDER BY 1, 2, 3, 5 desc;

 table_catalog | table_schema | table_name |  column_name   | constraint_type
---------------+--------------+------------+----------------+-----------------
 tutorial      | production   | article    | article_id     | PRIMARY KEY
 tutorial      | production   | article    | article_name   | PRIMARY KEY
 tutorial      | production   | article    | article_option | PRIMARY KEY
 tutorial      | production   | article    | article_price  | PRIMARY KEY
 tutorial      | production   | article    | article_id     | CHECK
 tutorial      | production   | article    | article_name   | CHECK
 tutorial      | production   | article    | article_option | CHECK
 tutorial      | production   | article    | article_price  | CHECK
 tutorial      | production   | stock      | article_id     | PRIMARY KEY
 tutorial      | production   | stock      | stock_mvt_date | PRIMARY KEY
 tutorial      | production   | stock      | stock_mvt_id   | PRIMARY KEY
 tutorial      | production   | stock      | stock_mvt_oper | PRIMARY KEY
 tutorial      | production   | stock      | stock_mvt_qte  | PRIMARY KEY
 tutorial      | production   | stock      | article_id     | CHECK
 tutorial      | production   | stock      | stock_mvt_date | CHECK
 tutorial      | production   | stock      | stock_mvt_id   | CHECK
 tutorial      | production   | stock      | stock_mvt_oper | CHECK
 tutorial      | production   | stock      | stock_mvt_qte  | CHECK
 tutorial      | recette      | article    | article_id     | PRIMARY KEY
 tutorial      | recette      | article    | article_name   | PRIMARY KEY
 tutorial      | recette      | article    | article_option | PRIMARY KEY
 tutorial      | recette      | article    | article_price  | PRIMARY KEY
 tutorial      | recette      | article    | article_id     | CHECK
 tutorial      | recette      | article    | article_name   | CHECK
 tutorial      | recette      | article    | article_option | CHECK
 tutorial      | recette      | article    | article_price  | CHECK
(26 rows)