PostgreSQL

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

Pour ne rien faire en cas de conflit (ligne déjà existante), ajouter :

ON CONFLICT DO NOTHING

Dans ce cas, les logs afficheront le même résultat qu'une ligne soit ajoutée ou non (NOTHING).

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;

Source

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;

UPDATE jsonb

UPDATE A
set json = json || '{"attribute": "newValue"}'
where uuid IN ('1c0ea8fc-fb45-103f-8a26-9ddfe99d0dd2'); -- L'opérateur == semble poser problème

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

Opérateurs, rangetypes.

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()

Exemple :

wait_event_type	wait_event	state	Description
Client	ClientRead	idle in transaction	La requête s'est terminée, mais la transaction n'a pas encore été commitée
Lock	transactionid	active	La requête est bloquée

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

Source.

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};

Doc

Logs

ALTER DATABASE "your_database_name"
SET log_statement = 'all';

Source Doc

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.