Clause WITH

La clause WITH vous permet de spécifier une ou plusieurs sous-requêtes qui peuvent être utilisées par leur nom dans la requête principale. Les sous-requêtes se comportent comme des tables temporaires ou des vues pendant la durée d'exécution de la requête principale. Chaque sous-requête peut être un ordre SELECT, VALUES, INSERT, UPDATE ou bien DELETE. Lorsque vous écrivez un ordre de modification de données (INSERT, UPDATE ou DELETE) dans une clause WITH, il est habituel d'inclure une clause RETURNING. C'est la sortie de cette clause RETURNING, et non pas la table sous-jacente que l'ordre modifie, qui donne lieu à la table temporaire lue par la requête principale. Si la clause RETURNING est omise, l'ordre est tout de même exécuté, mais il ne produit pas de sortie ; il ne peut donc pas être référencé comme une table par la requête principale.

Un nom (sans qualification de schéma) doit être spécifié pour chaque requête WITH. En option, une liste de noms de colonnes peut être spécifié ; si elle est omise, les noms de colonnes sont déduites de la sous-requête.

Si RECURSIVE est spécifié, la sous-requête SELECT peut se référencer elle même. Une sous-requête de ce type doit avoir la forme

terme_non_récursif UNION [ ALL | DISTINCT ] terme_récursif

où l'auto-référence récursive doit apparaître dans la partie droite de l'UNION. Seule une auto-référence récursive est autorisée par requête. Les ordres de modification récursifs ne sont pas supportés, mais vous pouvez utiliser le résultat d'une commande SELECT récursive dans un ordre de modification. Voir Section 7.8, « Requêtes WITH (Common Table Expressions) » pour un exemple.

Un autre effet de RECURSIVE est que les requêtes WITH n'ont pas besoin d'être ordonnées : une requête peut en référencer une autre qui se trouve plus loin dans la liste (toutefois, les références circulaires, ou récursion mutuelle, ne sont pas implémentées). Sans RECURSIVE, les requêtes WITH ne peuvent référencer d'autres requêtes WITH soœurs que si elles sont déclarées avant dans la liste WITH.

Une propriété clé des requêtes WITH est qu'elles ne sont évaluées qu'une seule fois par exécution de la requête principale, même si la requête principale les utilise plus d'une fois. En particulier, vous avez la garantie que les traitements de modification de données sont exécutés une seule et unique fois, que la requête principale lise tout ou partie de leur sortie.

Tout se passe comme si la requête principale et les requêtes WITH étaient toutes exécutées en même temps. Ceci a pour conséquence que les effets d'un ordre de modification dans une clause WITH ne peuvent pas être vues des autres parties de la requête, sauf en lisant la sortie de RETURNING. Si deux de ces ordres de modifications tentent de modifier la même ligne, les résultats sont imprévisibles.

Voir Section 7.8, « Requêtes WITH (Common Table Expressions) » pour plus d'informations.

Clause FROM

La clause FROM spécifie une ou plusieurs tables source pour le SELECT. Si plusieurs sources sont spécifiées, le résultat est un produit cartésien (jointure croisée) de toutes les sources. Mais habituellement, des conditions de qualification (via WHERE) sont ajoutées pour restreindre les lignes renvoyées à un petit sous-ensemble du produit cartésien.

La clause FROM peut contenir les éléments suivants :

Clause WHERE

La clause WHERE optionnelle a la forme générale

WHERE condition

où condition est une expression dont le résultat est de type boolean. Toute ligne qui ne satisfait pas cette condition est éliminée de la sortie. Une ligne satisfait la condition si elle retourne vrai quand les valeurs réelles de la ligne sont substituées à toute référence de variable.

Clause GROUP BY

La clause GROUP BY optionnelle a la forme générale

GROUP BY expression [, ...]

GROUP BY condense en une seule ligne toutes les lignes sélectionnées qui partagent les mêmes valeurs pour les expressions regroupées. expression peut être le nom d'une colonne en entrée, le nom ou le numéro d'une colonne en sortie (élément de la liste SELECT), ou une expression quelconque formée de valeurs de colonnes en entrée. En cas d'ambiguïté, un nom de GROUP BY est interprété comme un nom de colonne en entrée, non en sortie.

Les fonctions d'agrégat, si utilisées, sont calculées pour toutes les lignes composant un groupe, produisant une valeur séparée pour chaque groupe (alors que sans GROUP BY, un agrégat produit une valeur unique calculée pour toutes les lignes sélectionnées). Quand GROUP BY est présent, les expressions du SELECT ne peuvent faire référence qu'à des colonnes groupées, sauf à l'intérieur de fonctions d'agrégat, ou bien si la colonne non groupée dépend fonctionnellement des colonnes groupées. En effet, s'il en était autrement, il y aurait plus d'une valeur possible pour la colonne non groupée. Une dépendance fonctionnelle existe si les colonnes groupées (ou un sous-ensemble de ces dernières) sont la clé primaire de la table contenant les colonnes non groupées.

