Vous appréciez mon travail ? Je serais ravi de prendre un café !

Vous prenez du plaisir à lire mes articles ? Vous apprenez de nouvelles choses ? Je serais ravis que vous supportiez mon travail avec une petite participation

1 café Merci, vous financez ma dose quotidienne de théïne (oui, en vrai je ne bois pas de café).
5 cafés Génial, ça couvre mes frais de serveur mensuels.
10 cafés Fantastique, avec ça je peux investir dans du matériel et approfondir mes connaissances.
BazinGa's - Tips & tuto IT

PostgreSQL – Vade-mecum SQL – 4/8 – Fonctions et opérateurs

Voici quelques fonctions et opérateurs à utiliser

Fonctions

Numériques

FonctionDescriptionExempleRésultat
x+yAddition1+12
x-ySoustraction1-10
x*yMultiplication2*36
x/yDivision6/32
x%y
ou mod(x,y)
Modulo (reste entier de la division)9%41
div(x,y)Diviseur entierdiv(11,5)2
x^y
ou power(x,y)
Puissance2^38
sqrt(x)Racine carréesqrt(16)4
cbrt(x)Racine cubiquecbrt(32)4
x!
ou !!5
Factoriel5!120
abs(x)Valeur absolueabs(-5)5
sign(x)Signe de la valeur (-1, 0, 1)abs(-5)-1
round(x,n)Arrondis (défaut n = 0) abs(2.435,1)2.4
ceil(x)Arrondi à l’entier supérieurceil(2.2)3
floor(x)Arrondi à l’entier inférieurfloor(2.8)2
trunc(x,n)Troncature (défaut n = 0)trunc(2.5375,2)2.53
pi()Pipi()3.14159265358979
degrees(x)Convertion randians vers degrésdegrees(3.1415)179.99
radian(x)Convertion degrés vers radiansradian(180)3.14159265358979
cos(x)Cosinus (angle en radian)cos(pi())-1
sin(x)Sinus (angle en radian)sin(pi())0
tan(x)Tangeante (angle en radian)tan(pi())0
cot(x)Cotangeante (angle en radian)cot(pi()/2)0

Textuels

