cookbook

Un livre de recette SQL pour PostgreSQL : nos trucs et astuces ! N'hésitez pas à enrichir ce livre avec vos propres astuces SQL...

aggregats et statistiques

Ah, les plaisirs des moving average et autres sommes intermédiaires...

Sommes intermédiaires

Un exemple qui nous viens tout droit du site http://www.udel.edu/evelyn/SQL-Class3/SQL3_Stat.html

Il s'agit de calculer pour chaque ligne de résultat la somme des éléments déjà affichés jusque là, sans pour autant modifier la requête d'une quelconque autre façon.

L'idée est d'utiliser une jointure sur la même table, et de limiter cette jointure aux tuples déjà pris en compte jusque-là. Vous aurez donc à adapter le code suivant à vos données, bien sûr, mais aussi à votre façon de limiter la somme intermédiaire :

SELECT a.OrderID, a.OrderDate, Sum(b.OrderTotal) AS RunningTotal
FROM RATTC_ForRunSum AS a, RATTC_ForRunSum AS b
WHERE b.OrderDate <= a.OrderDate
GROUP BY a.OrderID, a.OrderDate;

Tableau Accumulateur

Plutôt que de faire une somme, on peut avoir besoin de cumuler l'ensemble des valeurs différentes d'un GROUP BY dans un tableau. On peut à cet effet utiliser l'agrégat suivant :


CREATE AGGREGATE array_acc (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);

Il s'utilise tout simplement comme suit :


