On peut depuis bien longtemps exporter des résultats de requête en CSV, soit avec COPY (SELECT ...) TO STDOUT CSV qui est une commande SQL, soit via la méta-commande \copy de psql qui appelle en sous-main COPY en gérant le flux de données côté client.

Mais il reste quelques situations non couvertes par cette fonctionnalité, et c’est pourquoi dans PostgreSQL 12, le CSV a été ajouté aux formats de sortie en général, c’est-à-dire qu’il est disponible pour toutes les commandes produisant des résultats tabulaires. On peut opter pour ce format avec la commande \pset format csv, ou en ligne de commande avec l’option --csv.

Concrètement, quels sont les cas où on peut en avoir besoin préférentiellement à \copy?

• quand on récupère les données via une méthode non supportée par COPY, par exemple un curseur: on peut faire fetch from c mais pas copy (fetch from c) to stdout csv.

• quand le résultat est produit par une méta-commande plutôt qu’une requête directe: \crosstabview, \l, \d etc…

Le CSV en remplacement du format unaligned (-A)

Jusqu’à présent, les besoins d’export simple au format tabulaire sont souvent couverts par le format “unaligned” et son séparateur de champ paramétrable “fieldsep” (option -F). Mais ce format a deux faiblesses:

• quand le séparateur se trouve dans les données, il n’y a aucun moyen de le distinguer (pas d’échappement).
• quand des retours à la ligne sont présents dans les champs, il n’y a pas de moyen de distinguer un saut de ligne intra-champ d’un séparateur d’enregistrement.

Exemple:

$ query="select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2"
$ psql -AtF',' -c "$query"
ab,cd,ef
gh

Le problème est qu’en partant de cette sortie, on ne sait plus où démarrent et finissent les champs ni combien d’enregistrements il y avait au départ. Alors qu’il y a un seul enregistrement et deux colonnes, cette sortie pourrait aussi bien représenter une seule colonne avec ab,cd,ef en valeur du premier enregistrement et gh pour le second.

Le “vrai” CSV règle ce problème:

$ psql  --csv -t -c "$query"
"ab,cd","ef
gh"

La règle qui fait toute la différence est que des guillemets sont ajoutés pour encadrer un champ dès qu’il contient le séparateur ou un saut de ligne, ou un guillemet (dans ce dernier cas les guillemets intra-champs sont doublés).

En principe, les scripts qui importent du CSV sont capables de relire ce contenu en restituant les valeurs de départ dans tous les cas, y compris les champs multi-lignes.

CSV en format intermédiaire

Le CSV peut aussi être utilisé comme intermédiaire pour produire au final un autre format, que psql ne gèrerait pas directement.

Il faut pour ça intercaler un script entre la sortie de psql et une commande de transformation de CSV vers cet autre format. Pour la partie lecture des données CSV, le script sera souvent simple parce que la plupart des languages de script sont dotés d’analyseurs CSV déjà faits, l’intérêt de ce format étant justement son ubiquité.

Voici par exemple un programme Perl csv-to-markdown qui transforme du CSV UTF-8 vers le format markdown tel qu’utilisé notamment par github, et destiné à produire du HTML. Il ne faudra probablement pas plus de code pour faire la même chose en Ruby, Python ou un language comparable, et aussi certainement guère plus d’effort pour gérer d’autres formats que markdown, ou encore personnaliser cette sortie.

#!/usr/bin/perl

use Text::CSV;
use open qw( :std :encoding(UTF-8) );

my $csv = Text::CSV->new({ binary => 1, eol => $/ });

sub do_format {
  s/&/&/g;
  s/</&lt;/g;
  s/>/&gt;/g;
  s/\n/<br>/g;
  s/\|/&#x7C;/g;
  return $_;
}

my $header = $csv->getline(STDIN);
for (@{$header}) {
  $_ = do_format($_);
}
print join ('|', @{$header}), "\n";
print join ('|', map { "---" } @{$header}), "\n";

while (my $row = $csv->getline(STDIN)) {
  my @contents = map { do_format($_) } @{$row};
  print join('|', @contents), "\n";
}

Et voici quelques possibilités d’appeler ce programme depuis psql pour lui faire sortir du format markdown:

\pset format csv

-- méthode 1 avec \g (par requête)

select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2
   \g |csv-to-markdown >/tmp/table1.md

-- méthode 2 avec \o (pour toutes les requêtes et métacommandes à suivre)

\o |csv-to-markdown >/tmp/table2.md
select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2;

-- revient à l'affichage normal (aligned) et à l'écran
\a \o

Si on a une seule requête à sortir, on peut aussi l’écrire en une seule ligne de commande.

$ psql  --csv -c "$query" | csv-to-markdown >/tmp/table3.md

Dans tous les cas ci-dessus, le résultat produit est:

col1|col2
---|---
ab,cd|ef<br>gh

où les deux champs sont bien interprétés comme ils doivent.