Générer des données de test

      Pour tester une fonctionnalité il est souvent intéressant de pouvoir générer rapidement des données. Cet article donne quelques éléments pour le faire.

Générer des identifiants

      Il suffit de choisir le type SERIAL ou le type INTEGER avec la propriété GENERATED ALWAYS AS IDENTITY depuis PostgreSQL 10. Démonstration avec PostgreSQL 10 devel :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-12) 6.3.0 20170406, 64-bit (1 ligne) create table t1(c1 serial); CREATE TABLE insert into t1(c1) values(default); INSERT 0 1 insert into t1(c1) values(default); INSERT 0 1 select * from t1; c1 ---- 1 2 (2 lignes) create table t2(c2 integer generated always as identity); CREATE TABLE insert into t2(c2) values(default); INSERT 0 1 insert into t2(c2) values(default); INSERT 0 1 select * from t2; c2 ---- 1 2 (2 lignes)

Générer des identifiants universellement uniques

      Les séquences permettent de générer des identifiants pouvant servir de clés techniques. Si vous avez besoin d’identifiants universellement uniques il existe un type dédié, UUID. L’extension UUID_OSSP offre plusieurs fonctions pour générer ces identifiants. Démonstration :

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION select uuid_generate_v4(); uuid_generate_v4 -------------------------------------- af727305-b95c-4527-b6bf-00431d125de3 (1 ligne)

Créer une suite

      Afin de pouvoir boucler en SQL sans un langage comme PL/pgSQL, il est nécessaire de savoir générer des suites.
      Voici quelques commandes de génération d’une suite de nombres de 1 à 100, tout d’abord avec une fonction spécifique PostgreSQL, generate_series, puis avec des WITH QUERIES récursives :

select generate_series(1, 100, 1); 1 .. 100 (100 lignes) with recursive serie(i) as (select 1 UNION ALL select i + 1 from serie where i < 100) select i from serie; 1 .. 100 (100 lignes) with recursive serie(i) as (values(1) UNION ALL select i + 1 from serie where i < 100) select i from serie; 1 .. 100 (100 lignes)

Générer des données aléatoires

      Les données aléatoires sont très utilisées dans les environnements de test. Voici les commandes permettant de générer aléatoirement un nombre entre 0 et 1, une chaîne de caractères, une date dans le passé :

select random(); random ------------------- 0.305917965713888 (1 ligne) select md5(random()::text); md5 ---------------------------------- 23fd844da04de08b3c18c44eb6bc3d00 (1 ligne) select current_date - (ceil(random()*1000) || ' days')::interval; ?column? --------------------- 2016-10-10 00:00:00 (1 ligne)

Créer une table et la remplir

      Vous pouvez à présent combiner ces éléments. Exemple de création d’une table et d’insertion de données :

create table geants( idg serial, idgu uuid, dtn timestamp, taille smallint, devise varchar(128), berserk boolean); CREATE TABLE with recursive serie(i) as (select 550000 UNION ALL select i - 1 from serie where i > 100001) insert into geants(idgu, dtn, taille, devise, berserk) select uuid_generate_v4(), current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)), case when random() < 0.001 then true else false end from serie; INSERT 0 450000

      Utiliser une WITH QUERY avec INSERT est une extension PostgreSQL à la norme SQL. Vous pouvez aussi créer la table et la remplir en une seule commande avec un CREATE TABLE AS :

create table geants as with recursive serie(i) as (select 550000 UNION ALL select i - 1 from serie where i > 100001) select 550001 - i idg, uuid_generate_v4() idgu, current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval dtn, 200 + (trunc(random() * 200 + 1)) taille, upper(md5(random()::text)) devise, case when random() < 0.001 then true else false end berserk from serie; SELECT 450000

      Il est bien sûr possible d’optimiser, de varier à l’infini les jeux de test, de créer plusieurs tables etc.

Mise à jour : 19/04/2017