pgloader=# begin;
BEGIN
pgloader=# create table foo (a int, b text);
CREATE TABLE
pgloader=# insert into foo values(1, 'foo'), (1, 'bar'), (2, 'baz'), (1, 'zoinx');
INSERT 0 4
pgloader=# CREATE AGGREGATE array_acc (
pgloader(# BASETYPE = anyelement,
pgloader(# SFUNC = array_append,
pgloader(# STYPE = anyarray,
pgloader(# INITCOND = '{}'
pgloader(# );
CREATE AGGREGATE
pgloader=# select a, array_acc(b) from foo group by a;
a | array_acc
---+-----------------
2 | {baz}
1 | {foo,bar,zoinx}
(2 lignes)
pgloader=# rollback;
ROLLBACK
pgloader=# begin;
BEGIN
pgloader=# create table foo (a int, b text);
CREATE TABLE
pgloader=# insert into foo values(1, 'foo'), (1, 'bar'), (2, 'baz'), (1, 'zoinx');
INSERT 0 4
pgloader=# CREATE AGGREGATE array_acc (
pgloader(# BASETYPE = anyelement,
pgloader(# SFUNC = array_append,
pgloader(# STYPE = anyarray,
pgloader(# INITCOND = '{}'
pgloader(# );
CREATE AGGREGATE
pgloader=# select a, array_acc(b) from foo group by a;
a | array_acc
---+-----------------
2 | {baz}
1 | {foo,bar,zoinx}
(2 lignes)

pgloader=# rollback;
ROLLBACK

fonctions sur les dates

Un chapitre de notre livre de recettes entièrement consacré à la manipulation de dates.

Nombre de jours dans chaque mois

La requête suivante permet de connaître pour chaque mois d'une période donnée (ici de l'année courante) son nombre de jours.

select to_char(month, 'FMMonth YYYY') as month,

       date_part('day',month + '1 month - 1 day'::interval) as lastday

from (

  select (date_part('year', current_date) || '-' || m || '-01')::date as month

  from   generate_series(1, 12) m) months;

On obtient le résultat suivant :

     month      | lastday

----------------+---------

 January 2006   |      31

 February 2006  |      28

 March 2006     |      31

 April 2006     |      30

 May 2006       |      31

 June 2006      |      30

 July 2006      |      31

 August 2006    |      31

 September 2006 |      30

 October 2006   |      31

 November 2006  |      30

 December 2006  |      31

(12 rows)

Nombre de mois contenus entre deux dates

Cela se fait avec les deux fontions suivantes :

create function extract_months(interval) returns integer language sql

  as $f$

    select 12 * extract(year from $1)::integer

           + extract(month from $1)::integer

$f$;

create function months_between(date,date) returns integer language sql

  as $f$

    select extract_months(age($2,$1))

$f$;

Nombre de semaines entre deux dates quelconques

Cette fois-ci il s'agit donc de trouver le nombre de semaines entre deux dates quelconques. Ce qui rend la solution un poil compliquée, c'est que toutes les années n'ont pas le même nombre de semaines (52*7 ça donne que 364 jours).

On va donc faire ça en deux temps, d'abord trouver le nombre de semaines de chaque année faisant partie de l'intervalle, ensuite c'est facile.

Déjà, trouver les années comprises entre deux dates, ainsi que le premier janvier de l'année suivante :


dim# select year, to_date(year+1, 'YYYY-01-01') as d
 from generate_series(extract(year from (current_date - interval '4 years')::date)::int,
          extract(year from current_date)::int) as year;
 year | d
------+------------
 2003 | 01-01-2004
 2004 | 01-01-2005
 2005 | 01-01-2006
 2006 | 01-01-2007
 2007 | 01-01-2008
(5 lignes)

Maintenant, première ruse : si le premier janvier de l'année suivante est semaine 1, alors l'année donnée contient 52 semaines, sinon elle contient le nombre de semaine correspondant au numéro de semaine du premier janvier de l'année suivante :

dim=# select extract(year from d) - 1 as year,
dim-# case when extract(week from d) = 1
dim-# then extract(week from d - interval '1 week')
dim-# else extract(week from d) end as nb_weeks
dim-# from (
dim(# select year, to_date(year+1, 'YYYY-01-01') as d
dim(# from generate_series(extract(year from (current_date - interval '4 years')::date)::int,
dim(# extract(year from current_date)::int) as year) as x;
 year | nb_weeks
------+----------
 2003 | 52
 2004 | 53
 2005 | 52
 2006 | 52
 2007 | 52
(5 lignes)

Il reste à additionner toutes les semaines des années intermédiaires et à ajouter le numéro de semaine de l'année courante pour avoir le nombre de semaines depuis le premier janvier de l'année de la date de début. On enlèvera donc de cette somme le numéro de semaine de la date de début pour avoir notre résultat :

create or replace function weeks(timestamp, timestamp) returns int language sql
as $$
  select extract(week from $2)::int
  +
  coalesce(
   sum( case when extract(week from d) = 1
    then extract(week from d - interval '1 week')
    else extract(week from d) end ),
   0)::int
   -
   extract(week from $1)::int
  from (
   select year, to_date(year+1, 'YYYY-01-01') as d
   from generate_series(extract(year from $1)::int,
    extract(year from $2)::int - 1) as year
   ) as x;
$$;
 
dim=# select weeks(current_date - interval '18 months', current_date);
 weeks
-------
   79
(1 ligne)
 
dim=# select weeks(current_date - interval '4 years', current_date);
 weeks
-------
   209
(1 ligne)

Et voilà :)

Note : on utilise COALESCE() sur le SUM() afin de savoir compter le nombre de semaines entre deux dates de la même année aussi !

timestamp PostgreSQL et timestamp Unix

Pour convertir d'un timestamp PostgreSQL vers un timestamp Unix:

select extract(epoch from now())::bigint;

date_part
------------
1162822549
(1 ligne)


Pour convertir d'un timestamp Unix vers un timestamp PostgreSQL:


select to_timestamp(1162822549);

to_timestamp
------------------------
2006-11-06 15:15:49+01
(1 ligne)


Filtrer des tuples à intervalle temporel régulier

Conditions préalables


Imaginons une table contenant plusieurs colonnes dont une est une estampille. Par exemple la table mesures :

CREATE TABLE mesures (
     estampille TIMESTAMP WITH TIMEZONE PRIMARY KEY,
     valeur DOUBLE PRECISION);

Nous pouvons imaginer que les données sont temporellement réparties uniformément dans le temps (une donnée toutes les minutes par exemple)

Cas d'utilisation


On souhaite avoir les mesures entre deux dates en échantillonnant les données présentes dans la table mesures avec un intervalle régulier supérieur à la répartition initiale de la mesure (par exemple toutes les 5 minutes).

Requête


Nous allons utiliser la fonction EXTRACT avec comme argument EPOCH pour extraire le nombre de secondes écoulées depuis le 1er Janvier 1970 à minuit GMT sur l'estampille de chaque ligne. Nous pourrons ensuite utiliser la fonction modulo (%) pour fixer déterminer si la ligne est sélectionnable ou non.

SELECT 
 * 
FROM 
 mesures
WHERE
 estampille BETWEEN CURRENT_TIMESTAMP - '1 day'::INTERVAL AND CURRENT_TIMESTAMP
AND
 ( (EXTRACT(EPOCH FROM estampille)::INTEGER ) % (5 * 60) ) = 0;

Ainsi chaque fois que la date EPOCH sera un multiple de 5*60 secondes (5 minutes) le modulo sera égal à 0 et notre ligne sera sélectionnée.

fonctions système

Contrôler l'utilisation de HOT, à partir de la 8.3

Si comme moi vous avez prévu un fort besoin d'utiliser HOT sur vos tables (UPDATE fréquents sur des colonnes non indexées), vous aurez sûrement réglé un fillfactor largement inférieur à 100. Mais comment contrôler que HOT profite réellement de l'opportunité théorique ? La requête suivante permet de contrôler HOT:

SELECT schemaname, relname,
       n_tup_upd,n_tup_hot_upd,
       case when n_tup_upd > 0
            then ((n_tup_hot_upd::numeric/n_tup_upd::numeric)*100.0)::numeric(5,2) 
            else NULL
       end as hot_ratio

 FROM pg_stat_all_tables;

 schemaname | relname | n_tup_upd | n_tup_hot_upd | hot_ratio
------------+---------+-----------+---------------+-----------
 public     | table1  |         6 |             6 |    100.00
 public     | table2  |   2551200 |       2549474 |     99.93

Et si l'on souhaite ajouter à l'affichage le réglage actuel du fillfactor, c'est possible. Mais il faut se référer au catalogue système, où pg_class référence le schéma sous relnamespace qui est un OID de la table système pg_namespace, laquelle publie le nom du schéma sous la colonne nspname, retrouvée sous le nom schemaname dans les tables de stats...

SELECT t.schemaname, t.relname, c.reloptions, 
       t.n_tup_upd, t.n_tup_hot_upd, 
       case when n_tup_upd > 0 
            then ((n_tup_hot_upd::numeric/n_tup_upd::numeric)*100.0)::numeric(5,2)
            else NULL
        end as hot_ratio

 FROM pg_stat_all_tables t 
      join (pg_class c join pg_namespace n on c.relnamespace = n.oid) 
        on n.nspname = t.schemaname and c.relname = t.relname

 schemaname | relname |   reloptions    | n_tup_upd | n_tup_hot_upd | hot_ratio
------------+---------+-----------------+-----------+---------------+-----------
 public     | table1  | {fillfactor=50} |   1585920 |       1585246 |     99.96
 public     | table2  | {fillfactor=50} |   2504880 |       2503154 |     99.93

On dirait que les espoirs théoriques sont plutôt bien vérifiés en pratique... on peut donc continuer de suivre la documentation les yeux fermés^Wgrands ouverts ;) Merci PostgreSQL !

Rétablir la sauvegarde et la restauration

Bonjour,

La version 1.6.0 de pgadmin sous Windows ne permet pas d'utiliser par défaut les outils de sauvegarde et de restauration de PostgreSQL à savoir pg_dump.exe et pg_restore.exe. La solution à ce défaut est de rajouter dans la variable PATH de Windows le chemin d'accès vers le répertoire de pgadmin à savoir par défaut C:\Program Files\pgAdmin III\1.6. Après un redémarrage de votre machine, vous pourrez de nouveau sauvegarder et restaurer vos bases de données.

Merci.

Supprimer les fonctions à l'intérieur d'un schéma

Trouvé sur un des forums, cette astuce qui mérite de figurer dans le cookbook.

Pour supprimer toutes les fonctions d'un schéma, on peut utiliser la commande suivante, en shell :


psql -U MON_USER MA_BASE -t -P format=unaligned -c "\df MON_SCHEMA.*"|awk -F '|' '{if (NF > 0 ) print "drop function "$2"("$4") cascade;";}' | psql -U MON_USER MA_BASE


Le cascade permet de gérer la présence d'une fonction d'agrégation.

Il est utile pour le bon fonctionnement de la commande de mettre le mot de passe de l'utilisateur dans la variable d'environnement PGPASSWORD.

Merci à jxemo pour cette astuce et à Sparky pour la piste.

Taille des tables TOAST

La requête suivante permet d'afficher la taille des tables de TOAST de votre système, pour celles dont la taille est supérieure à 0 octets. On utilise une sous-requête dans la clause FROM (cf. queries-table-expressions) afin de récupérer les informations brutes voulues, puis on les met en forme dans le SELECT en utilisant entre autres pg_size_pretty.

SELECT a, n, pg_relation_size(t), pg_size_pretty(pg_relation_size(t))

FROM   (SELECT c.relname, c.reltoastrelid, d.relname

        FROM   pg_class c JOIN pg_class d ON c.reltoastrelid = d.oid

       ) as x(a, t, n)
WHERE  t > 0 and pg_relation_size(t) > 0
ORDER BY 3 DESC;

Et voici un exemple d'utilisation :

psql amarok < /home/dim/PostgreSQL/cookbook/size.toast.sql

     a      |       n       | pg_relation_size | pg_size_pretty

------------+---------------+------------------+----------------

 pg_rewrite | pg_toast_2618 |           139264 | 136 kB

(1 ligne)

Trouver facilement la taille d'une table, de ses index et son nombre de tuples

On a besoin parfois d'avoir rapidement la taille sur disque d'une table et le nombre d'enregistrements qu'elle contient. Pour cela, on peut utiliser la fonction pg_total_relation_size('nom de table') qui permet de rapatrier la taille totale de la table sur disque, index inclus. Si on ne veut pas connaître la taille prise par les index, il suffit d'utiliser la fonction pg_relation_size('nom de table'). La soustraction des deux permet de connaître la taille des index!

Tout cela nous permet de réaliser une vue fort sympathique pour l'administrateur de base de données. Attention à lancer un ANALYZE avant tout requêtage de cette vue:

drop view vue_stats;

create view vue_stats as
SELECT
c.relname as nom,
c.reltuples::bigint as tuples,
pg_total_relation_size(c.relname) as volume_total,
pg_relation_size(c.relname) as volume_donnees,
pg_total_relation_size(c.relname)-pg_relation_size(c.relname) as volume_index
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;


Merci à klando (Cédric) pour cet ajout, fort sympathique: il permet d'avoir les tailles des objets lisibles sous la forme humaine:


drop view vue_stats_pretty ;

create view vue_stats_pretty as
SELECT
nom,
tuples,
pg_size_pretty(volume_total) as volume_total,
pg_size_pretty(volume_donnees) as volume_donnees,
pg_size_pretty(volume_index) as volume_index
FROM vue_stats;


Enjoy!

--
Jean-Paul ARGUDO
http://dalibo.com | http://dalibo.org

trouver le nom de l'objet associé à un OID

Il est possible de trouver le nom de l'objet associé à un OID en passant par la conversion regclass. Par exemple, pour connaître le nom de l'objet dont l'OID est 2613 :

guillaume=# select 2613::regclass;

    regclass

----------------

 pg_largeobject

(1 ligne)

L'OID 2613 correspond donc à la table système pg_largeobject.

fonctions texte

diviser un texte en plusieurs lignes

Que faire si vous avez une chaîne, que vous voulez la séparer en plusieurs morceaux suivant un pattern et que vous voulez récupérer chaque morceau sur une ligne ?

Commencer par créer la fonction suivante :

CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT

AS $$

    SELECT (string_to_array($1, $2))[s.i]

    FROM generate_series(

        1,

        array_upper(string_to_array($1, $2), 1)

    ) AS s(i);

$$ language sql strict;

Pour l'utiliser, rien de plus simple :

dev=#select split_to_rows('aa--bb--cc','--');

split_to_rows

---------------

 aa

 bb

 cc

(3 rows)

Cette fonction a été écrite par David Fetter (voir son message sur pgsql-general).

misc

Lister les tables filles d'une table

On remplacera "element" par la table père, on pourra bien sûr récupérer les informations de la table pg_class (c) ou pg_inherits (i) en fonction des besoins...



SELECT relname

FROM pg_catalog.pg_class c,

     pg_catalog.pg_inherits i

WHERE i.inhparent=(select oid from pg_class where relname='element');

Modifier les droits d'un utilisateur sur toutes les tables d'un schéma.

Ci-dessous une requête qui permet de composer un script permettant d'affecter les droits SELECT sur toutes les tables du schéma public

select 'GRANT SELECT ON '||table_schema||'.'||table_name||' TO mon_user;'

from   information_schema.tables

where  table_type='BASE TABLE'

and    table_schema='public';

Enregistrer le contenu de cette requête dans un fichier et rejouer le script.

Cette requête peut être modifier pour le faire sur plusieurs schéma ou bien ajouter des droits supplémentaires.

Récupérer le résultat d'une requête éxécutée dans une procédure stockée

En php on peut utiliser directement une requête pour obtenir un recordset.
Cependant si l'on veux récupérer un recordset via une procedure stockée -- pour appliquer des traitements sur des paramètres avant d'exécuter la requête par exemple -- on peut créer une fonction équivalent à celle qui suit.

L'exemple renvoie un setof de la variable out nommée myset et qui est definie du type du champs convoité dans la table. (On utilise %TYPE pour recupérer le type du champs).

CREATE OR REPLACE FUNCTION ma_fonction(out myset ma_table.mon_champ_pk%TYPE)

    RETURNS setof integer AS

$BODY$

    DECLARE

    -- On crée une variable de type record , on peut aussi suivant le besoin créer une variable de type %ROWTYPE

     myrecord record;

BEGIN

    FOR myrecord in SELECT mon_pk FROM ma_table loop

        myset:= myrecord.mon_pk;

        RETURN NEXT;

    END LOOP;

    RETURN;

END;

$BODY$

    LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION ma_fonction(out myset demande.demande_pk%TYPE) OWNER TO mon_user;

Pour récupérer l'ensemble des resultat on selectionne la procedure ainsi:

SELECT * FROM  ma_fonction();

Les mots importants sont :
- setof
- record
- %TYPE et %ROWTYPE
- for...loop
- return next

Trouver le 1er élément disponible dans une liste de numériques

On veut parfois trouver le 1er élément disponible dans une liste. C'est souvent le cas quand la clé primaire d'une table est un nombre mais qu'il n'est pas soumis à une séquence.. Ou alors qu'on a des trous dans la séquence et qu'on veut les combler.

Soit la table nombres (a integer, [...]), la requête suivante retourne le 1er élement disponible:

explain

SELECT (x.a+1) AS id_disponible

FROM nombres x

     LEFT JOIN nombres y

     ON ((x.a + 1) = y.a)

WHERE y.a IS NULL

ORDER BY x.a LIMIT 1;

                                    QUERY PLAN

-----------------------------------------------------------------------------------

 Limit  (cost=0.00..1.58 rows=1 width=4)

   ->  Nested Loop Left Join  (cost=0.00..22.09 rows=14 width=4)

         Join Filter: (("outer".a + 1) = "inner".a)

         Filter: ("inner".a IS NULL)

         ->  Index Scan using id_a on nombres x  (cost=0.00..3.15 rows=14 width=4

         ->  Seq Scan on nombres y  (cost=0.00..1.14 rows=14 width=4)

(6 lignes)
test2=> SELECT (x.a+1) AS id_disponible FROM nombres x LEFT JOIN nombres

y ON ((x.a + 1) = y.a) WHERE y.a IS NULL ORDER BY x.a LIMIT 1;

 id_disponible

---------------

             4

(1 ligne)

Si vous constatez des lenteurs, il vous faudra créer un index sur a (si ce n'est pas déjà fait, ce dont je doute):

create unique index id_a on nombres(a);

Et un index fonctionnel comme suit:

create index id_a_suivant on nombres ((a+1));

Merci à John Hansen (appeljack) pour cette idée.