FonctionDescriptionExempleRésultat
chr(n)Renvoi le caractère correspondant au codechr(190)'¾'
'chaine_1'||'chaine_2'
ou concat('chaine_1','chaine_2')
Concaténation'bonjour'||'test''bonjourtest'
concat('separateur','chaine_1','chaine_2')Concaténation avec séparateurconcat('/','valeur1','valeur2')'valeur1/valeur2'
char_length('chaine_1')
ou length('chaine_1')
Nombre de caractèreschar_length('chaine_1')8
lpad('chaine_1',n,'x')Remplissage par la gauche pour atteindre n caractère (défaut x = espace)lpad('chaine_1',10,'z')'zzchaine_1'
rpad('chaine_1',n,'x')Remplissage par la droite pour atteindre n caractère (défaut x = espace)rpad('chaine_1',10,'z')'chaine_1zz'
trim([leading | trailing | both] 'x' from 'chaine_1')Suppression par le début, la fin ou les deux du charactère x (défaut x = espace)trim(leading 'z' from 'zzzzchaine_1')'chaine_1'
ltrim('chaine_1', 'x')Suppression par le début du charactère x (défaut x = espace)ltrim('zzzxxzchaine_1', 'zx')'chaine_1'
rtrim('chaine_1', 'x')Suppression par la fin du charactère x (défaut x = espace)rtrim('chaine_1zzzxxz', 'zx')'chaine_1'
left('chaine_1', n)Renvoi les n premiers caractèresleft('chaine_1', 2)'ch'
right('chaine_1', n)Renvoi les n derniers caractèresright('chaine_1', 2)'_1'
substring('chaine_1', n, m)Renvoi m charactères à partir du n-ième (défaut m = infini)substring('chaine_1', 3, 4)'aine'
substring('chaine_1' from 'motif_regex')Renvoi les charactères correspondant au motif d’expression régulièresubstring('chaine_1' from '....$')'ne_1'
regexp_matches('chaine_1', 'motif_regex')Renvoi les charactères correspondant au motif d’expression régulière sous forme de tableau (il est donc possible de récupérer un groupe en particulier)regexp_matches('chaine_1', '....$')
ou
(regexp_matches('chaine_1', '(..)(..)$'))[2]
'ne_1'
ou
'_1'
strpos('chaine_1', 'x')
ou position('x' in 'chaine_1')
Renvoi la position de xstrpos('chaine_1', 'aine')3
lower('chaine_1')Met en minusculelower('ChAiNe_1')'chaine_1'
upper('chaine_1')Met en majusculeupper('ChAiNe_1')'CHAINE_1'
initcap('chaine_1')Met en majuscule la première lettre de chaque motinitcap('ChAiNe_1 chaInE_2')'Chaine_1 Chaine_2'
reverse('chaine_1')Inverse les caractèresreverse('chaine_1')'1_eniahc'
md5('chaine_1')Calcul le hash MD5md5('chaine_1')'d5ab9d67dcbc7429300a1716f203c33c'
repeat('chaine_1', n)Répète la chaine n foisrepeat('chaine_1', 3)'chaine_1chaine_1chaine_1'
overlay('chaine_1' placing 'xyz' from n for m)Remplace à partir du caractère n jusqu’au caratère m (défaut m = length(xyz))overlay('chaine_1' placing 'xyz' from 2 for 5)'cxyz_1'
replace('chaine_1', 'x', 'y')Remplace x par yreplace('chaine_1', 'ain', 'aaaaa')'chaaaaae_1'
regexp_replace('chaine_1', 'motif_regex', 'y')Remplace le motif d’expression régulière par yregexp_replace('chaine_1', '.$', 'aaaaa')'chaine_aaaaa'
translate('chaine_1', 'xyz', 'abc')Remplace xyz par abc (lettre à lettre)translate('Cet été je suis allé à la mer','àáâãäåèéêëìíîïòóôõöőùúûüűýÿœæçñ²','aaaaaaeeeeiiiioooooouuuuuyyoacn2')'Cet ete je suis alle a la mer'
split_part('chaine_1,chaine_2,chaine_3', 'delimiteur', n)Renvoi le n-ième objet de la liste délimitéesplit_part('chaine_1,chaine_2,chaine_3', ',', 2)'chaine_2'
regexp_split_to_array('chaine_1,chaine_2,chaine_3', 'motif_regex')Renvoi un tableau à partir de la liste délimitée selon les expressions régulièresregexp_split_to_array('chaine_1xxchaine_2yyychaine_3', 'x+|y+')'{chaine_1,chaine_2,chaine_3}'
regexp_split_to_table('chaine_1,chaine_2,chaine_3', 'motif_regex')Renvoi plusieurs lignes à partir de la liste délimitée selon les expressions régulièresregexp_split_to_table('chaine_1xxchaine_2yyychaine_3', 'x+|y+')'chaine_1'/'chaine_2'/'chaine_3' 3 lignes
quote_ident('chaine_1')Ajoute des guillemets pour obtenir un nom d’objet PostgreSQLquote_ident('chaine_1')'"chaine_1"'
quote_literal('chaine_1')Ajoute des guillemets pour obtenir une valeur textuellequote_ident(E'C\'est super')'C''est super'
coalesce('chaine_1','chaine_2')Renvoi la première chaine non nullecoalesce(NULL,NULL,'chaine_3')'chaine_3'

Informations serveur

Quelques fonctions supplémentaires

FonctionDescription
current_catalogNom de la BDD courante (appellée « catalog » dans le standard SQL)
current_database()Nom de la BDD courante
current_query()Requête en cours
current_schemaNom du schéma courant (premier schéma présent dans le search_path)
current_schemas(TRUE)Liste des schémas présent dans le sarch_path (TRUE pour afficher les schémas systèmes)
current_userNom du rôle utilisé
inet_client_addr()Adresse IP du client
inet_client_port()Port du client
inet_server_addr()Adresse IP du serveur
inet_server_port()Port du serveur
version()Version du serveur PostgreSQL
pg_backend_pid()Identifiant du processus utilisé par la session courante
pg_postmaster_start_time()Date et heure du démarrage du serveur PostgreSQL
pg_backend_pid()Identifiant du processus utilisé par la session courante

Date et heure

FonctionDescriptionExempleRésultat
current_time
ou localtime
Heure actuelle
current_dateDate actuelle
current_timestamp
ou localtimestamp
ou now()
Timestamp du début de la transaction
extract(format from timestamp)
ou date_part(format, timestamp)
Récupérer un élémentextract(hour from '2019-04-05 10:42:33')10
date_trunk(precision, timestamp)Tronque une date selon la precision indiquéedate_trunk(hour, '2019-04-05 10:42:33')2019-04-05 10:00:00
to_timestamp(timestamp_unix)Converti un timestamp unixto_timestamp(1284352323)2010-09-13 04:32:03+00
age(timestamp, timestamp)Interval entre deux timestampage('2019-04-05', '2019-04-10')5 days
make_date(annee, mois, jour)Créé une date à partir de trois valeurs numériquesmake_date(2019, 04, 05)2019-04-05
make_time(heure, minute, seconde)Créé une heure à partir de trois valeurs numériquesmake_time(14, 34, 22.5)14:34:22.5
make_timestamp(annee, mois, jour, heure, minute, seconde)Créé une date à partir de trois valeurs numériquesmake_date(2019, 04, 05, 14, 34, 22.5)2019-04-05 14:34:22.5

