Cette page complète le tutoriel SQL de base avec ce qu'un informaticien utilise vite en vrai sur PostgreSQL : schémas, vues, index, transactions, séquences, identités, analyse de plans et quelques gestes d'administration courante.
Si vous venez d'Oracle, retenez surtout les différences de vocabulaire et de réflexes : PostgreSQL est plus direct, plus explicite sur les types, et beaucoup de besoins quotidiens passent par les vues système, EXPLAIN et les bons index plutôt que par des réflexes spécifiques Oracle.
-- Créer un schéma
CREATE SCHEMA compta;
-- Créer une table dans un schéma
CREATE TABLE compta.factures (
facture_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
client_id BIGINT NOT NULL,
date_facture DATE NOT NULL,
montant NUMERIC(12,2) NOT NULL,
statut VARCHAR(20) NOT NULL DEFAULT 'BROUILLON'
);
-- Lister les tables utilisateur
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
-- Voir la structure d'une table
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'compta'
AND table_name = 'factures'
ORDER BY ordinal_position;
En Oracle, on pense souvent USER_TABLES, ALL_TABLES ou DBA_TABLES. En PostgreSQL, le réflexe courant passe par information_schema et les vues pg_catalog.
-- Colonne identity moderne
CREATE TABLE produits (
produit_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
code VARCHAR(30) NOT NULL,
libelle TEXT NOT NULL
);
-- Séquence explicite
CREATE SEQUENCE seq_audit START 1 INCREMENT 1;
SELECT nextval('seq_audit');
SELECT currval('seq_audit');
-- Affecter une séquence à une colonne
CREATE TABLE audit_log (
audit_id BIGINT PRIMARY KEY DEFAULT nextval('seq_audit'),
message TEXT NOT NULL
);
Pour un profil Oracle : le parallèle mental est simple. Une SEQUENCE reste une séquence, mais les colonnes IDENTITY offrent un équivalent souvent plus moderne et plus lisible selon le cas.
-- Index classique CREATE INDEX idx_factures_client ON compta.factures (client_id); -- Index composite CREATE INDEX idx_factures_client_date ON compta.factures (client_id, date_facture); -- Index unique CREATE UNIQUE INDEX idx_produits_code ON produits (code); -- Index partiel CREATE INDEX idx_factures_ouvertes ON compta.factures (date_facture) WHERE statut = 'BROUILLON'; -- Index sur expression CREATE INDEX idx_clients_email_lower ON clients (LOWER(email));
PostgreSQL rend très utiles les index partiels et les index sur expression. Ce sont souvent des leviers simples et puissants quand on veut optimiser des requêtes réelles sans surindexer tout le schéma.
-- Vue simple
CREATE VIEW v_factures_ouvertes AS
SELECT facture_id, client_id, date_facture, montant
FROM compta.factures
WHERE statut = 'BROUILLON';
-- Vue matérialisée
CREATE MATERIALIZED VIEW mv_ca_mensuel AS
SELECT DATE_TRUNC('month', date_facture) AS mois,
SUM(montant) AS chiffre_affaires
FROM compta.factures
GROUP BY DATE_TRUNC('month', date_facture);
-- Rafraîchissement
REFRESH MATERIALIZED VIEW mv_ca_mensuel;
Le parallèle Oracle existe, mais le mode opératoire est différent. En PostgreSQL, pensez à expliciter le rafraîchissement des vues matérialisées dans vos procédures ou vos tâches planifiées.
BEGIN; UPDATE compta.factures SET statut = 'VALIDEE' WHERE facture_id = 1001; COMMIT; -- Annulation BEGIN; DELETE FROM compta.factures WHERE facture_id = 1002; ROLLBACK; -- Verrouillage de ligne BEGIN; SELECT * FROM compta.factures WHERE facture_id = 1001 FOR UPDATE; COMMIT;
Le principe est familier pour un utilisateur Oracle. La différence pratique est surtout dans la manière de diagnostiquer rapidement les blocages et de lire l'état des sessions à travers les vues PostgreSQL.
-- Plan théorique EXPLAIN SELECT * FROM compta.factures WHERE client_id = 42 ORDER BY date_facture DESC; -- Plan avec exécution réelle EXPLAIN ANALYZE SELECT * FROM compta.factures WHERE client_id = 42 ORDER BY date_facture DESC;
Si vous venez d'Oracle, gardez l'équivalent mental du plan d'exécution, mais habituez-vous à lire les noeuds PostgreSQL et les temps réels de EXPLAIN ANALYZE.
-- Taille d'une table
SELECT pg_size_pretty(pg_total_relation_size('compta.factures'));
-- Taille des bases
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS taille
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Activité en cours
SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE state <> 'idle';
-- Statistiques de table
SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
ORDER BY relname;
Pour un expert Oracle, ces vues remplacent vite une partie des réflexes issus des vues d'administration Oracle. Elles sont incontournables pour comprendre l'activité, la volumétrie et la pertinence des index.
-- Mettre à jour les statistiques ANALYZE compta.factures; -- Nettoyage standard VACUUM compta.factures; -- Nettoyage + statistiques VACUUM ANALYZE compta.factures;
C'est un point important quand on vient d'Oracle : PostgreSQL a ses propres mécanismes d'entretien. Il faut comprendre l'importance de VACUUM, de l'autovacuum et des statistiques pour garder de bons plans.
Oracle PostgreSQL
------------------------------------- ---------------------------------------------
USER_TABLES / ALL_TABLES / DBA_TABLES information_schema + pg_catalog
SEQUENCE.NEXTVAL nextval('ma_sequence')
IDENTITY plus récente GENERATED ... AS IDENTITY
PLAN_TABLE / EXPLAIN PLAN EXPLAIN / EXPLAIN ANALYZE
Vue matérialisée Oracle MATERIALIZED VIEW + REFRESH explicite
Hints fréquents peu de hints natifs, agir sur schéma et requêtes
TRUNC(date) DATE_TRUNC('...')
MERGE MERGE ou souvent INSERT ... ON CONFLICT
Réflexe administration Oracle pg_stat_activity, pg_stat_user_tables, pg_locks
Le plus grand changement n'est pas la syntaxe pure, mais la manière de diagnostiquer, mesurer et maintenir. En PostgreSQL, un bon usage des statistiques, des index et d'EXPLAIN ANALYZE est central.
Voir aussi le tutoriel PostgreSQL SQL, le tutoriel PostgreSQL PL/pgSQL, le tutoriel Oracle SQL et le tutoriel Oracle PL/SQL.