julp

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== En CLI (psql) ======

En ligne de commande (psql) :
  * Liste des utilisateurs : \du
  * Liste des bases : \l
  * Liste des tables : \dt
  * Liste des extensions : \dx
  * Changer de base de données : \c <nom de la base>
  * Description d'une table (colonnes, triggers, index, etc) : \d <nom de la table>

====== Dates ======

Date du jour au format "français" :
<code sql>SELECT TO_CHAR(NOW(), 'DD-MM-YYYY');</code>
Pour l'inverse (format "français" vers PostgreSQL) :
<code sql>SELECT TO_DATE('21-05-1998', 'DD-MM-YYYY');</code>
(ou TO_TIMESTAMP pour un DATETIME)

====== Expressions régulières ======

* selon le standard SQL (correspondance sur toute la chaîne) : valeur [NOT] SIMILAR TO motif
  * fonctions : substring
* selon le standard POSIX (sans la contrainte précédente), syntaxe similaire à celle de nginx :
  * valeur ~ motif : correspondance sensible à la casse
  * valeur ~* motif : correspondance insensible à la casse
  * valeur !~ motif : non correspondance sensible à la casse
  * valeur !~* motif : non correspondance insensible à la casse
  * fonctions : substring, regexp_replace, regexp_matches, regexp_split_to_table
  * prise en charge des notations \uXXXX (UTF-16) et \UXXXXXXXX (UTF-32)
  * semblable à PCRE

====== Insensibilité à la casse ======