Pour les extractions, voici les formats possibles :

  • millenium : millénaire
  • century : siècle
  • decade : décénie
  • year : année
  • quarter : trimestre de l’année
  • month : mois
  • week : semaine depuis le début de l’année
  • doy : jour de l’année
  • day : jour du mois
  • dow : jour de la semaine (dimanche = 0 et samedi = 6)
  • isodow : jour de la semaine (lundi = 1 et dimanche = 7)
  • hour : heures
  • minute : minutes
  • second : secondes
  • millisecond : secondes en millisecondes
  • microsecond : secondes en microsecondes
  • epoch : timestamp unix – nombre de jours depuis le 1er janvier 1970 à 00:00:00
  • timezone_hour : écart par rapport à l’heure UTC en heure
  • timezone_minute : écart par rapport à l’heure UTC en minute
  • timezone : écart par rapport à l’heure UTC en seconde

Tableau (array)

Les tableaux sont un peu plus complexes que les autres types de données mais sont très puissants. On peut les comparer à des sortes de listes.

FonctionDescriptionExempleRésultat
array['x', 'y']Créer un tableauxarray['chaine_1', 'chaine_2']'{chaine_1,chaine_2}'
string_to_array('x', 'y', 'z')Créer un tableaux à partir d’une texte x délimité par y et définisant z comme une valeur nullestring_to_array('chaine_1,chaine2,chaine_3,chaine_4', ',', 'chaine_3')'{chaine_1,chaine_2,NULL,chaine_4}'
array_cat(tableau_1, tableau_2)
ou tableau_1||tableau_2
Concaténer des tableauxarray_cat(ARRAY['chaine_1','chaine_2'], ARRAY['chaine_3'])'{chaine_1,chaine_2,chaine_3}'
array_append(tableau_1, 'x')
ou tableau_1||'x'
Ajouter à la fin d’un tableauarray_append(ARRAY['chaine_1','chaine_2'], 'chaine_3')'{chaine_1,chaine_2,chaine_3}'
array_prepend(tableau_1, 'x')
ou 'x'||tableau_1
Ajouter au début d’un tableauarray_prepend(ARRAY['chaine_1','chaine_2'], 'chaine_3')'{chaine_3,chaine_1,chaine_2}'
array_remove(tableau_1, 'x')Retire toutes les occurences d’un élémentarray_remove(ARRAY['chaine_1','chaine_2','chaine_1','chaine_3','chaine_1'], 'chaine_1')'{chaine_2,chaine_3}'
array_replace(tableau_1, 'x', 'y')Remplace les occurences de x par yarray_replace(ARRAY['chaine_1','chaine_2','chaine_1','chaine_3','chaine_1'], 'chaine_1', 'chaine_4')'{chaine_4,chaine_2,chaine_4,chaine_3,chaine_4}'
array_to_string(tableau_1, 'x', 'y')Concaténation des éléments du tableau délimités par x et utilisant y pour les valeurs nullesarray_to_string(ARRAY['chaine_1','chaine_2',NULL,'chaine_3'], ',', 'nulle')'chaine_1,chaine_2,nulle,chaine_3'
unnest(tableau_1)Explose le tableau en plusieurs lignesunnest(ARRAY['chaine_1','chaine_2','chaine_3'])'chaine_1' | 'chaine_2' | 'chaine_3' 3 lignes
array_position(tableau_1, 'x')Position de la chaine dans le tableauarray_position(ARRAY['chaine_1','chaine_2'], 'chaine_1')1
array_positions(tableau_1, 'x')Position de la chaine dans le tableau (cas des positions multiples)array_position(ARRAY['chaine_1','chaine_2','chaine_1','chaine_3','chaine_1'], 'chaine_1'){1,3,5}
array_length(tableau_1)Longueur d’un tableauarray_length(ARRAY['chaine_1','chaine_2'])2
cardinality(tableau_1)Nombre d’élément dans un tableaucardinality(ARRAY[['chaine_1','chaine_2']['chaine_3','chaine_4']])4
tableau_1 @> tableau_2Tableau_1 contient tableau_2ARRAY['chaine_1','chaine_2','chaine_3'] @> ARRAY['chaine_1','chaine_2'])TRUE
tableau_1 <@ tableau_2Tableau_1 est contenu dans tableau_2ARRAY['chaine_1','chaine_2'] <@ ARRAY['chaine_1','chaine_2','chaine_3'])TRUE

XML