Clause HAVING

La clause optionnelle HAVING a la forme générale

HAVING condition

où condition est identique à celle spécifiée pour la clause WHERE.

HAVING élimine les lignes groupées qui ne satisfont pas à la condition. HAVING est différent de WHERE : WHERE filtre les lignes individuelles avant l'application de GROUP BY alors que HAVING filtre les lignes groupées créées par GROUP BY. Chaque colonne référencée dans condition doit faire référence sans ambiguïté à une colonne groupée, sauf si la référence apparaît dans une fonction d'agrégat.

Même en l'absence de clause GROUP BY, la présence de HAVING transforme une requête en requête groupée. Cela correspond au comportement d'une requête contenant des fonctions d'agrégats mais pas de clause GROUP BY. Les lignes sélectionnées ne forment qu'un groupe, la liste du SELECT et la clause HAVING ne peuvent donc faire référence qu'à des colonnes à l'intérieur de fonctions d'agrégats. Une telle requête ne produira qu'une seule ligne si la condition HAVING est réalisée, aucune dans le cas contraire.

Clause WINDOW

La clause optionnelle WINDOW a la forme générale

WINDOW nom_window AS ( définition_window ) [, ...]

où nom_window est un nom qui peut être référencé par des clauses OVER ou par des définitions Window, et définition_window est

[ nom_window_existante ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operateur ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ clause_frame ]

Si un nom_window_existante est spécifié, il doit se référer à une entrée précédente dans la liste WINDOW ; la nouvelle Window copie sa clause de partitionnement de cette entrée, ainsi que sa clause de tri s'il y en a. Dans ce cas, la nouvelle Window ne peut pas spécifier sa propre clause PARTITION BY, et ne peut spécifier de ORDER BY que si la Window copiée n'en a pas. La nouvelle Window utilise toujours sa propre clause frame ; la Window copiée ne doit pas posséder de clause frame.

Les éléments de la liste PARTITION BY sont interprétés à peu près de la même façon que des éléments de la section intitulée « Clause GROUP BY », sauf qu'ils sont toujours des expressions simples et jamais le nom ou le numéro d'une colonne en sortie. Une autre différence est que ces expressions peuvent contenir des appels à des fonctions d' agrégat, ce qui n'est pas autorisé dans une clause GROUP BY classique. Ceci est autorisé ici parce que le windowing se produit après le regroupement et l' agrégation.

De façon similaire, les éléments de la liste ORDER BY sont interprétés à peu près de la même façon que les éléments d'un la section intitulée « Clause ORDER BY », sauf que les expressions sont toujours prises comme de simples expressions et jamais comme le nom ou le numéro d'une colonne en sortie.

La clause clause_frame optionnelle définit la frame window pour les fonctions window qui dépendent de la frame (ce n'est pas le cas de toutes). La frame window est un ensemble de lignes liées à chaque ligne de la requête (appelée la ligne courante). La clause_frame peut être une des clauses suivantes :

[ RANGE | ROWS ] début_frame
[ RANGE | ROWS ] BETWEEN début_frame AND fin_frame

où début_frame et fin_frame peuvent valoir

UNBOUNDED PRECEDING
valeur PRECEDING
CURRENT ROW
valeur FOLLOWING
UNBOUNDED FOLLOWING

Si fin_frame n'est pas précisé, il vaut par défaut CURRENT ROW. Les restrictions sont les suivantes : début_frame ne peut pas valoir UNBOUNDED FOLLOWING, fin_frame ne peut pas valoir UNBOUNDED PRECEDING, et le choix fin_frame ne peut apparaître avant le choix début_frame -- par exemple RANGE BETWEEN CURRENT ROW AND valeur PRECEDING n'est pas permis.

L'option par défaut pour la clause frame est RANGE UNBOUNDED PRECEDING, ce qui revient au même que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ; il positionne la frame pour qu'il couvre toutes les lignes à partir du début de la partition jusqu'à la dernière ligne à égalité avec la ligne courante dans l'ordre défini par l' ORDER BY (ce qui signifie toutes les lignes s'il n'y a pas d' ORDER BY). Généralement, UNBOUNDED PRECEDING signifie que la frame commence à la première ligne de la partition, et de même UNBOUNDED FOLLOWING signifie que la frame se termine avec la dernière ligne de la partition (quel que soit le mode, RANGE ou bien ROWS ). Dans le mode ROWS, CURRENT ROW signifie que la frame commence ou se termine sur la ligne courante  ; mais dans le mode RANGE cela signifie que la frame débute ou se termine sur la première ou la dernière des lignes à égalité avec la ligne courante dans l'ordre de la clause ORDER BY. Les valeur PRECEDING et valeur FOLLOWING sont actuellement seulement permis en mode ROWS. Ils indiquent que la frame débute ou se termine autant de lignes avant ou après la ligne courante. valeur doit être une expression entière, ne contenant aucune variable, fonction d' agrégat ni fonction window. La valeur ne doit être ni null ni négative  ; mais elle peut être de zéro, ce qui sélectionne la ligne courante elle-même.