Déplacé sur [[http://www.julp.fr/blog/posts/4-sql-gestion-de-la-casse]]

====== Équivalents MySQL de INSERT IGNORE, ON DUPLICATE KEY UPDATE, etc ======

Déplacé et MàJ sur [[http://www.julp.fr/blog/posts/11-postgresql-equivalents-mysql-de-insert-ignore-et-insert-on-duplicate-key-update]]

====== Tableaux ======

Déterminer si un tableau contient un élément :
<code sql>-- 1 est-il parmi [1,3,5,7,9] ?
SELECT 1 = ANY(ARRAY[1,3,5,7,9]);
-- Option moins performante en "castant" notre élément en tableau :
-- (utile pour savoir si au moins une des valeurs du tableau A est commune au tableau B)
SELECT ARRAY[1] && ARRAY[1,3,5,7,9];</code>

Valeurs communes à deux tableaux (intersection) :
<code sql>CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
  SELECT ARRAY(SELECT UNNEST($1) INTERSECT SELECT UNNEST($2))
$$;</code>
Autre solution : installer le module complémentaire //intarray// pour disposer de l'opérateur %%''&''%%.

Différence de deux tableaux (obtenir les valeurs d'un tableau qui ne sont pas communes avec un second) :
<code sql>CREATE OR REPLACE FUNCTION array_diff(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
  SELECT ARRAY(SELECT UNNEST($1) EXCEPT SELECT UNNEST($2))
$$;</code>
Solution alternative : installer le module complémentaire //intarray// pour disposer de l'opérateur %%''-''%%.

====== PHP/PDO ======

  * Récupérer le dernier id (en (big)serial), deux méthodes :
    * Avec lastInsertId, implique de connaître le nom de la séquence (par défaut, elle est nommée telle que <nom table>_<nom colonne>_seq) :<code php>$dbh = new PDO(...);
$dbh->exec("INSERT INTO utilisateurs(login, mot_de_passe) VALUES('foo', SHA1('bar'))");
$lastId = $dbh->lastInsertId('utilisateurs_id_seq');</code>
    * Avec une requête INSERT ... RETURNING ... (incompatible avec les règles) :<code php>$dbh = new PDO(...);
$stmt = $dbh->query("INSERT INTO utilisateurs(login, mot_de_passe) VALUES('foo', SHA1('bar')) RETURNING id");
$lastId = $stmt->fetchColumn(); // fetchColumn est approprié (et plus direct) parce que seul l'id est retourné et nous intéresse</code>

====== Altérer la structure d'une table ======

  * Autoriser la valeur NULL pour une colonne précédemment en NOT NULL :<code sql>ALTER TABLE <nom de la table> ALTER COLUMN <nom de la colonne> DROP NOT NULL;</code>
  * L'inverse, ne pas autoriser NULL quand la colonne le permettait précédemment (attention : vos données actuelles ne doivent pas déjà avoir une valeur NULL pour cette colonne, il faudra d'abord corriger les données concernées) :<code sql>ALTER TABLE <nom de la table> ALTER COLUMN <nom de la colonne> SET NOT NULL;</code>
  * Supprimer une clé étrangère :<code sql>ALTER TABLE <nom de la table> DROP CONSTRAINT <nom de la contrainte correspondant à la clé étrangère (nom par défaut : <nom de la table>_<nom de la colonne>_fkey)>;</code>
  * Renommer une table :<code sql>ALTER TABLE <ancien nom de la table> RENAME TO <nouveau nom de la table>;</code>
  * Renommer une colonne :<code sql>ALTER TABLE <nom de la table> RENAME <ancien nom de la colonne> TO <nouveau nom de la colonne>;</code>
  * Supprimer un trigger :<code sql>-- supprimer le trigger en conservant la fonction exécutée
DROP TRIGGER <nom de la fonction>() ON <nom de la table à laquelle il est associé>;
-- supprimer la fonction et le(s) trigger(s) qui y font appel
DROP FUNCTION (IF EXISTS) <nom de la fonction>() CASCADE;
</code>

====== Mapper des valeurs en d'autres lors d'une requête ======

Plutôt que d'écrire des constructions comme celles-ci :
<code sql>SELECT CASE <in>
    WHEN 'FT' THEN 'Fiche Technique'
    WHEN 'PV' THEN 'Procès Verbal'
    WHEN 'NF' THEN 'Norme'
    ELSE 'Valeur par défaut'
END;</code>
Dans le cas particulier où les valeurs à remplacer sont des entiers d'une plage finie et limitée, on peut utiliser un tableau :
<code sql>SELECT COALESCE((ARRAY['Fiche Technique','Procès Verbal','Norme'])[<in>], 'Valeur par défaut');</code>
Attention : les indices de tableaux pour PostgreSQL commence à 1 et non 0 !
Pour les autres cas, autre solution, passer par un hstore :
<code sql>SELECT COALESCE($$"FT"=>"Fiche Technique","PV"=>"Procès Verbal","NF"=>"Norme"$$::hstore -> <in>, 'Valeur par défaut');</code>
Ou json :
<code sql>SELECT COALESCE($${"FT"=>"Fiche Technique","PV"=>"Procès Verbal","NF"=>"Norme"}$$::json ->> <in>, 'Valeur par défaut');</code>

====== Déclencheurs (triggers) ======

===== Limiter un trigger ON UPDATE à des colonnes particulières =====

Lors de la déclaration du trigger, il est possible, via la partie WHEN, de rendre conditionnel l'exécution d'un trigger. Ainsi, on peut ne le déclencher que lorsque une ou des colonnes spécifiques ont été modifiées. Exemple :
<code sql>CREATE TRIGGER foo_after_update_function AFTER UPDATE ON foo FOR EACH ROW WHEN(ROW(NEW.colonne1, NEW.colonne2, NEW.colonne3) IS DISTINCT FROM ROW(OLD.colonne1, OLD.colonne2, OLD.colonne3)) EXECUTE PROCEDURE foo_after_update_function();</code>
Ici le trigger foo_after_update_function ne sera déclenché que lorsqu'au moins une des colonnes colonne1, colonne2 ou colonne3 verra sa valeur modifiée.

===== Éviter les boucles infinies =====

Comment s'assurer qu'un trigger ne puisse pas être déclenché à partir d'un autre ? Il faut ajouter une condition (WHEN) à la définition du trigger qui invoque la fonction vérifiant si le nombre de triggers actuellement invoqués est nul (pg_trigger_depth() = 0). Exemple :
<code sql>CREATE TRIGGER ... [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON ... FOR EACH ROW WHEN (pg_trigger_depth() = 0) EXECUTE PROCEDURE ...();</code>
Note : la fonction pg_trigger_depth requiert une version >= 9.2

====== Analyse de requête (EXPLAIN) ======

Ne pas oublier de forcer PostgreSQL à utiliser les index via un préalable :
<code sql>SET enable_seqscan = false;</code>
Parce que dans certains cas, notamment s'il y a peu de données, il ne les utilisera pas car ils n'apportent rien.

====== Comparer directement des tuples entre eux ======

<code sql>... WHERE /*ROW*/(x, y) = ANY(VALUES (1,2),(3,4),(5,6));
-- plus simple :
... WHERE (x, y) IN((1,2),(3,4),(5,6));</code>
Ce qui revient à :
<code sql>... WHERE (x = 1 AND y = 2) OR (x = 3 AND y = 4) OR (x = 5 AND y = 6);</code>

====== Installation de PostgreSQL 9.2 sur FreeBSD ======

  * Installer postgresql :<code>cd /usr/ports/databases/postgresql92-server/
make config
make install clean</code>
  * Initialiser postgresql :<code>echo 'postgresql_enable="YES"' >> /etc/rc.conf
echo 'postgresql_initdb_flags="--encoding=utf-8 --locale=fr_FR.UTF-8 --lc-collate=C --text-search-config=french"' >> /etc/rc.conf
service postgresql initdb</code>
  * En écoute locale (via socket), uniquement pour des membres du groupe pgsql : éditer /usr/local/pgsql/data/postgresql.conf pour changer ces paramètres :<code># Écoute uniquement sur socket
listen_addresses = ''
# Propriété de la socket
unix_socket_group = 'pgsql'
unix_socket_permissions = 0660</code>
  * Premier démarrage de postgresql :<code>service postgresql start</code>
  * Facultatif, installer des modules complémentaires (tout le monde en héritera ici) :<code>cd /usr/ports/databases/postgresql92-contrib
make install clean

su -l pgsql -c "psql template1"
#CREATE EXTENSION hstore;
CREATE EXTENSION ltree;
\q</code>
  * Assigner un mot de passe au compte administrateur :<code>su -l pgsql -c "psql template1"
\password
\q</code>
  * Créer vos bases/comptes utilisateur (j'en crée un de suite mais il est parfaitement possible de le faire n'importe quand) :
    * Création d'un compte : ''%%su -l pgsql -c "createuser -PE julp"%%''
    * Création de sa base de données : ''%%su -l pgsql -c "createdb -O julp db_julp"%%''
    * Permettre à l'utilisateur système correspondant de pouvoir se connecter par :<code>pw groupmod pgsql -m julp</code>
  * Supprimer l'historique de pgsql (au cas où) <code>su -l pgsql -c "rm -P .psql_history"</code>
  * Restreindre les accès conformément à ce qui a été fait ci-dessus (local avec mot de passe, rejeter le reste) : modifier /usr/local/pgsql/data/pg_hba.conf tel que 
    - la ligne "local", remplacer "trust" par "md5"
    - aux lignes "host", remplacer "trust" par "reject"
  * Relancer Postgre :<code>service postgresql restart</code>

====== PL/pgSQL ======

===== Equivalent de LAST_INSERT_ID() (obtenir l'id de la dernière valeur insérée) =====

De ce côté là, ce ne sont pas les moyens qui manquent. Au choix :
  * on retrouve ''%%INSERT ... RETURNING id INTO STRICT <nom variable>;%%''
  * les variables de statut ''%%GET DIAGNOSTICS <nom variable> = RESULT_OID;%%''
  * la fonction ''%%LASTVAL(/*VOID*/)%%'' donne la dernière valeur attribuée pour la dernière séquence entrée en action pour la session courante (c'est l'équivalent le plus strict de LAST_INSERT_ID())
  * la fonction ''%%CURRVAL(<nom de la séquence>)%%'' donne la dernière valeur attribuée pour la séquence donnée pour la session courante

===== Exécuter du code d'un langage de procédures (PL/pgSQL ou autre) =====

C'est simple, il faut l'encapsuler dans un bloc DO. Exemple avec PL/pgSQL :
<code>
DO LANGUAGE 'plpgsql' $$
    DECLARE
        -- ...
    BEGIN
        -- ...
    END
$$
</code>

===== Énumérations (ENUM) =====

Chaque énumération, pour PostgreSQL, est un type à part entière que l'on déclare préalablement par une instruction :
<code>CREATE TYPE <nom> AS ENUM(<liste de valeurs>);</code>
Exemple :
<code>CREATE TYPE couleurs AS ENUM('rouge', 'bleu', 'vert');</code>

PostgreSQL >= 9.1 permet d'ajouter des valeurs. Exemple :
<code>ALTER TYPE couleurs ADD VALUE 'jaune';</code>
(on peut même spécifier où - avant/après une autre valeur)

Rien (encore ?) pour apporter facilement d'autres types de modifications (changer ou supprimer une valeur) aux énumérations.

Il est toutefois possible de renommer une valeur en passant par les tables système (requiert un compte PostgreSQL privilégié) :
<code sql>UPDATE pg_catalog.pg_enum SET enumlabel = '<nouveau nom de la valeur>' WHERE enumtypid = '<nom de l'enum>'::regtype::oid AND enumlabel = '<ancien nom de la valeur>';</code>

===== "Utilitaires" =====

==== Vider "toutes" ses tables ====

<code sql>DO $$
    BEGIN
        EXECUTE (
            SELECT 'TRUNCATE TABLE ' || string_agg(quote_ident(t.tablename), ', ') || ' RESTART IDENTITY CASCADE'
            FROM pg_tables t
            WHERE t.tableowner = current_user
            AND t.schemaname = 'public'
        );
    END;
$$
;</code>

==== Dropper toutes les fonctions utilisateur ====

<code sql>DO $$
    BEGIN
        EXECUTE (
            SELECT
                string_agg(
                    format('DROP FUNCTION %s(%s) CASCADE;', pg_proc.oid::regproc, pg_get_function_identity_arguments(pg_proc.oid)),
                    E'\n'
                )
            FROM
                pg_proc INNER JOIN pg_roles ON pg_proc.proowner = pg_roles.oid
            WHERE
                pg_roles.rolname = current_user
            AND
                pg_function_is_visible(pg_proc.oid)
        );
    END;
$$
;</code>
julp

Outils pour utilisateurs

Outils du site

Outils de la page