FonctionDescriptionExempleRésultat
xmlelement(name x)Créé un élément XMLxmlelement(name br)<br />
xmlelement(name x, xmlattributes('y' as z))Créé un élément XML avec un attributxmlelement(name img, xmlattributes('/media/image.jpg' as src))<img src="/media/image.jpg"/>
xmlelement(name x, 'y')Créé un élément XML avec du contenuxmlelement(name div, 'Chaine 1')<div>Chaine 1</div>
xmlcomment('x')Créé un commentaire XMLxmlcomment('chaine_1')<!--hello-->
xmlforest('w' as x, 'y' as z)Créé plusieurs élément XML avec leurs contenusxmlforest('Chaine 1' as div, 'Chaine 2' as span)<div>Chaine 1</div><span>Chaine 2</span>
xmlpi(name x, 'y')Créé une instruction XMLxmlpi(name php, 'echo "hello world";')<?php echo "hello world";?>

Voici un exemple de construction d’une page HTML :

SELECT
	xmlelement(
		name html,
		xmlcomment('Header'),
		xmlelement(
			name head,
			xmlelement(
				name title,
				'Titre de la page'
			),
			xmlelement(
				name link,
				xmlattributes(
					'css/mes_styles.css' as href,
					'stylesheet' as rel,
					'text/css' as type)
			)
		),
		xmlcomment('Corps'),
		xmlelement(
			name body,
			xmlelement(
				name div,
				xmlattributes(
					'titre' as class
				),
				'Mon titre'
			),
			xmlelement(
				name div,
				xmlattributes(
					'objet' as class
				),
				'Voici une petite phrase'
			)
		)
	)

Qui donnera le résultat suivant :

<html>
	<!--Header-->
	<head>
		<title>Titre de la page</title>
		<link href="css/mes_styles.css" rel="stylesheet" type="text/css"/>
	</head>
	<!--Corps-->
	<body>
		<div class="titre">Mon titre</div>
		<div class="objet">Voici une petite phrase</div>
	</body>
</html>

Json

Le JSON est un format JavaScript d’échange de donnée.
Voici le script JSON sur lequel est basé les exemples :

{
	"a": "chaine_1",
	"b": "chaine_2",
	"c": {
		"1": "chaine_3",
		"2": "chaine_4"
	}
}
FonctionDescriptionExempleRésultat
json_1 -> 'clé'Récupérer la valeur d’une clé au format jsonex_json -> 'a'"chaine_1"
json_1 ->> 'clé'Récupérer la valeur d’une clé au format texteex_json ->> 'a''chaine_1'
jsonb_set(json_1, 'chemin', 'valeur_json')Remplace la valeur désignée dans le chemin (si le chemin n’existe pas, la valeur est ajoutée)jsonb_set(ex_json, '{c,2}', '"nouvelle valeur"'){"a": "chaine_1", "b": "chaine_2", "c": {"1": "chaine_3", "2": "nouvelle valeur"}}
jsonb_insert(json_1, 'chemin', 'valeur_json')Remplace la valeur désignée dans le cheminjsonb_insert(ex_json, '{c,4}', '"nouvelle valeur"'){"a": "chaine_1", "b": "chaine_2", "c": {"1": "chaine_3", "2": "chaine_4", "4": "nouvelle valeur"}}
json_1 #> 'chemin'Récupérer la valeur depuis un chemin au format jsonex_json #> '{c,2}'"chaine_3"
json_1 #>> 'chemin'Récupérer la valeur depuis un chemin au format texteex_json #>> '{c,2}''chaine_3'
json_1 @> json_2Le json_1 contient-il le json_2 ?ex_json @> '{"b":"chaine_2"}'TRUE
json_1 <@ json_2Le json_2 contient-il le json_1 ?'{"b":"chaine_2"}' <@ ex_jsonTRUE
json_1 ? 'clé'La clé existe-t-elle ?ex_json ? 'c'TRUE
json_1 ?| ARRAY['clé_1', 'clé_2']L’une de ces clés existent-elles ?ex_json ? ARRAY['a', 'c']TRUE
json_1 - 'clé'Supprimer un élémentex_json - 'c'{"a": "chaine_1", "b": "chaine_2"}
json_1 #- 'chemin'Supprimer un élément à partir d’un cheminex_json #- '{c,2}'{"a": "chaine_1", "b": "chaine_2", "c": {"1": "chaine_3"}}
jsonb_pretty(json_1)Affiche le json sous forme de texte indentéjsonb_pretty(ex_json){
"a": "chaine_1",
"b": "chaine_2",
"c": {
"1": "chaine_3",
"2": "chaine_4"
}
}

Données binaires et large object

Les données binaires et les objets larges sont assez complexes à manipuler.
Je vous invite à consulter cette page pour en apprendre plus : cliquez-ici

Large object

Les larges objects sont stockés directement dans les répertoires de PostgreSQL. Deux tables permettent de stocker ces données :

pg_largeobject_metadata : qui possède une ligne par objet large.

CREATE TABLE pg_catalog.pg_largeobject_metadata (
	oid oid,			-- identifiant du large object
	lomowner oid,			-- propriétaire du large object
	lomacl aclitem[]		-- droits du large object
)

