Voici quelques fonctions et opérateurs à utiliser
Fonctions
Numériques
Fonction | Description | Exemple | Résultat |
---|---|---|---|
x+y | Addition | 1+1 | 2 |
x-y | Soustraction | 1-1 | 0 |
x*y | Multiplication | 2*3 | 6 |
x/y | Division | 6/3 | 2 |
x%y ou mod(x,y) | Modulo (reste entier de la division) | 9%4 | 1 |
div(x,y) | Diviseur entier | div(11,5) | 2 |
x^y ou power(x,y) | Puissance | 2^3 | 8 |
sqrt(x) | Racine carrée | sqrt(16) | 4 |
cbrt(x) | Racine cubique | cbrt(32) | 4 |
x! ou !!5 | Factoriel | 5! | 120 |
abs(x) | Valeur absolue | abs(-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érieur | ceil(2.2) | 3 |
floor(x) | Arrondi à l’entier inférieur | floor(2.8) | 2 |
trunc(x,n) | Troncature (défaut n = 0 ) | trunc(2.5375,2) | 2.53 |
pi() | Pi | pi() | 3.14159265358979 |
degrees(x) | Convertion randians vers degrés | degrees(3.1415) | 179.99 |
radian(x) | Convertion degrés vers radians | radian(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
Fonction | Description | Exemple | Résultat |
---|---|---|---|
chr(n) | Renvoi le caractère correspondant au code | chr(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éparateur | concat('/','valeur1','valeur2') | 'valeur1/valeur2' |
char_length('chaine_1') ou length('chaine_1') | Nombre de caractères | char_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ères | left('chaine_1', 2) | 'ch' |
right('chaine_1', n) | Renvoi les n derniers caractères | right('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ère | substring('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 x | strpos('chaine_1', 'aine') | 3 |
lower('chaine_1') | Met en minuscule | lower('ChAiNe_1') | 'chaine_1' |
upper('chaine_1') | Met en majuscule | upper('ChAiNe_1') | 'CHAINE_1' |
initcap('chaine_1') | Met en majuscule la première lettre de chaque mot | initcap('ChAiNe_1 chaInE_2') | 'Chaine_1 Chaine_2' |
reverse('chaine_1') | Inverse les caractères | reverse('chaine_1') | '1_eniahc' |
md5('chaine_1') | Calcul le hash MD5 | md5('chaine_1') | 'd5ab9d67dcbc7429300a1716f203c33c' |
repeat('chaine_1', n) | Répète la chaine n fois | repeat('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 y | replace('chaine_1', 'ain', 'aaaaa') | 'chaaaaae_1' |
regexp_replace('chaine_1', 'motif_regex', 'y') | Remplace le motif d’expression régulière par y | regexp_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ée | split_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ères | regexp_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ères | regexp_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 PostgreSQL | quote_ident('chaine_1') | '"chaine_1"' |
quote_literal('chaine_1') | Ajoute des guillemets pour obtenir une valeur textuelle | quote_ident(E'C\'est super') | 'C''est super' |
coalesce('chaine_1','chaine_2') | Renvoi la première chaine non nulle | coalesce(NULL,NULL,'chaine_3') | 'chaine_3' |
Informations serveur
Quelques fonctions supplémentaires
Fonction | Description |
---|---|
current_catalog | Nom 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_schema | Nom 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_user | Nom 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
Fonction | Description | Exemple | Résultat |
---|---|---|---|
current_time ou localtime | Heure actuelle | ||
current_date | Date 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ément | extract(hour from '2019-04-05 10:42:33') | 10 |
date_trunk(precision, timestamp) | Tronque une date selon la precision indiquée | date_trunk(hour, '2019-04-05 10:42:33') | 2019-04-05 10:00:00 |
to_timestamp(timestamp_unix) | Converti un timestamp unix | to_timestamp(1284352323) | 2010-09-13 04:32:03+00 |
age(timestamp, timestamp) | Interval entre deux timestamp | age('2019-04-05', '2019-04-10') | 5 days |
make_date(annee, mois, jour) | Créé une date à partir de trois valeurs numériques | make_date(2019, 04, 05) | 2019-04-05 |
make_time(heure, minute, seconde) | Créé une heure à partir de trois valeurs numériques | make_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ériques | make_date(2019, 04, 05, 14, 34, 22.5) | 2019-04-05 14:34:22.5 |
Pour les extractions, voici les formats possibles :
millenium
: millénairecentury
: siècledecade
: décénieyear
: annéequarter
: trimestre de l’annéemonth
: moisweek
: semaine depuis le début de l’annéedoy
: jour de l’annéeday
: jour du moisdow
: jour de la semaine (dimanche = 0 et samedi = 6)isodow
: jour de la semaine (lundi = 1 et dimanche = 7)hour
: heuresminute
: minutessecond
: secondesmillisecond
: secondes en millisecondesmicrosecond
: secondes en microsecondesepoch
: timestamp unix – nombre de jours depuis le 1er janvier 1970 à 00:00:00timezone_hour
: écart par rapport à l’heure UTC en heuretimezone_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.
Fonction | Description | Exemple | Résultat |
---|---|---|---|
array['x', 'y'] | Créer un tableaux | array['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 nulle | string_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 tableaux | array_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 tableau | array_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 tableau | array_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ément | array_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 y | array_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 nulles | array_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 lignes | unnest(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 tableau | array_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 tableau | array_length(ARRAY['chaine_1','chaine_2']) | 2 |
cardinality(tableau_1) | Nombre d’élément dans un tableau | cardinality(ARRAY[['chaine_1','chaine_2']['chaine_3','chaine_4']]) | 4 |
tableau_1 @> tableau_2 | Tableau_1 contient tableau_2 | ARRAY['chaine_1','chaine_2','chaine_3'] @> ARRAY['chaine_1','chaine_2']) | TRUE |
tableau_1 <@ tableau_2 | Tableau_1 est contenu dans tableau_2 | ARRAY['chaine_1','chaine_2'] <@ ARRAY['chaine_1','chaine_2','chaine_3']) | TRUE |
XML
Fonction | Description | Exemple | Résultat |
---|---|---|---|
xmlelement(name x) | Créé un élément XML | xmlelement(name br) | <br /> |
xmlelement(name x, xmlattributes('y' as z)) | Créé un élément XML avec un attribut | xmlelement(name img, xmlattributes('/media/image.jpg' as src)) | <img src="/media/image.jpg"/> |
xmlelement(name x, 'y') | Créé un élément XML avec du contenu | xmlelement(name div, 'Chaine 1') | <div>Chaine 1</div> |
xmlcomment('x') | Créé un commentaire XML | xmlcomment('chaine_1') | <!--hello--> |
xmlforest('w' as x, 'y' as z) | Créé plusieurs élément XML avec leurs contenus | xmlforest('Chaine 1' as div, 'Chaine 2' as span) | <div>Chaine 1</div><span>Chaine 2</span> |
xmlpi(name x, 'y') | Créé une instruction XML | xmlpi(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" } }
Fonction | Description | Exemple | Résultat |
---|---|---|---|
json_1 -> 'clé' | Récupérer la valeur d’une clé au format json | ex_json -> 'a' | "chaine_1" |
json_1 ->> 'clé' | Récupérer la valeur d’une clé au format texte | ex_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 chemin | jsonb_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 json | ex_json #> '{c,2}' | "chaine_3" |
json_1 #>> 'chemin' | Récupérer la valeur depuis un chemin au format texte | ex_json #>> '{c,2}' | 'chaine_3' |
json_1 @> json_2 | Le json_1 contient-il le json_2 ? | ex_json @> '{"b":"chaine_2"}' | TRUE |
json_1 <@ json_2 | Le json_2 contient-il le json_1 ? | '{"b":"chaine_2"}' <@ ex_json | TRUE |
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ément | ex_json - 'c' | {"a": "chaine_1", "b": "chaine_2"} |
json_1 #- 'chemin' | Supprimer un élément à partir d’un chemin | ex_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) | { |
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
Fonction | Description |
---|---|
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.
Fonction | Description |
---|---|
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
CC | Siècle (2 chiffres) |
YYYY | Années (4 chiffres) |
YY | Années (2 derniers chiffres) |
MONTH | Mois en toute lettre (en majuscule complété avec des espaces à 9 caractères) |
Month | Mois en toute lettre (première lettre en majuscule complété avec des espaces à 9 caractères) |
month | Mois en toute lettre (en minuscule complété avec des espaces à 9 caractères) |
MON | Mois en abrégé (en majuscule) |
Mon | Mois en abrégé (première lettre en majuscule) |
mon | Mois en abrégé (en minuscule) |
MM | Mois en numérique (01-12) |
WW | Numéro de semaine (1-53) (la première semaine démarre le premier jour de l’année) |
IW | Numéro de semaine ISO (1-53) (le premier jeudi de l’année est semaine 1) |
DAY | Jour en toute lettre (en majuscule complété avec des espaces à 9 caractères) |
Day | Jour en toute lettre (première lettre en majuscule complété avec des espaces à 9 caractères) |
day | Jour en toute lettre (en minuscule complété avec des espaces à 9 caractères) |
DY | Jour en abrégé (en majuscule) |
Dy | Jour en abrégé (première lettre en majuscule) |
dy | Jour en abrégé (en minuscule) |
DDD | Jour de l’année (001-366) |
DD | Jour du mois (01-31) |
D | Jour de la semaine (dimanche = 1, samedi = 7) |
ID | Jour de la semaine (lundi = 1, dimanche = 7) |
HH
ou HH12 | Heure (01-12) |
HH24 | Heure (00-23) |
MI | Minute (00-59) |
SS | Seconde (00-59) |
MS | Milliseconde (000-999) |
US | Microseconds (000000-999999) |
SSSS | Seconde depuis minuit (0-86399) |
TZ | Fuseau horaire en abrégé (en majuscule) |
tz | Fuseau 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 :
Exemple | Ré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
9 | Emplacement d’un chiffre (non utilisé si inutile) |
0 | Emplacement d’un chiffre (toujours utilisé même si inutile) |
. | Séprateur des décimales |
D | Séprateur des décimales selon la locale |
, | Séprateur des milliers |
G | Séprateur des milliers selon la locale |
MI | Signe négatif (si le nombre est < 0) |
PL | Signe positif (si le nombre est > 0) |
SG | Signe (positif ou négatif) |
RN | Chiffre romain (de 1 à 3999) |
EEEE | Notation scientifique |
FM | Supprimer les espaces supplémentaires |
Quelques exemples provenant de la documentation officielle de PostgreSQL (disponible ici) :
Exemple | Ré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
.
Fonction | Description |
---|---|
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.
J’ai consacré un article entier sur le fenêtrage et ses fonctions, vous en apprendrez plus par ici :
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édicat | Description |
---|---|
a < b | a inférieur à b |
a > b | a supérieur à b |
a <= b | a inférieur ou égal à b |
a >= b | a supérieur ou égal à b |
a = b | a égal à b |
a <> b ou a != b | a différent de b |
a BETWEEN x AND y | a 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 y | a 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 b | a est différent de b . Les valeurs null sont considérées comme des valeurs ordinaires |
a IS NOT DISTINCT FROM b | a égal b . Les valeurs null sont considérées comme des valeurs ordinaires |
a LIKE b | a é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 NULL | a est null |
a IS NOT NULL | a n’est pas null |
a IS TRUE | a est vrai |
a IS NOT TRUE | a est faux ou inconnu |
a IS FALSE | a est faux |
a IS NOT FALSE | a est vrai ou inconnu |
a IS UNKNOWN | a est inconnu |
a IS NOT UNKNOWN | a 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 ba OR b
: a ou bNOT a
: pas a
Voici le tableau de résultat
a | b | a AND b | a OR b |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | NULL | NULL | NULL |
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érateur | Description | Exemple | Valeur correspondante | Valeur 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 :
Motif | Correspondance |
---|---|
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