Attention, les options ROWS peuvent produire des résultats imprévisibles si l'ordre défini par l' ORDER BY n'ordonne pas les lignes de manière unique. Les options RANGE sont conçues pour s'assurer que les lignes qui sont à égalité suivant l'ordre de l' ORDER BY sont traitées de la même manière  ; toutes les lignes à égalité seront ensemble dans la frame ou ensemble hors de la frame.

L'utilité d'une clause WINDOW est de spécifier le comportement des fonctions window apparaissant dans la clause la section intitulée « Liste SELECT » ou la clause la section intitulée « Clause ORDER BY » de la requête. Ces fonctions peuvent référencer les entrées de clauses WINDOW par nom dans leurs clauses OVER. Toutefois, il n'est pas obligatoire qu'une entrée de clause WINDOW soit référencée quelque part ; si elle n'est pas utilisée dans la requête, elle est simplement ignorée. Il est possible d'utiliser des fonctions window sans aucune clause WINDOW puisqu'une fonction window peut spécifier sa propre définition de window directement dans sa clause OVER. Toutefois, la clause WINDOW économise de la saisie quand la même définition window est utilisée pour plus d'une fonction window.

Les fonctions window sont décrites en détail dans Section 3.5, « Fonctions window », Section 4.2.8, « Appels de fonction window » et Section 7.2.4, « Traitement de fonctions Window ».

Liste SELECT

La liste SELECT (entre les mots clés SELECT et FROM) spécifie les expressions qui forment les lignes en sortie de l'instruction SELECT. Il se peut que les expressions fassent référence aux colonnes traitées dans la clause FROM. En fait, en général, elles le font.