pg_largeobject : qui possède plusieurs lignes par objet large.

CREATE TABLE pg_catalog.pg_largeobject (
	loid oid,			-- identifiant du large object
	pageno int4,			-- numéro de page de stockage
	"data" bytea			-- Données
);

TOAST

En parallèle des larges object, il y a les tables TOAST ( The Oversized-Attribute Storage Technique : technique de stockage des attributs trop grands).

En gros, le stockage des données dans PostgreSQL s’effectue dans des pages (conteneur de données) d’une taille de 8 ko maximum. Une ligne ne peut être stockée que sur une seule page.
Ainsi, si la taille d’une ligne dépasse 8ko (dans le cas ou une grande valeur valeur serait présente dans un champs), alors on utilise TOAST.

Cette technique (affectueusement surnommée comme « the best thing since sliced bread ») consiste à compresser et/ou diviser les valeurs volumineuses en plusieurs lignes. Ceci se fait de façon totalement transparente pour l’utilisateur.

Le stockage binaire impliquera très certainement la technique TOAST car les données stockées dans les champs au format bianire peuvent avoir une taille importante.

Fonctions

FonctionDescription
decode('x', format)Converti un texte au format binaire selon le format spécifié : base64, hex, escape
encode('x', format)Traduit un format binaire en un texte lisible selon le format spécifié : base64, hex, escape
lo_create(0)Création d’un large object vide (présent dans pg_largeobject_metadata mais pas dans pg_largeobject), l’oid de l’objet est renvoyé
lo_import('chemin/vers/fichier.ext')Import d’un large object, l’oid de l’objet est renvoyé (attention, le fichier doit être situé sur le serveur)
lo_from_bytea(oid, donnees_binaire)Créer un large object avec les données binaire fournies (oid = 0 pour que le système choisisse l’oid)
lo_export(oid, 'chemin/vers/fichier_exporte.ext')Export du large object n°oid (attention, le fichier sera créé sur le serveur)
lo_unlink(oid)Suppression du large object
lo_get(oid)Récupérer les données du large object (au format binaire)

Il est possible de créer cette fonction pour importer des données dans une colonnes au format binaire :

CREATE OR REPLACE FUNCTION bytea_import(param_chemin text) 
RETURNS bytea AS
$corps$
	DECLARE
		-- oid du large object importé
		v_oid_objet oid;
		-- Données du fichier au format binaire
		v_donnees;
	BEGIN
		-- Import du fichier sous forme de large objet
		SELECT lo_import(param_chemin) INTO v_oid_objet;
		-- Récupération des données du large objet
		SELECT lo_get(v_oid_objet) INTO v_donnees;
		-- Suppression du large objet
		PERFORM lo_unlink(v_oid_objet);

		-- Renvoi des données
		RETURN v_donnees;
	END;
$corps$
LANGUAGE PLPGSQL
;

La fonction lo_get() n’existe pas avant pg 9.4. Il est possible d’utiliser cette fonction :

CREATE OR REPLACE FUNCTION bytea_import(param_chemin text) 
RETURNS bytea AS
$corps$
	DECLARE
		-- oid du large object importé
		v_oid_objet oid;
		-- Boucle sur l'objet large découpé
		v_morceau_objet record;
		-- Données du fichier au format binaire
		v_donnees;
	BEGIN
		-- Import du fichier sous forme de large objet
		SELECT lo_import(param_chemin) INTO v_oid_objet;

		-- Boucle pour récupérer toutes les données du large object
		FOR v_morceau_objet IN (
			SELECT data
			FROM pg_largeobject 
			WHERE loid = v_oid_objet 
			ORDER BY pageno )
		LOOP
			v_donnees = v_donnees || v_morceau_objet.data;
		END LOOP;

		-- Suppression du large objet
		PERFORM lo_unlink(v_oid_objet);

		-- Renvoi des données
		RETURN v_donnees;
	END;
$corps$
LANGUAGE PLPGSQL
;

La fonction d’import s’utilise ensuite comme suivant :

INSERT INTO ma_table (champ_donnees_binaire)
	SELECT bytea_import('chemin/vers/mon_fichier.ext')
;

Formatage

Le formatage des données permet de modifier la représentation de la données. Le principe est simple, on fournit une valeur ainsi qu’un motif correspondant.

FonctionDescription
to_char(timestamp, motif)Convertir un timestamp en texte
to_char(numeric, motif)Convertir un numérique en texte
to_date(text, motif)Convertir un texte en date

Motifs de date

