ronBureau : PostgreSQL

Installation

Connexion

psql -U {username} -h {hostname} -d {database_name} -W

Utilisation de base

Créer, importer, et supprimer une base de donnée

-- Importer un fichier SQL (depuis psql)
\\i {database-path}

-- (Alternative depuis le shell)
-- psql -U {username} -d {database_name} -f {database-path}

-- Créer une base de données (Pas de OR REPLACE ni IF NOT EXISTS en PostgreSQL)
CREATE DATABASE {database_name};

-- supprimer (irrécupérable !!)
DROP DATABASE IF EXISTS {database_name};

Rechercher une base de données

-- Lister les bases (psql)
-- \\l [PATTERN]

-- En SQL (catalogue système)
SELECT datname FROM pg_database
[WHERE datname LIKE 'pattern'];

-- Avec expression régulière (PostgreSQL utilise l'opérateur ~ pour REGEXP)
SELECT datname FROM pg_database WHERE datname ~ '^prod_[0-9]{4}$';

Voir recherche conditionnelles

Ouvrir une base de données

-- Afficher les bases de données disponibles (psql)
\\l
-- Ouvrir/Se connecter à une base
\\c {database-name}

-- Vérifier la base actuellement sélectionnée
SELECT current_database();

-- Afficher toutes les tables du schéma courant (psql)
\\dt

-- Afficher la structure d'une table spécifique (psql)
\\d {table-name}
-- ou avec détails
\\d+ {table-name}

-- (Alternative SQL)
-- Lister les tables d'un schéma
-- SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'public';
-- Lister les colonnes d'une table
-- SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '{table-name}';

Recherche conditionnelles

WHERE

• AND OR ()

WHERE
    <condition 1> AND (<condition 2> OR <condition 3>)

• BETWEEN > < =

WHERE
    area BETWEEN 10000 
        AND 1000000
    AND population > 1000

• IN

WHERE
    country_code2 IN ('US','FR','JP')

• Expressions régulières (REGEXP / RLIKE) — POSIX ERE

-- PostgreSQL: ~ (sensible à la casse), ~* (insensible à la casse)
SELECT * FROM logs WHERE message ~ 'error|warning';
SELECT * FROM users WHERE username ~ '^[A-Za-z][A-Za-z0-9_]{2,15}$';
-- Variante insensible à la casse
-- SELECT * FROM logs WHERE message ~* 'error|warning';

Il est aussi possible utiliser LIKE pour les cas de base

Wildcard	Signification	Exemple
%	0 ou plusieurs caractères	WHERE nom LIKE 'Jean%' : trouve "Jean", "Jean-Luc"
_	Un seul caractère	WHERE code LIKE 'A_3' : trouve "AB3", "A13"
\	Échapper % ou _ comme littéraux	WHERE txt LIKE '%\_%' ESCAPE '\' : cherche un underscore dans txt

ORDER BY

ORDER by
    <sort_expression1> [asc | desc],
    <sort_expression2> [asc | desc],
    ...;    

Par défaut, PostgreSQL trie NULL comme des valeurs plus hautes que toute valeur non NULL (donc en ASC, NULL apparaît en dernier). Utilisez NULLS FIRST / NULLS LAST pour contrôler l'ordre.

SELECT FROM WHERE ORDER BY

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition
ORDER BY
    sort_expression;

Exemple concrète

-- SELECT data columns
SELECT
    name, 
    area, 
    region_id
-- FROM a table
FROM
    countries
-- WHERE those conditions are met
WHERE
    region_id = 2 OR 
    area > 2000000
-- and ORDER the results BY
ORDER BY
    name desc;

Ajout et édition des données

INSERT

-- Insertion simple (spécifier les colonnes)
INSERT INTO {table_name} (col1, col2, created_at)
VALUES ('val1', 'val2', now()) ;

-- Multi-lignes
INSERT INTO countries (code, name) VALUES
    ('FR','France'),
    ('JP','Japan')
RETURNING id, created_at;

RETURNING retourne la ligne créée. Il est optionnel.

-- INSERT ... SELECT (copier des résultats d'une requête)
INSERT INTO archived_logs (message, level, created_at)
SELECT message, level, created_at
FROM logs
WHERE created_at < now() - interval '30 days';

Astuce: utilisez RETURNING pour récupérer l'id créé, des timestamps, etc.

UPSERT (INSERT ... ON CONFLICT)

-- Nécessite une contrainte UNIQUE (ou index unique) sur la/les colonne(s) ciblée(s)
INSERT INTO users (username, email)
VALUES ('ron', 'new@example.org')
ON CONFLICT (username) DO UPDATE
SET email = EXCLUDED.email,
    updated_at = now();
-- Variante: ignorer le conflit
-- ON CONFLICT (username) DO NOTHING;

UPDATE

-- Mise à jour simple
UPDATE countries
SET area = area * 1.05
WHERE region_id = 2
RETURNING code, area;

-- UPDATE avec JOIN (via FROM)
UPDATE orders o
SET customer_name = c.name
FROM customers c
WHERE o.customer_id = c.id
  AND o.customer_name IS NULL;

Toujours vérifier le WHERE (testez d'abord la condition avec un SELECT).

DELETE

-- Supprimer des lignes (pensez au WHERE !)
DELETE FROM logs
WHERE level = 'DEBUG'
RETURNING id;

-- Vider rapidement une table (irrécupérable !!)
TRUNCATE TABLE temp_import RESTART IDENTITY;

Exemple : Transcations financières

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Vérifier, puis valider
COMMIT;
-- ou annuler
-- ROLLBACK;

Import CSV (psql)

-- Depuis le client psql (fichier lu depuis votre machine)
\\copy {schema}.{table}(col1, col2, ...) FROM '/chemin/data.csv' WITH (FORMAT csv, HEADER true)

-- En SQL côté serveur (fichier sur le serveur PostgreSQL)
-- COPY {schema}.{table}(col1, col2, ...) FROM '/chemin/data.csv' WITH (FORMAT csv, HEADER true);

Voir aussi: Recherche conditionnelles pour affiner vos WHERE avant d'écrire.