PostgreSQL - Bludwarf/CodeBank GitHub Wiki
SEARCH_PATH
Attention à ne pas mettre de quotes dans la valeur du SEARCH_PATH
.
ALTER ROLE "mon-utilisateur" set SEARCH_PATH = "$user", "mon-schema", public;
Utilisation de tables temporaires (Common Table Expression)
ATTENTION : cela empêche les optimisations : https://medium.com/@hakibenita/be-careful-with-cte-in-postgresql-fca5e24d2119
WITH temp AS (
SELECT *
FROM table
)
SELECT *
FROM temp;
INSERT + SELECT
INSERT INTO items_ver(item_id, item_group, name)
SELECT *
FROM items
WHERE item_id = 2;
À partir d'une série nombre :
INSERT INTO target(id)
SELECT generate_series
FROM generate_series(1, 100); -- Série de nombre de 1 à 100
DELETE + JOIN
Avec une table :
DELETE
FROM deleted_table D
USING joined_table J
WHERE D.j_id = J.id;
Avec un LEFT OUTER JOIN
:
DELETE
FROM target
USING target AS target_alias
LEFT OUTER JOIN joined ON target_alias.uid_legal_case = joined.uid
WHERE target.uid = target_alias.uid
AND joined IS NULL;
Avec deux tables :
DELETE
FROM deleted_table D
USING joined_table J, joined_table2 J2
WHERE D.j_id = J.id AND D.j2_id = J2.id;
UPDATE + JOIN
UPDATE A
SET old_value = B.new_value
FROM B
WHERE A.id = B.id;
JOIN avec "table constante"
JOIN (VALUES
('dossier1', 1),
('dossier2', 2)
) AS selection(dossier, exercice) ON -- ...
Fonction fenêtrée
Tous les éléments de la partition
avg(salary) OVER (PARTITION BY depname)
Avec ORDER BY : tous les éléments déjà parcourus de la partition
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
Avec WHERE
rank() FILTER (WHERE depname IS NOT NULL) OVER (PARTITION BY depname ORDER BY salary DESC)
Lien : https://www.postgresql.org/docs/9.3/functions-window.html
-- Les 4 premières fonctions dépendent du ORDER BY : même valeur = pair => même résultat
-- row_number() :
-- Si on souhaite avoir la ligne sans ORDER BY : row_number() OVER (ORDER BY 1)
-- rank() :
-- dense_rank() :
-- percent_rank() :
-- Si on utilise ORDER BY : les fonctions d'aggrégation utilisent les résultats jusqu'à la ligne actuelle
-- Sinon : le résultat en prenant toute la partition
Pour déclarer une fenêtre :
SELECT count(1) OVER w,
FROM table_name
WINDOW w AS (PARTITION BY c1 ORDER BY c2);
NOT EXISTS
-- NOT EXISTS à la place de NOT IN : https://www.red-gate.com/hub/product-learning/sql-prompt/consider-using-not-exists-instead-not-subquery
-- On supprime des résultat à garder tout ce qui existe aussi dans à retirer
SELECT *
FROM garder
WHERE NOT EXISTS (
SELECT 1 -- Aucune colonne n'est utilisée pour le NOT EXISTS
FROM retirer
WHERE garder.id = retirer.id
)
Permissions, droits
GRANT SELECT, UPDATE, DELETE, INSERT
ON ALL TABLES
IN SCHEMA :schema
TO :user;
ALTER DEFAULT PRIVILEGES
IN SCHEMA :schema
GRANT SELECT, UPDATE, DELETE, INSERT
ON TABLES
TO :user;
WHERE avec dates
WHERE date <@ '[2023-08-10, 2023-08-18]'::daterange
WHERE age(date) < INTERVAL '1 day'
Connexions clientes
Afficher le nombre maximum de connexion autorisées :
SHOW max_connections
Afficher toutes les connexions actuelles sur la BDD :
SELECT *
FROM pg_stat_activity
-- Toutes les requêtes qui ont démarré il y a plus d'une minute
SELECT *
FROM pg_stat_activity
where now() - query_start > '1 mins'
ORDER BY query_start;
Supprimer des connexions :
SELECT PG_TERMINATE_BACKEND(pid)
FROM pg_stat_activity
WHERE pid IN (...)
Pour éviter de supprimer sa propre connexion, ajouter au WHERE :
pid <> pg_backend_pid()
Liste des rôles d'un utilisateur
WITH RECURSIVE cte AS (
SELECT oid FROM pg_roles WHERE rolname = 'mlavigne'
UNION ALL
SELECT m.roleid
FROM cte
JOIN pg_auth_members m ON m.member = cte.oid
)
SELECT oid, oid::regrole::text AS rolename FROM cte; -- oid & name
Strings
Saut de ligne :
E'\n'
Paramètres
-- Valeur littérale du paramètre
SELECT :param;
-- Entre simple quotes
SELECT :'param';
-- Entre double quotes
SELECT :"param";
-- Booléen
SELECT :{?param};
Logs
ALTER DATABASE "your_database_name"
SET log_statement = 'all';
Si ça ne marche pas, modifier directement le paramètre dans la conf (postgresql.conf).
Si ça ne marche toujours pas, on peut logger depuis Hibernate via Spring. Cf. Hibernate
Stats
https://www.cybertec-postgresql.com/en/postgresql-detecting-slow-queries-quickly/.
Taille d'une table
SELECT pg_size_pretty(pg_total_relation_size('"schema"."table"'));
bytea
Tableau d'octets. Représente les octets sous forme hexadécimale majuscule, préfixée par 0x
. Pour le convertir en varchar, remplacer le préfixe par \x
.