CCSiècle (2 chiffres)
YYYYAnnées (4 chiffres)
YYAnnées (2 derniers chiffres)
MONTHMois en toute lettre (en majuscule complété avec des espaces à 9 caractères)
MonthMois en toute lettre (première lettre en majuscule complété avec des espaces à 9 caractères)
monthMois en toute lettre (en minuscule complété avec des espaces à 9 caractères)
MONMois en abrégé (en majuscule)
MonMois en abrégé (première lettre en majuscule)
monMois en abrégé (en minuscule)
MMMois en numérique (01-12)
WWNuméro de semaine (1-53) (la première semaine démarre le premier jour de l’année)
IWNuméro de semaine ISO (1-53) (le premier jeudi de l’année est semaine 1)
DAYJour en toute lettre (en majuscule complété avec des espaces à 9 caractères)
DayJour en toute lettre (première lettre en majuscule complété avec des espaces à 9 caractères)
dayJour en toute lettre (en minuscule complété avec des espaces à 9 caractères)
DYJour en abrégé (en majuscule)
DyJour en abrégé (première lettre en majuscule)
dyJour en abrégé (en minuscule)
DDDJour de l’année (001-366)
DDJour du mois (01-31)
DJour de la semaine (dimanche = 1, samedi = 7)
IDJour de la semaine (lundi = 1, dimanche = 7)
HH
ou HH12
Heure (01-12)
HH24Heure (00-23)
MIMinute (00-59)
SSSeconde (00-59)
MSMilliseconde (000-999)
USMicroseconds (000000-999999)
SSSSSeconde depuis minuit (0-86399)
TZFuseau horaire en abrégé (en majuscule)
tzFuseau horaire en abrégé (en minuscule)
OFÉcart du fuseau horaire avec UTC
FM Supprimer les espaces supplémentaires
TM Utiliser les noms locaux (au lieu de l’anglais). Utilise la local de la BDD.

Quelques exemples :

ExempleRésultat
to_char(current_timestamp, 'HH24:MI:SS')16:23:09
to_char(current_timestamp, 'DD Mon YYYY')25 Oct 2019
to_char(current_timestamp, 'HH24hMI TZ(OF)')16h26 CEST(+02)
to_char(current_timestamp, 'TMDay DD TMMonth')Vendredi 25 Octobre
to_timestamp('21h43min 12,52s', 'HH24.MI....SS,MS')0001-01-01 21:43:12.52+00:09:21 BC
to_timestamp('18:22:15.070.004377', 'HH24:MI:SS.MS.US')0001-01-01 18:22:15.074377+00:09:21 BC

Motifs numériques

9Emplacement d’un chiffre (non utilisé si inutile)
0Emplacement d’un chiffre (toujours utilisé même si inutile)
.Séprateur des décimales
DSéprateur des décimales selon la locale
,Séprateur des milliers
GSéprateur des milliers selon la locale
MISigne négatif (si le nombre est < 0)
PLSigne positif (si le nombre est > 0)
SGSigne (positif ou négatif)
RNChiffre romain (de 1 à 3999)
EEEENotation scientifique
FMSupprimer les espaces supplémentaires

Quelques exemples provenant de la documentation officielle de PostgreSQL (disponible ici) :

ExempleRésultat
to_char(-0.1, '99.99')' -.10'
to_char(-0.1, 'FM9.99')'-.1'
to_char(-0.1, 'FM90.99')'-0.1'
to_char(0.1, '0.9')' 0.1'
to_char(12, '9990999.9')' 0012.0'
to_char(12, 'FM9990999.9')'0012.'
to_char(485, '999') ' 485'
to_char(-485, '999')'-485'
to_char(485, '9 9 9')' 4 8 5'
to_char(1485, '9,999')' 1,485'
to_char(1485, '9G999')' 1 485'
to_char(148.5, '999.999')' 148.500'
to_char(148.5, 'FM999.999')'148.5'
to_char(148.5, 'FM999.990')'148.500'
to_char(148.5, '999D999')' 148,500'
to_char(3148.5, '9G999D999')' 3 148,500'
to_char(-485, '999MI')'485-'
to_char(485, '999MI')'485 '
to_char(485, 'FM999MI')'485'
to_char(485, 'PL999')'+485'
to_char(485, 'SG999')'+485'
to_char(-485, 'SG999')'-485'
to_char(-485, '9SG99')'4-85'
to_char(485, 'RN')' CDLXXXV'
to_char(485, 'FMRN')'CDLXXXV'
to_char(5.2, 'FMRN')'V'
to_char(482, '999th')' 482nd'
to_char(485, '"Good number:"999')'Good number: 485'
to_char(485.8, '"Pre:"999" Post:" .999')'Pre: 485 Post: .800'
to_char(0.0004859, '9.99EEEE')' 4.86e-04'

Fonction d’agrégation

Ces fonctions permettent d’aggréger les données : à partir de plusieurs lignes on obtient une seule ligne. On utilisera notamment ces fonctions avec la clause GROUP BY.

