Lire les colonnes et les clés d'une base

Tu veux joindre deux tables, mais le nom d'une colonne dans l'énoncé ne correspond pas à la base. Deviner un nom proche peut produire une erreur ou une jointure sur la mauvaise donnée. Le schéma décrit ce qui existe réellement.

Cette fiche travaille le contrôle et l'extraction des données, une compétence du bloc BC01.

Ton besoin	Où travailler
Trouver les colonnes et leurs types	Sections 1 et 2
Lire les clés d'une table	Section 3
Cartographier toute la base	Section 4, au besoin

1. Commencer par une seule table

Dans psql, la commande \d orders affiche les colonnes, les types et les contraintes de orders. Ce n'est pas une instruction SQL, donc elle ne s'exécute pas avec le bouton de cette page.

Le même premier contrôle est possible en SQL :

SQL

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
ORDER BY ordinal_position
LIMIT 5;

Voici les cinq premières lignes :

column_name	data_type	is_nullable
order_id	character varying	NO
order_customer_id	character varying	NO
order_status	character varying	NO
order_purchase_timestamp	timestamp without time zone	NO
order_approved_at	timestamp without time zone	YES

Avant de continuer, interprète YES sur order_approved_at. Cela signifie-t-il qu'une valeur manque réellement dans cette colonne ?

is_nullable = YES décrit ce que le schéma autorise : la colonne peut contenir NULL. Il faut interroger les données pour savoir si cela arrive. Le schéma et le contenu répondent à deux questions différentes.

2. Vérifier un nom avant d'écrire la jointure

Tu cherches les colonnes qui parlent de client dans orders :

SQL

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
  AND column_name LIKE '%customer%'
ORDER BY ordinal_position;

Le résultat contient order_customer_id. Une requête qui utilise orders.customer_id échoue, même si ce nom paraissait plausible.

Modifie maintenant la requête pour chercher les colonnes contenant order dans order_payments. Avant de l'exécuter, écris le nom que tu prévois. Si le résultat diffère, utilise le nom fourni par le schéma dans ta prochaine requête.

Si tu bloques, change seulement table_name et le texte placé après LIKE.

3. Lire les clés sans déplier les catalogues

Une clé primaire identifie une ligne de façon unique. Une clé étrangère indique qu'une colonne fait référence à une autre table. PostgreSQL sait afficher les deux sous une forme lisible :

SQL

SELECT conname AS contrainte,
       CASE contype
           WHEN 'p' THEN 'cle primaire'
           WHEN 'f' THEN 'cle etrangere'
       END AS type_contrainte,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass
  AND contype IN ('p', 'f')
ORDER BY contype DESC, conname;

Avant d'exécuter, prédis si la clé primaire de order_items porte sur une ou plusieurs colonnes. Lis ensuite la définition qui commence par PRIMARY KEY.

Une clé composée porte sur plusieurs colonnes ensemble. Si la définition est PRIMARY KEY (order_id, order_item_id), c'est le couple qui est unique. Une commande peut donc occuper plusieurs lignes dans order_items.

Choisis ensuite une autre table et adapte seulement le nom placé avant ::regclass. Pour vérifier ton interprétation, écris en une phrase ce qu'une ligne de cette table représente, puis affiche cinq lignes de données.

4. Au besoin : cartographier les contraintes de toute la base

Cette requête utilise les catalogues internes de PostgreSQL et pg_get_constraintdef pour afficher les clés sans les reconstruire colonne par colonne.

SQL

SELECT c.relname AS table_name,
       con.conname AS contrainte,
       CASE con.contype
           WHEN 'p' THEN 'cle primaire'
           WHEN 'f' THEN 'cle etrangere'
       END AS type_contrainte,
       pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
JOIN pg_class c ON c.oid = con.conrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND con.contype IN ('p', 'f')
ORDER BY c.relname, type_contrainte, contrainte;

Tu n'as pas besoin de mémoriser les noms pg_class, pg_namespace ou pg_constraint. Garde la requête comme outil. La compétence attendue est de lire la colonne definition, puis de vérifier ta jointure sur les deux tables concernées.

Le schéma dit qu'une relation est autorisée. Il ne dit pas combien de lignes la jointure produira ni si des données sont absentes. Après avoir choisi les clés, compare toujours la maille et le nombre de lignes avant et après la jointure.