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>

Date du jour au format "français" :

SELECT TO_CHAR(NOW(), 'DD-MM-YYYY');

Pour l'inverse (format "français" vers PostgreSQL) :

SELECT TO_DATE('21-05-1998', 'DD-MM-YYYY');

(ou TO_TIMESTAMP pour un DATETIME)

* 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

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

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

Déterminer si un tableau contient un élément :

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

Valeurs communes à deux tableaux (intersection) :

CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
  SELECT ARRAY(SELECT UNNEST($1) INTERSECT SELECT UNNEST($2))
$$;

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

CREATE OR REPLACE FUNCTION array_diff(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
  SELECT ARRAY(SELECT UNNEST($1) EXCEPT SELECT UNNEST($2))
$$;

Solution alternative : installer le module complémentaire intarray pour disposer de l'opérateur ''-''.

• 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) :

    $dbh = new PDO(...);
    $dbh->exec("INSERT INTO utilisateurs(login, mot_de_passe) VALUES('foo', SHA1('bar'))");
    $lastId = $dbh->lastInsertId('utilisateurs_id_seq');

  • Avec une requête INSERT … RETURNING … (incompatible avec les règles) :

    $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

• Autoriser la valeur NULL pour une colonne précédemment en NOT NULL :

  ALTER TABLE <nom de la table> ALTER COLUMN <nom de la colonne> DROP NOT NULL;

• 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) :

  ALTER TABLE <nom de la table> ALTER COLUMN <nom de la colonne> SET NOT NULL;

• Supprimer une clé étrangère :

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

• Renommer une table :

  ALTER TABLE <ancien nom de la table> RENAME TO <nouveau nom de la table>;

• Renommer une colonne :

  ALTER TABLE <nom de la table> RENAME <ancien nom de la colonne> TO <nouveau nom de la colonne>;

• Supprimer un trigger :

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

Plutôt que d'écrire des constructions comme celles-ci :

SELECT CASE <in>
    WHEN 'FT' THEN 'Fiche Technique'
    WHEN 'PV' THEN 'Procès Verbal'
    WHEN 'NF' THEN 'Norme'
    ELSE 'Valeur par défaut'
END;

Dans le cas particulier où les valeurs à remplacer sont des entiers d'une plage finie et limitée, on peut utiliser un tableau :

SELECT COALESCE((ARRAY['Fiche Technique','Procès Verbal','Norme'])[<in>], 'Valeur par défaut');

Attention : les indices de tableaux pour PostgreSQL commence à 1 et non 0 ! Pour les autres cas, autre solution, passer par un hstore :

SELECT COALESCE($$"FT"=>"Fiche Technique","PV"=>"Procès Verbal","NF"=>"Norme"$$::hstore -> <in>, 'Valeur par défaut');

Ou json :

SELECT COALESCE($${"FT"=>"Fiche Technique","PV"=>"Procès Verbal","NF"=>"Norme"}$$::json ->> <in>, 'Valeur par défaut');

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 :

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

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.

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 :

CREATE TRIGGER ... [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON ... FOR EACH ROW WHEN (pg_trigger_depth() = 0) EXECUTE PROCEDURE ...();

Note : la fonction pg_trigger_depth requiert une version >= 9.2

Ne pas oublier de forcer PostgreSQL à utiliser les index via un préalable :

SET enable_seqscan = FALSE;

Parce que dans certains cas, notamment s'il y a peu de données, il ne les utilisera pas car ils n'apportent rien.

... WHERE /*ROW*/(x, y) = ANY(VALUES (1,2),(3,4),(5,6));
-- plus simple :
... WHERE (x, y) IN((1,2),(3,4),(5,6));

Ce qui revient à :

... WHERE (x = 1 AND y = 2) OR (x = 3 AND y = 4) OR (x = 5 AND y = 6);

• Installer postgresql :

  cd /usr/ports/databases/postgresql92-server/
  make config
  make install clean

• Initialiser postgresql :

  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

• En écoute locale (via socket), uniquement pour des membres du groupe pgsql : éditer /usr/local/pgsql/data/postgresql.conf pour changer ces paramètres :

  # Écoute uniquement sur socket
  listen_addresses = ''
  # Propriété de la socket
  unix_socket_group = 'pgsql'
  unix_socket_permissions = 0660

• Premier démarrage de postgresql :

  service postgresql start

• Facultatif, installer des modules complémentaires (tout le monde en héritera ici) :

  cd /usr/ports/databases/postgresql92-contrib
  make install clean
  
  su -l pgsql -c "psql template1"
  #CREATE EXTENSION hstore;
  CREATE EXTENSION ltree;
  \q

• Assigner un mot de passe au compte administrateur :

  su -l pgsql -c "psql template1"
  \password
  \q

• 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 :

    pw groupmod pgsql -m julp

• Supprimer l'historique de pgsql (au cas où)

  su -l pgsql -c "rm -P .psql_history"

• 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
  1. la ligne "local", remplacer "trust" par "md5"
  2. aux lignes "host", remplacer "trust" par "reject"
• Relancer Postgre :

  service postgresql restart

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

C'est simple, il faut l'encapsuler dans un bloc DO. Exemple avec PL/pgSQL :

DO LANGUAGE 'plpgsql' $$
    DECLARE
        -- ...
    BEGIN
        -- ...
    END
$$

Chaque énumération, pour PostgreSQL, est un type à part entière que l'on déclare préalablement par une instruction :

CREATE TYPE <nom> AS ENUM(<liste de valeurs>);

Exemple :

CREATE TYPE couleurs AS ENUM('rouge', 'bleu', 'vert');

PostgreSQL >= 9.1 permet d'ajouter des valeurs. Exemple :

ALTER TYPE couleurs ADD VALUE 'jaune';

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

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

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

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