FonctionDescription
count(*)Compte le nombre de ligne
count(colonne_1)Compte le nombre de valeurs non nulles
sum(colonne_1)Renvoi la somme
avg(colonne_1)Calcule la moyenne arithmétique
var_samp(colonne_1)Calcule la variance
max(colonne_1)Renvoi la valeur maximum
min(colonne_1)Renvoi la valeur minimum
bool_and(colonne_1)Renvoi TRUE si toutes les valeur sont TRUE
bool_or(colonne_1)Renvoi TRUE si au moins une valeur est TRUE
string_agg(colonne_1, 'delimiteur' ORDER BY colonne_2)Concatène les valeurs en utilisant un délimiteur et en triant selon colonne_2
array_agg(colonne_1 ORDER BY colonne_2)Concatène les valeurs dans un tableau
json_agg(colonne_1 ORDER BY colonne_2)Concatène les valeurs dans un json

Fonction de fenêtrage

Ces fonctions permettent d’extraire des informations des données qui ont été aggrégées via du fenêtrage (voir par là).

Attention, ces fonction tiennens compte de l’ordre des lignes qui peut être définie par la clause ORDER BY de la fenêtre.

FonctionDescription
row_number()Numéro de ligne
rank()Rang de l’objet avec trou.
Comme row_number() mais deux objets peuvent avoir le même rang.
Par exemple : 1, 2, 2, 2, 5, 6, 7, 7, 9
dense_rank()Rang de l’objet sans trou.
Comme rank() mais si plusieurs objets ont le même rang, l’objet d’après possède le rang suivant.
Par exemple : 1, 2, 2, 2, 3, 4, 5, 5, 6
percent_rank()Rang en pourcentage (de 0% à 100%) : (rang -1) / (nb_ligne_total – 1).
Par exemple (pour 4 valeurs) : 0%, 33%, 66%, 100%
cume_dist()Rang en pourcentage (de n% à 100%) : (nb_lignes_précédentes + nb_ligne_même_rang + 1) / (nb_ligne_total).
Par exemple (pour 4 valeurs) : 25%, 50%, 75%, 100%
first_value(colonne_1)Première valeur issue de la fenêtre pour la colonne indiquée.
last_value(colonne_1)Dernière valeur issue de la fenêtre pour la colonne indiquée.
nth_value(colonne_1,n)Valeur ‘n’ issue de la fenêtre pour la colonne indiquée.
lag(colonne_1,n,'text')Valeur issue de la fenêtre pour la colonne indiquée située n lignes (defaut = 1) avant la ligne évaluée.
Si aucune valeur n’existe, ‘text’ (défaut = NULL) est renvoyée.
lead(colonne_1,n,'text')Valeur issue de la fenêtre pour la colonne indiquée située n lignes (defaut = 1) après la ligne évaluée.
Si aucune valeur n’existe, ‘text’ (défaut = NULL) est renvoyée.

Opérateurs

Les clauses WHERE permettent de filtrer les données. Pour cela on utilise des opérateurs de différents types. Le but est au final d’obtenir une réponse à la question « faut-il utiliser cette ligne ? ». La réponse attendu est donc de type booléenne : « oui » : TRUE ou « non » : FALSE.

Ainsi, la question posée utilisera un opérateur de comparaison pour évaluer si la réponse est vrai ou fausse.
Il est également possible de poser plusieurs questions d’un seul coup. Les différentes questions seront évaluées entre-elles grâce aux opérateurs logiques.

Opérateur de comparaison

Voici la liste des différents opérateur et prédicat de comparaison que vous pouvez utiliser (un prédicat est un peu comme un opérateur).

a et b peuvent être des valeurs, des colonnes ou des expressions (des calculs).

x et y doivent être des nombres.

Opérateur/prédicatDescription
a < ba inférieur à b
a > ba supérieur à b
a <= ba inférieur ou égal à b
a >= ba supérieur ou égal à b
a = ba égal à b
a <> b ou a != ba différent de b
a BETWEEN x AND ya entre x et y (avec x < y)
a BETWEEN SYMMETRIC x AND y a entre x et y (avec x < y ou x > y, peu importe)
a NOT BETWEEN x AND ya n’est pas entre x et y (avec x < y)
a NOT BETWEEN SYMMETRIC x AND y a n’est pas entre x et y (avec x < y ou x > y, peu importe)
a IS DISTINCT FROM ba est différent de b. Les valeurs null sont considérées comme des valeurs ordinaires
a IS NOT DISTINCT FROM ba égal b. Les valeurs null sont considérées comme des valeurs ordinaires
a LIKE ba égal b. LIKE permet d’utiliser des expressions régulières.
a NOT LIKE b a est différent de b. LIKE permet d’utiliser des expressions régulières.
a IS NULLa est null
a IS NOT NULLa n’est pas null
a IS TRUEa est vrai
a IS NOT TRUEa est faux ou inconnu
a IS FALSEa est faux
a IS NOT FALSEa est vrai ou inconnu
a IS UNKNOWNa est inconnu
a IS NOT UNKNOWNa est vrai ou faux
a IN (expression)a appartient aux valeurs contenues dans expression
a NOT IN (expression) a n’appartient pas aux valeurs contenues dans expression
a operateur ANY (expression)Compare a à chaque résultat de l’expression en utilisant l’opérateur mentionné, renvoi vrai si une comparaison est vraie
a operateur ALL (expression) Compare a à chaque résultat de l’expression en utilisant l’opérateur mentionné, renvoi vrai si toutes les comparaisons sont vraies
EXISTS (expression)L’expression renvoi une ligne (même avec la valeur null).
NOT EXISTS (expression) L’expression ne renvoi aucune ligne.