Comme pour une table, chaque colonne de sortie d'un SELECT a un nom. Dans un SELECT simple, ce nom est juste utilisé pour donner un titre à la colonne pour l'affichage, mais quand le SELECT est une sous-requête d'une requête plus grande, le nom est vu par la grande requête comme le nom de colonne de la table virtuelle produite par la sous-requête. Pour indiquer le nom à utiliser pour une colonne de sortie, écrivez AS nom_de_sortie après l'expression de la colonne. (Vous pouvez omettre AS seulement si le nom de colonne souhaité n'est pas un mot clé réservé par PostgreSQL™ (voir Annexe C, Mots-clé SQL). Pour vous protéger contre l'ajout futur d'un mot clé, il est recommandé que vous écriviez toujours AS ou que vous mettiez le nom de sortie entre guillemets. Si vous n'indiquez pas de nom de colonne, un nom est choisi automatiquement par PostgreSQL™. Si l'expression de la colonne est une simple référence à une colonne alors le nom choisi est le même que le nom de la colonne. Dans les cas plus complexes, un nom de fonction ou de type peut être utilisé, ou le système peut opter pour un nom généré automatiquement tel que ?column?.

Un nom de colonne de sortie peut être utilisé pour se référer à la valeur de la colonne dans les clauses ORDER BY et GROUP BY, mais pas dans la clauseWHERE ou HAVING ; à cet endroit, vous devez écrire l'expression.

* peut être utilisé, à la place d'une expression, dans la liste de sortie comme raccourci pour toutes les colonnes des lignes sélectionnées. De plus, nom_table.* peut être écrit comme raccourci pour toutes les colonnes de cette table. Dans ces cas, il est impossible de spécifier de nouveaux noms avec AS ; les noms des colonnes de sorties seront les même que ceux de la table.

DISTINCT Clause

Si SELECT DISTINCT est spécifié, toutes les lignes en double sont supprimées de l'ensemble de résultats (une ligne est conservée pour chaque groupe de doublons). SELECT ALL spécifie le contraire : toutes les lignes sont conservées. C'est l'option par défaut.

SELECT DISTINCT ON ( expression [, ...] ) conserve seulement la première ligne de chaque ensemble de lignes pour lesquelles le résultat de l'expression est identique. Les expressions DISTINCT ON expressions sont interprétées avec les mêmes règles que pour ORDER BY (voir ci-dessous). Notez que la « première ligne » de chaque ensemble est imprévisible, à moins que la clause ORDER BY ne soit utilisée, assurant ainsi que la ligne souhaitée apparaisse en premier. Par exemple :

SELECT DISTINCT ON (lieu) lieu, heure, rapport
    FROM rapport_météo
    ORDER BY lieu, heure DESC;

renvoie le rapport météo le plus récent de chaque endroit. Mais si nous n'avions pas utilisé ORDER BY afin de forcer le tri du temps dans le sens descendant des temps pour chaque endroit, nous aurions récupéré, pour chaque lieu, n'importe quel bulletin de ce lieu.

La (ou les ) expression(s) DISTINCT ON doivent correspondre à l'expression (ou aux expressions) ORDER BY la(les) plus à gauche. La clause ORDER BY contient habituellement des expressions supplémentaires qui déterminent l'ordre des lignes au sein de chaque groupe DISTINCT ON.

Clause UNION

La clause UNION a la forme générale :

instruction_select UNION [ ALL | DISTINCT ] instruction_select

instruction_select est une instruction SELECT sans clause ORDER BY, LIMIT, FOR SHARE ou FOR UPDATE. (ORDER BY et LIMIT peuvent être attachés à une sous-expression si elle est entourée de parenthèses. Sans parenthèses, ces clauses s'appliquent au résultat de l'UNION, non à l'expression à sa droite.)

L'opérateur UNION calcule l'union ensembliste des lignes renvoyées par les instructions SELECT impliquées. Une ligne est dans l'union de deux ensembles de résultats si elle apparaît dans au moins un des ensembles. Les deux instructions SELECT qui représentent les opérandes directes de l'UNION doivent produire le même nombre de colonnes et les colonnes correspondantes doivent être d'un type de données compatible.

Sauf lorsque l'option ALL est spécifiée, il n'y a pas de doublons dans le résultat de UNION. ALL empêche l'élimination des lignes dupliquées. UNION ALL est donc significativement plus rapide qu'UNION, et sera préféré. DISTINCT peut éventuellement être ajouté pour préciser explicitement le comportement par défaut : l'élimination des lignes en double.

Si une instruction SELECT contient plusieurs opérateurs UNION, ils sont évalués de gauche à droite, sauf si l'utilisation de parenthèses impose un comportement différent.

Actuellement, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE et FOR KEY SHARE ne peuvent pas être spécifiés pour un résultat d'UNION ou pour toute entrée d'un UNION.

Clause INTERSECT

La clause INTERSECT a la forme générale :

instruction_select INTERSECT [ ALL | DISTINCT ] instruction_select

instruction_select est une instruction SELECT sans clause ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE ou FOR KEY SHARE.

L'opérateur INTERSECT calcule l'intersection des lignes renvoyées par les instructions SELECT impliquées. Une ligne est dans l'intersection des deux ensembles de résultats si elle apparaît dans chacun des deux ensembles.

Le résultat d'INTERSECT ne contient aucune ligne dupliquée sauf si l'option ALL est spécifiée. Dans ce cas, une ligne dupliquée m fois dans la table gauche et n fois dans la table droite apparaît min(m,n) fois dans l'ensemble de résultats. DISTINCT peut éventuellement être ajouté pour préciser explicitement le comportement par défaut : l'élimination des lignes en double.

Si une instruction SELECT contient plusieurs opérateurs INTERSECT, ils sont évalués de gauche à droite, sauf si l'utilisation de parenthèses impose un comportement différent. INTERSECT a une priorité supérieur à celle d'UNION. C'est-à-dire que A UNION B INTERSECT C est lu comme A UNION (B INTERSECT C).

Actuellement, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE et FOR KEY SHARE ne peuvent pas être spécifiés pour un résultat d'INTERSECT ou pour une entrée d'INTERSECT.

Clause EXCEPT

La clause EXCEPT a la forme générale :

instruction_select EXCEPT [ ALL | DISTINCT ] instruction_select

instruction_select est une instruction SELECT sans clause ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE ou FOR KEY SHARE.

L'opérateur EXCEPT calcule l'ensemble de lignes qui appartiennent au résultat de l'instruction SELECT de gauche mais pas à celui de droite.

Le résultat d'EXCEPT ne contient aucune ligne dupliquée sauf si l'option ALL est spécifiée. Dans ce cas, une ligne dupliquée m fois dans la table gauche et n fois dans la table droite apparaît max(m-n,0) fois dans l'ensemble de résultats. DISTINCT peut éventuellement être ajouté pour préciser explicitement le comportement par défaut : l'élimination des lignes en double.

Si une instruction SELECT contient plusieurs opérateurs EXCEPT, ils sont évalués de gauche à droite, sauf si l'utilisation de parenthèses impose un comportement différent. EXCEPT a la même priorité qu'UNION.

Actuellement, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE et FOR KEY SHARE ne peuvent pas être spécifiés dans un résultat EXCEPT ou pour une entrée d'un EXCEPT.

Clause ORDER BY

La clause optionnelle ORDER BY a la forme générale :

ORDER BY expression [ ASC | DESC | USING opérateur ] [ NULLS { FIRST | LAST } ] [, ...]

La clause ORDER BY impose le tri des lignes de résultat suivant les expressions spécifiées. Si deux lignes sont identiques suivant l'expression la plus à gauche, elles sont comparées avec l'expression suivante et ainsi de suite. Si elles sont identiques pour toutes les expressions de tri, elles sont renvoyées dans un ordre dépendant de l'implantation.

Chaque expression peut être le nom ou le numéro ordinal d'une colonne en sortie (élément de la liste SELECT). Elle peut aussi être une expression arbitraire formée à partir de valeurs des colonnes.

Le numéro ordinal fait référence à la position ordinale (de gauche à droite) de la colonne de résultat. Cette fonctionnalité permet de définir un ordre sur la base d'une colonne dont le nom n'est pas unique. Ce n'est pas particulièrement nécessaire parce qu'il est toujours possible d'affecter un nom à une colonne de résultat avec la clause AS.

Il est aussi possible d'utiliser des expressions quelconques dans la clause ORDER BY, ce qui inclut des colonnes qui n'apparaissent pas dans la liste résultat du SELECT. Ainsi, l'instruction suivante est valide :

SELECT nom FROM distributeurs ORDER BY code;

Il y a toutefois une limitation à cette fonctionnalité. La clause ORDER BY qui s'applique au résultat d'une clause UNION, INTERSECT ou EXCEPT ne peut spécifier qu'un nom ou numéro de colonne en sortie, pas une expression.

Si une expression ORDER BY est un nom qui correspond à la fois à celui d'une colonne résultat et à celui d'une colonne en entrée, ORDER BY l'interprète comme le nom de la colonne résultat. Ce comportement est à l'opposé de celui de GROUP BY dans la même situation. Cette incohérence est imposée par la compatibilité avec le standard SQL.

Un mot clé ASC (ascendant) ou DESC (descendant) peut être ajouté après toute expression de la clause ORDER BY. ASC est la valeur utilisée par défaut. Un nom d'opérateur d'ordre spécifique peut également être fourni dans la clause USING. Un opérateur de tri doit être un membre plus-petit-que ou plus-grand-que de certaines familles d'opérateur B-tree. ASC est habituellement équivalent à USING < et DESC à USING >. Le créateur d'un type de données utilisateur peut définir à sa guise le tri par défaut qui peut alors correspondre à des opérateurs de nom différent.

Si NULLS LAST est indiqué, les valeurs NULL sont listées après toutes les valeurs non NULL  si NULLS FIRST est indiqué, les valeurs NULL apparaissent avant toutes les valeurs non NULL. Si aucune des deux n'est présente, le comportement par défaut est NULLS LAST quand ASC est utilisé (de façon explicite ou non) et NULLS FIRST quand DESC est utilisé (donc la valeur par défaut est d'agir comme si les NULL étaient plus grands que les non NULL). Quand USING est indiqué, le tri des NULL par défaut dépend du fait que l'opérateur est un plus-petit-que ou un plus-grand-que.

Notez que les options de tri s'appliquent seulement à l'expression qu'elles suivent. Par exemple, ORDER BY x, y DESC ne signifie pas la même chose que ORDER BY x DESC, y DESC.

Les chaînes de caractères sont triées suivant le collationnement qui s'applique à la colonne triée. Ce collationnement est surchargeable si nécessaire en ajoutant une clause COLLATE dans l'expression, par exemple ORDER BY mycolumn COLLATE "en_US". Pour plus d'informations, voir Section 4.2.10, « Expressions de collationnement » et Section 22.2, « Support des collations ».

Clause LIMIT

La clause LIMIT est constituée de deux sous-clauses indépendantes :

LIMIT { nombre | ALL }
OFFSET début

nombre spécifie le nombre maximum de lignes à renvoyer alors que début spécifie le nombre de lignes à passer avant de commencer à renvoyer des lignes. Lorsque les deux clauses sont spécifiées, début lignes sont passées avant de commencer à compter les nombre lignes à renvoyer.

Si l'expression de compte est évaluée à NULL, il est traité comme LIMIT ALL, c'est-à-dire sans limite. Si début est évalué à NULL, il est traité comme OFFSET 0.

SQL:2008 a introduit une sytaxe différente pour obtenir le même résultat. PostgreSQL™ supporte aussi cette syntaxe.

OFFSET début { ROW | ROWS }
FETCH { FIRST | NEXT } [ compte ] { ROW | ROWS } ONLY

Avec cette syntaxe, pour écrire tout sauf une simple constant de type entier pour début ou compte, vous devez l'entourer de parenthèses. Si compte est omis dans une clause FETCH, il vaut 1 par défaut. ROW et ROWS ainsi que FIRST et NEXT sont des mots qui n'influencent pas les effets de ces clauses. D'après le standard, la clause OFFSET doit venir avant la clause FETCH si les deux sont présentes ; PostgreSQL™ est plus laxiste et autorise un ordre différent.

Avec LIMIT, utiliser la clause ORDER BY permet de contraindre l'ordre des lignes de résultat. Dans le cas contraire, le sous-ensemble obtenu n'est pas prévisible -- rien ne permet de savoir à quel ordre correspondent les lignes retournées. Celui-ci ne sera pas connu tant qu'ORDER BY n'aura pas été précisé.

Lors de la génération d'un plan de requête, le planificateur tient compte de LIMIT. Le risque est donc grand d'obtenir des plans qui diffèrent (ordres des lignes différents) suivant les valeurs utilisées pour LIMIT et OFFSET. Ainsi, sélectionner des sous-ensembles différents d'un résultat à partir de valeurs différentes de LIMIT/OFFSET aboutit à des résultats incohérents à moins d'avoir figé l'ordre des lignes à l'aide de la clause ORDER BY. Ce n'est pas un bogue, mais une conséquence du fait que SQL n'assure pas l'ordre de présentation des résultats sans utilisation d'une clause ORDER BY.

Il est même possible pour des exécutions répétées de la même requête LIMIT de renvoyer différents sous-ensembles des lignes d'une table s'il n'y a pas de clause ORDER BY pour forcer la sélection d'un sous-ensemble déterministe. Encore une fois, ce n'est pas un bogue ; le déterminisme des résultats n'est tout simplement pas garanti dans un tel cas.

Clause de verrouillage

FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE et FOR KEY SHARE sont des clauses de verrouillage. Elles affectent la façon dont SELECT verrouille les lignes au moment de leur obtention sur la table.

La clause de verrouillage a la forme suivante :

FOR force_verrou [ OF nom_table [, ...] ] [ NOWAIT ]

où force_verrou fait partie de :

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE
 

FOR UPDATE verrouille pour modification les lignes récupérées par l'instruction SELECT. Cela les empêche d'être modifiées ou supprimées par les autres transactions jusqu'à la fin de la transaction en cours. Les autres transactions qui tentent des UPDATE, DELETE ou SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE ou SELECT FOR KEY SHARE sur ces lignes sont bloquées jusqu'à la fin de la transaction courante. Le mode de verrouillage FOR UPDATE est aussi acquis par toute commande DELETE sur une ligne ainsi que par un UPDATE qui modifie les valeurs de certaines colonnes. Actuellement, l'ensemble de colonnes considéré par le cas UPDATE est celui qu'a un index unique lors de son utilisation par une clé étrangère (donc les index partiels et fonctionnels ne sont pas considérés), mais cela pourra être modifié dans le futur. De plus, si un UPDATE, DELETE ou SELECT FOR UPDATE a déjà verrouillé une ligne ou un ensemble de lignes à partir d'une autre transaction, SELECT FOR UPDATE attend la fin de l'autre transaction puis verrouille et renvoie la ligne modifiée (ou aucune ligne si elle a été supprimée). Cependant, au sein d'une transaction REPEATABLE READ ou SERIALIZABLE, une erreur est levée si une ligne à verrouiller à changé depuis le début de la transaction. Pour plus d'informations, voir Chapitre 13, Contrôle d'accès simultané.

FOR NO KEY UPDATE se comporte de la même façon sauf que le verrou acquis est moins fort : ce verrou ne bloquera pas les commandes SELECT FOR KEY SHARE qui tenteraient d'acquérir un verrou sur les mêmes lignes. Ce mode de verrou est aussi acquis par tout UPDATE qui ne nécessite pas un verrou FOR UPDATE.

FOR SHARE a un comportement similaire. La différence se situe dans le type de verrou acquis. Contrairement à FOR UPDATE qui pose un verrou exclusif, FOR SHARE pose un verrou partagé sur chaque ligne récupérée. Un verrou partagée bloque les instructions UPDATE, DELETE, SELECT FOR UPDATE et SELECT FOR NO KEY UPDATE sur ces lignes mais il n'empêche pas les SELECT FOR SHARE et SELECT FOR KEY SHARE.

FOR KEY SHARE a un comportement similaire à FOR SHARE, sauf que le verrou est plus faible : SELECT FOR UPDATE est verrouillé alors que SELECT FOR NO KEY UPDATE ne l'est pas. Un verrou à clé partagée bloque les autres transactions lors de l'exécution d'un DELETE ou d'un UPDATE qui modifie les valeurs clés mais pas les autres UPDATE. Il n'empêche pas non plus les SELECT FOR NO KEY UPDATE, SELECT FOR SHARE et SELECT FOR KEY SHARE.

Pour éviter à l'opération d'attendre la validation des autres transactions, on utilise l'option NOWAIT. SELECT FOR UPDATE NOWAIT rapporte une erreur si une ligne sélectionnée ne peut pas être verrouillée immédiatement. Il n'y a pas d'attente. NOWAIT s'applique seulement au(x) verrou(x) niveau ligne -- le verrou niveau table ROW SHARE est toujours pris de façon ordinaire (voir Chapitre 13, Contrôle d'accès simultané). L'option NOWAIT de LOCK(7) peut toujours être utilisée pour acquérir le verrou niveau table sans attendre.

Si des tables particulières sont nommées dans une clause de verrouillage, alors seules les lignes provenant de ces tables sont verrouillées ; toute autre table utilisée dans le SELECT est simplement lue. Une clause de verrouillage sans liste de tables affecte toute les tables utilisées dans l'instruction. Si une clause de verrouillage est appliquée à une vue ou à une sous-requête, cela affecte toutes les tables utilisées dans la vue ou la sous-requête. Néanmoins, ces clauses ne s'appliquent pas aux requêtes WITH référencées par la clé primaire. Si vous voulez qu'un verrouillage de lignes intervienne dans une requête WITH, spécifiez une clause de verrouillage à l'intérieur de la requête WITH.

Plusieurs clauses de verrouillage peuvent être données si il est nécessaire de spécifier différents comportements de verrouillage pour différentes tables. Si la même table est mentionné (ou affectée implicitement) par plus d'une clause de verrouillage, alors elle est traitée comme la clause la plus forte. De façon similaire, une table est traitée avec NOWAIT si c'est spécifiée sur au moins une des clauses qui l'affectent.

Les clauses de verrouillage nécessitent que chaque ligne retournée soit clairement identifiable par une ligne individuelle d'une table ; ces options ne peuvent, par exemple, pas être utilisées avec des fonctions d'agrégats.

Quand une clause de verrouillage apparaissent au niveau le plus élevé d'une requête SELECT, les lignes verrouillées sont exactement celles qui sont renvoyées par la requête ; dans le cas d'une requête avec jointure, les lignes verrouillées sont celles qui contribuent aux lignes jointes renvoyées. De plus, les lignes qui ont satisfait aux conditions de la requête au moment de la prise de son instantané sont verrouillées, bien qu'elles ne seront pas retournées si elles ont été modifiées après la prise du snapshot et ne satisfont plus les conditions de la requête. Si LIMIT est utilisé, le verrouillage cesse une fois que suffisamment de lignes ont été renvoyées pour satisfaire la limite (mais notez que les lignes ignorées à cause de la clause OFFSET seront verrouillées). De la même manière, si une clause de verrouillage est utilisé pour la requête d'un curseur, seules les lignes réellement récupérées ou parcourues par le curseur seront verrouillées.

Si une clause de verrouillage apparait dans un sous-SELECT, les lignes verrouillées sont celles renvoyées par la sous-requête à la requête externe. Cela peut concerner moins de lignes que l'étude de la sous-requête seule pourrait faire penser, parce que les conditions de la requête externe peuvent être utilisées pour optimiser l'exécution de la sous-requête. Par exemple,

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

verrouillera uniquement le lignes pour lesquelles col1 = 5, même si cette condition n'est pas écrite dans la sous-requête.

Les anciennes versions échouaient à préserver un verrou qui est mis à jour par un point de sauvegarde ultérieur. Par exemple, ce code :

BEGIN;
SELECT * FROM ma_table WHERE cle = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE ma_table SET ... WHERE cle = 1;
ROLLBACK TO s;

va échouer à conserver le verrou FOR UPDATE après la commande ROLLBACK TO. Ceci a été corrigé en 9.3.

Commande TABLE

La commande

TABLE nom

est complètement équivalente à

SELECT * FROM nom

Elle peut être utilisée comme commande principale d'une requête, ou bien comme une variante syntaxique permettant de gagner de la place dans des parties de requêtes complexes.

Clauses FROM omises

PostgreSQL™ autorise l'omission de la clause FROM. Cela permet par exemple de calculer le résultat d'expressions simples :

SELECT 2+2;

 ?column?
----------
        4

D'autres bases de données SQL interdisent ce comportement, sauf à introduire une table virtuelle d'une seule ligne sur laquelle exécuter la commande SELECT.

S'il n'y a pas de clause FROM, la requête ne peut pas référencer les tables de la base de données. La requête suivante est, ainsi, invalide :

SELECT distributors.* WHERE distributors.name = 'Westward';

Les versions antérieures à PostgreSQL™ 8.1 acceptaient les requêtes de cette forme en ajoutant une entrée implicite à la clause FROM pour chaque table référencée. Ce n'est plus autorisé.

Omettre le mot clé AS

Dans le standard SQL, le mot clé AS peut être omis devant une colonne de sortie à partir du moment où le nouveau nom de colonne est un nom valide de colonne (c'est-à-dire, différent d'un mot clé réservé). PostgreSQL™ est légèrement plus restrictif : AS est nécessaire si le nouveau nom de colonne est un mot clé quel qu'il soit, réservé ou non. Il est recommandé d'utiliser AS ou des colonnes de sortie entourées de guillemets, pour éviter tout risque de conflit en cas d'ajout futur de mot clé.

Dans les éléments de FROM, le standard et PostgreSQL™ permettent que AS soit omis avant un alias qui n'est pas un mot clé réservé. Mais c'est peu pratique pour les noms de colonnes, à causes d'ambiguïtés syntaxiques.

ONLY et l'héritage

Le standard SQL impose des parenthèses autour du nom de table après la clause ONLY, comme dans SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL™ considère les parenthèses comme étant optionnelles.

PostgreSQL™ autorise une * en fin pour indiquer explicitement le comportement opposé de la clause ONLY (donc inclure les tables filles). Le standard ne le permet pas.

(Ces points s'appliquent de la même façon à toutes les commandes SQL supportant l'option ONLY.)

Appels de fonction dans la clause FROM

PostgreSQL™ autorise un appel de fonction dans la liste FROM. Pour le standard SQL, il serait nécessaire de placer cet appel de fonction dans un sous-SELECT ; autrement dit, la syntaxe FROM fonc(...) alias est à peu près équivalente à FROM LATERAL (SELECT fonc(...)) alias. Notez que LATERAL est considéré comme étant implicite ; ceci est dû au fait que le standard réclame la sémantique de LATERAL pour un élément UNNEST() dans la clause FROM. PostgreSQL™ traite UNNEST() de la même façon que les autres fonctions renvoyant des lignes.

Espace logique disponible pour GROUP BY et ORDER BY

Dans le standard SQL-92, une clause ORDER BY ne peut utiliser que les noms ou numéros des colonnes en sortie, une clause GROUP BY que des expressions fondées sur les noms de colonnes en entrée. PostgreSQL™ va plus loin, puisqu'il autorise chacune de ces clauses à utiliser également l'autre possibilité. En cas d'ambiguïté, c'est l'interprétation du standard qui prévaut. PostgreSQL™ autorise aussi l'utilisation d'expressions quelconques dans les deux clauses. Les noms apparaissant dans ces expressions sont toujours considérés comme nom de colonne en entrée, pas en tant que nom de colonne du résultat.

SQL:1999 et suivant utilisent une définition légèrement différente, pas totalement compatible avec le SQL-92. Néanmoins, dans la plupart des cas, PostgreSQL™ interprète une expression ORDER BY ou GROUP BY en suivant la norme SQL:1999.

Dépendances fonctionnelles

PostgreSQL™ reconnaît les dépendances fonctionnelles (qui permettent que les nom des colonnes ne soient pas dans le GROUP BY) seulement lorsqu'une clé primaire est présente dans la liste du GROUP BY. Le standard SQL spécifie des configurations supplémentaires qui doivent être reconnues.

Restrictions sur la clause WINDOW

Le standard SQL fournit des options additionnelles pour la clause_frame des window. PostgreSQL™ ne supporte à ce jour que les options mentionnées précédemment.

LIMIT et OFFSET

Les clauses LIMIT et OFFSET sont une syntaxe spécifique à PostgreSQL™, aussi utilisée dans MySQL™. La norme SQL:2008 a introduit les clauses OFFSET ... FETCH {FIRST|NEXT}... pour la même fonctionnalité, comme montré plus haut dans la section intitulée « Clause LIMIT ». Cette syntaxe est aussi utilisée par IBM DB2™. (Les applications écrites pour Oracle™ contournent fréquemment le problème par l'utilisation de la colonne auto-générée rownum pour obtenir les effets de ces clauses, qui n'est pas disponible sous PostgreSQL,)

FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE

Bien que FOR UPDATE soit présent dans le standard SQL, le standard ne l'autorise que comme une option de DECLARE CURSOR. PostgreSQL™ l'autorise dans toute requête SELECT et dans toute sous-requête SELECT, mais c'est une extension. Les variantes FOR NO KEY UPDATE, FOR SHARE et FOR KEY SHARE, ainsi que l'option NOWAIT, n'apparaissent pas dans le standard.

Ordre de modification de données dans un WITH

PostgreSQL™ permet que les clauses INSERT, UPDATE, et DELETE soient utilisées comme requêtes WITH. Ceci n'est pas présent dans le standard SQL.

Clauses non standard

La clause DISTINCT ON n'est pas définie dans le standard SQL.