Opérateur logiques

Ces opérateur permettent d’articuler les différentes questions entres elles :

  • a AND b : a et b
  • a OR b : a ou b
  • NOT a : pas a

Voici le tableau de résultat

aba AND ba OR b
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSEFALSEFALSEFALSE
FALSENULLFALSENULL
NULLNULLNULLNULL

Les expressions régulières

Pour faire des comparaisons de valeur, vous pouvez utiliser les expressions régulières. Le but de ces expressions est de représenter un ensemble de caractère au moyen d’un motif. Par exemple :

  • « abc » pour « abc »
  • « abc+ » pour « abc », « abcc », « abccc » …

Voici une liste d’opérateur avec quelques exemples (largement inspiré de l’article de Wikipédia) :

OpérateurDescriptionExempleValeur correspondanteValeur non correspondante
. N’importe quel caractère unique . a, b ab,null
expr? 0 ou 1 fois a? a, null ab, b
expr+ 1 ou plusieurs fois a+ a,aa, aaa ab, b
expr+? 1 ou plusieurs fois (plus petite occurrence) a+? a aa, ab
expr* 0, 1 ou plusieurs fois a* null, a, aa b, aab
expr*? 0, 1 ou plusieurs fois (plus petite occurrence) a*? null, a aa, b, aab
expr1|expr2 expr1 ou expr2 a|b a, b ab, null
[liste] Un caractère compris dans liste [abc] a, b, c ab, null
[1-4] Un caractère entre 1 et 4 compris. [a-e] a, b, c, d, e f, null
[^liste] Aucun caractère de liste [^1-3] 4, 5, null 1, 2, 3
(expr) Groupement (abc) abc null, abcd, ab
expr{n} Exactement n fois expr a{2} aa a, aaa, null
expr{n,m} Entre n et m fois expr a{1,3} a, aa, aaa aaaa, null
expr{,m} Moins de m fois expr a{,3} a, aa, aaa, null aaaa
expr{n,} Plus de n fois expr a{2,} aa, aaa, aaaa null, a
^expr expr doit être au début ^a abc, aa, a ba, bca, null
expr$ expr doit être à la fin a$ bca, aa, a ab, bac, null
[:alnum:]
ou [A-Za-z0-9]
ou \w
Tout caractère alphanumérique [:alnum:] a, B, c, 1, 2 null, ., !,
\W Aucun caractère alphanum = [^[:alnum:]]      
[:digit:]
ou [0-9]
ou \d
Tout caractère numérique [:digit:] 1, 2, 3 a, b, B
\D Aucun caractère numérique = [^[:digit:]]      
[:alpha:]
ou [A-Za-z]
Tout caractère alphabétique [:alpha:] a, b, c 1, 2
[:lower:]
ou [a-z]
Tout caractère alphabétique en minuscule [:lower:] a, b, c A, B, 1, 2
[:upper:]
ou [A-Z]
Tout caractère alphabétique en majuscule [:upper:] A, B, C a, b, 1, 2
[:punct:] Tout caractère de ponctuation [:punct:] ?, !, ., : null, a, 1
[:space:]
ou \s
Tout espace [:space:] null, a,  ?
\S Aucun espace = [^[:space:]]      
\ Caractère d’échappement \. . null, a

Voici quelques exemples :

MotifCorrespondance
m[aoe]ts mats, mots, mets
([bp]é){2} bébé, pépé
(0|\+33)[1-9]( *[0-9]{2}){4} 01 12 23 34 45, +331 12 23 34 45, 0112233445, 0112  2334 45, …
(.*)(\d+)(.*) abc01234xyz, abc0123, 4, xyz

Pour utiliser ces expressions, vous avez deux façons.

Dans une fonction :

SELECT
	SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');

Dans une clause WHERE :

-- Retrouver toutes les valeurs non alphanumériques
SELECT
	*
FROM
	mon_schema.ma_table
WHERE
	ma_colonne LIKE '.*[[:alnum:]].*'

Maintenant que vous êtes autonome sur PostgreSQL, passons au volet cartographique avec PostGIS.

Sommaire général

Voici le sommaire général du cours :


Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL tuto

50%