| Documentation PostgreSQL 8.0.25 | ||||
|---|---|---|---|---|
| Pr�c�dent | Arri�re rapide | Avance rapide | Suivant | |
Cette section tente de mettre en �vidence dans quelle mesure PostgreSQL est conforme au standard SQL actuel. L'information qui suit n'est pas une indication compl�te de compatibilit� mais pr�sente les th�mes principaux avec autant de d�tails que possible, c'est-�-dire en restant raisonnable et utile pour l'utilisateur.
Le nom complet du standard SQL est ISO/IEC 9075 <<�Database Language SQL�>>. Une version revue du standard est publi�e de temps en temps dont la plus r�cente date de la fin 2003. Cette version est r�f�renc�e en temps que ISO/IEC 9075:2003, ou de mani�re informelle comme SQL:2003. Les versions pr�c�dentes � celle-ci �taient SQL:1999 et SQL-92. Chaque version remplace la pr�c�dente, donc toute indication de support des versions pr�c�dentes n'a aucun m�rite. Le d�veloppement de PostgreSQL tend � se conformer � la derni�re version officielle dans la mesure o� celle-ci ne viendrait pas s'opposer aux fonctionnalit�s traditionnelles ou au bon sens. Le projet PostgreSQL n'�tait pas repr�sent� dans le groupe de travail ISO/IEC 9075 Working Group lors de la pr�paration de SQL:2003. Malgr� cela, un grand nombre des fonctionnalit�s requises par SQL:2003 sont d�j� support�es, bien que, parfois, avec une syntaxe ou une fonction l�g�rement diff�rente. Une meilleure compatibilit� est attendue pour les prochaines versions.
SQL-92 d�finit trois ensembles de fonctionnalit� pour le test de compatibilit� : base, interm�diaire, compl�te. La majorit� des syst�mes de gestion de bases de donn�es se disant compatibles au standard SQL sont en g�n�ral conformes au niveau des bases en vertu du fait que l'ensemble des fonctionnalit�s des niveaux interm�diaire et complet sont soit trop volumineux soit en conflit avec des comportements pr�c�demment implant�s.
Avec SQL99, le standard SQL a d�fini un vaste ensemble de fonctionnalit�s individuelles � la place des trois niveaux de fonctionnalit�s d�finis dans SQL-92 et dont l'efficacit� est discutable. Une grande partie repr�sente les fonctionnalit�s <<�centrales�>> que chaque impl�mentation de SQL doit fournir. Les fonctionnalit�s restantes sont purement optionnelles. Certaines de ces fonctionnalit�s optionnelles sont regroup�es au sein de <<�paquetages�>>. Une impl�mentation peut ainsi se dire conforme � certains paquetages, se d�clarant ainsi conforme � des groupes particuliers de fonctions.
Le standard SQL:2003 est aussi divis�e en un certain nombre de parties. Chacune est connu par un pseudonyme. Notez que ces parties ne sont pas num�rot�es dans l'ordre.
ISO/IEC 9075-1 Framework (SQL/Framework)
ISO/IEC 9075-2 Foundation (SQL/Foundation)
ISO/IEC 9075-3 Call Level Interface (SQL/CLI)
ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)
ISO/IEC 9075-9 Management of External Data (SQL/MED)
ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)
ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)
ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)
ISO/IEC 9075-14 XML-related specifications (SQL/XML)
PostgreSQL couvre les parties 1, 2 et 11. La partie 3 est similaire � l'interface ODBC et la partie 4 est similaire au langage de programmation PL/pgSQL mais une compatibilit� exacte n'est pas sp�cifiquement voulue ou v�rifi�e dans chaque cas.
PostgreSQL supporte la plupart des fonctionnalit�s majeures de SQL:2003. Des 164 fonctionnalit�s requises pour une compatibilit� Core, PostgreSQL est conforme sur au moins 150. De plus, il existe une longue liste de fonctionnalit�s suppl�mentaires support�es. Il est important de noter qu'au moment de l'�criture de ce texte, aucune version actuelle d'un quelconque syst�me de gestion de bases de donn�es n'indique une compatibilit� totale au Core de SQL:2003.
Dans les deux sections suivantes, nous fournissons une liste de ces fonctionnalit�s support�es par PostgreSQL suivies de la liste des fonctionnalit�s d�finies dans SQL:2003 qui ne sont pas encore prises en compte. Ces deux listes sont approximatives : certains d�tails mineurs peuvent ne pas �tre compatibles et certaines grandes parties d'une fonctionnalit� non support�e peuvent avoir �t� implant�es. Vous pourrez trouver des informations plus pr�cises sur ce qui fonctionne ou ne fonctionne pas dans les diff�rents chapitres principaux de la documentation.
Note�: Les codes de fonctionnalit�s contenant un tiret sont des sous-fonctionnalit�s. Le cas �ch�ant, si une sous-fonction n'est pas support�e, la fonctionnalit� de niveau sup�rieur sera d�finie comme non support�e m�me si d'autres sous-fonctions lui appartenant sont support�es.
| Identifiant | Paquetage | Description | Commentaire |
|---|---|---|---|
| B012 | Cœur | Int�gration de C | |
| B021 | SQL direct | ||
| E011 | Cœur | Types de donn�es num�riques | |
| E011-01 | Cœur | INTEGER and SMALLINT data types | |
| E011-02 | Cœur | REAL, DOUBLE PRECISON, and FLOAT data types | |
| E011-03 | Cœur | DECIMAL and NUMERIC data types | |
| E011-04 | Cœur | Arithmetic operators | |
| E011-05 | Cœur | Numeric comparison | |
| E011-06 | Cœur | Implicit casting among the numeric data types | |
| E021 | Cœur | Character data types | |
| E021-01 | Cœur | CHARACTER data type | |
| E021-02 | Cœur | CHARACTER VARYING data type | |
| E021-03 | Cœur | Character literals | |
| E021-04 | Cœur | CHARACTER_LENGTH function | |
| E021-05 | Cœur | OCTET_LENGTH function | |
| E021-06 | Cœur | SUBSTRING function | |
| E021-07 | Cœur | Character concatenation | |
| E021-08 | Cœur | UPPER and LOWER functions | |
| E021-09 | Cœur | TRIM function | |
| E021-10 | Cœur | Implicit casting among the character data types | |
| E021-11 | Cœur | POSITION function | |
| E021-12 | Cœur | Character comparison | |
| E031 | Cœur | Identifiers | |
| E031-01 | Cœur | Delimited identifiers | |
| E031-02 | Cœur | Lower case identifiers | |
| E031-03 | Cœur | Trailing underscore | |
| E051 | Cœur | Basic query specification | |
| E051-01 | Cœur | SELECT DISTINCT | |
| E051-02 | Cœur | GROUP BY clause | |
| E051-04 | Cœur | GROUP BY can contain columns not in <select list> | |
| E051-05 | Cœur | Select list items can be renamed | AS is required |
| E051-06 | Cœur | HAVING clause | |
| E051-07 | Cœur | Qualified * in select list | |
| E051-08 | Cœur | Correlation names in the FROM clause | |
| E051-09 | Cœur | Rename columns in the FROM clause | |
| E061 | Cœur | Basic predicates and search conditions | |
| E061-01 | Cœur | Comparison predicate | |
| E061-02 | Cœur | BETWEEN predicate | |
| E061-03 | Cœur | IN predicate with list of values | |
| E061-04 | Cœur | LIKE predicate | |
| E061-05 | Cœur | LIKE predicate ESCAPE clause | |
| E061-06 | Cœur | NULL predicate | |
| E061-07 | Cœur | Quantified comparison predicate | |
| E061-08 | Cœur | EXISTS predicate | |
| E061-09 | Cœur | Subqueries in comparison predicate | |
| E061-11 | Cœur | Subqueries in IN predicate | |
| E061-12 | Cœur | Subqueries in quantified comparison predicate | |
| E061-13 | Cœur | Correlated subqueries | |
| E061-14 | Cœur | Search condition | |
| E071 | Cœur | Basic query expressions | |
| E071-01 | Cœur | UNION DISTINCT table operator | |
| E071-02 | Cœur | UNION ALL table operator | |
| E071-03 | Cœur | EXCEPT DISTINCT table operator | |
| E071-05 | Cœur | Columns combined via table operators need not have exactly the same data type | |
| E071-06 | Cœur | Table operators in subqueries | |
| E081-01 | Cœur | SELECT privilege | |
| E081-02 | Cœur | DELETE privilege | |
| E081-03 | Cœur | INSERT privilege at the table level | |
| E081-04 | Cœur | UPDATE privilege at the table level | |
| E081-06 | Cœur | REFERENCES privilege at the table level | |
| E081-08 | Cœur | WITH GRANT OPTION | |
| E091 | Cœur | Set functions | |
| E091-01 | Cœur | AVG | |
| E091-02 | Cœur | COUNT | |
| E091-03 | Cœur | MAX | |
| E091-04 | Cœur | MIN | |
| E091-05 | Cœur | SUM | |
| E091-06 | Cœur | ALL quantifier | |
| E091-07 | Cœur | DISTINCT quantifier | |
| E101 | Cœur | Basic data manipulation | |
| E101-01 | Cœur | INSERT statement | |
| E101-03 | Cœur | Searched UPDATE statement | |
| E101-04 | Cœur | Searched DELETE statement | |
| E111 | Cœur | Single row SELECT statement | |
| E121-01 | Cœur | DECLARE CURSOR | |
| E121-02 | Cœur | ORDER BY columns need not be in select list | |
| E121-03 | Cœur | Value expressions in ORDER BY clause | |
| E121-08 | Cœur | CLOSE statement | |
| E121-10 | Cœur | FETCH statement implicit NEXT | |
| E121-17 | Cœur | WITH HOLD cursors | |
| E131 | Cœur | Null value support (NULLs in lieu of values) | |
| E141 | Cœur | Basic integrity constraints | |
| E141-01 | Cœur | NOT NULL constraints | |
| E141-02 | Cœur | UNIQUE constraints of NOT NULL columns | |
| E141-03 | Cœur | PRIMARY KEY constraints | |
| E141-04 | Cœur | Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action | |
| E141-06 | Cœur | CHECK constraints | |
| E141-07 | Cœur | Column defaults | |
| E141-08 | Cœur | NOT NULL inferred on PRIMARY KEY | |
| E141-10 | Cœur | Names in a foreign key can be specified in any order | |
| E151 | Cœur | Transaction support | |
| E151-01 | Cœur | COMMIT statement | |
| E151-02 | Cœur | ROLLBACK statement | |
| E152 | Cœur | Basic SET TRANSACTION statement | |
| E152-01 | Cœur | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause | |
| E152-02 | Cœur | SET TRANSACTION statement: READ ONLY and READ WRITE clauses | |
| E161 | Cœur | SQL comments using leading double minus | |
| F021 | Cœur | Basic information schema | |
| F021-01 | Cœur | COLUMNS view | |
| F021-02 | Cœur | TABLES view | |
| F021-03 | Cœur | VIEWS view | |
| F021-04 | Cœur | TABLE_CONSTRAINTS view | |
| F021-05 | Cœur | REFERENTIAL_CONSTRAINTS view | |
| F021-06 | Cœur | CHECK_CONSTRAINTS view | |
| F031 | Cœur | Basic schema manipulation | |
| F031-01 | Cœur | CREATE TABLE statement to create persistent base tables | |
| F031-02 | Cœur | CREATE VIEW statement | |
| F031-03 | Cœur | GRANT statement | |
| F031-04 | Cœur | ALTER TABLE statement: ADD COLUMN clause | |
| F031-13 | Cœur | DROP TABLE statement: RESTRICT clause | |
| F031-16 | Cœur | DROP VIEW statement: RESTRICT clause | |
| F031-19 | Cœur | REVOKE statement: RESTRICT clause | |
| F032 | CASCADE drop behavior | ||
| F033 | ALTER TABLE statement: DROP COLUMN clause | ||
| F034 | Extended REVOKE statement | ||
| F034-01 | REVOKE statement performed by other than the owner of a schema object | ||
| F034-02 | REVOKE statement: GRANT OPTION FOR clause | ||
| F034-03 | REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION | ||
| F041 | Cœur | Basic joined table | |
| F041-01 | Cœur | Inner join (but not necessarily the INNER keyword) | |
| F041-02 | Cœur | INNER keyword | |
| F041-03 | Cœur | LEFT OUTER JOIN | |
| F041-04 | Cœur | RIGHT OUTER JOIN | |
| F041-05 | Cœur | Outer joins can be nested | |
| F041-07 | Cœur | The inner table in a left or right outer join can also be used in an inner join | |
| F041-08 | Cœur | All comparison operators are supported (rather than just =) | |
| F051 | Cœur | Basic date and time | |
| F051-01 | Cœur | DATE data type (including support of DATE literal) | |
| F051-02 | Cœur | TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 | |
| F051-03 | Cœur | TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 | |
| F051-04 | Cœur | Comparison predicate on DATE, TIME, and TIMESTAMP data types | |
| F051-05 | Cœur | Explicit CAST between datetime types and character types | |
| F051-06 | Cœur | CURRENT_DATE | |
| F051-07 | Cœur | LOCALTIME | |
| F051-08 | Cœur | LOCALTIMESTAMP | |
| F052 | Enhanced datetime facilities | Intervals and datetime arithmetic | |
| F081 | Cœur | UNION and EXCEPT in views | |
| F111-02 | READ COMMITTED isolation level | ||
| F131 | Cœur | Grouped operations | |
| F131-01 | Cœur | WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views | |
| F131-02 | Cœur | Multiple tables supported in queries with grouped views | |
| F131-03 | Cœur | Set functions supported in queries with grouped views | |
| F131-04 | Cœur | Subqueries with GROUP BY and HAVING clauses and grouped views | |
| F131-05 | Cœur | Single row SELECT with GROUP BY and HAVING clauses and grouped views | |
| F171 | Multiple schemas per user | ||
| F191 | Enhanced integrity management | Referential delete actions | |
| F201 | Cœur | CAST function | |
| F221 | Cœur | Explicit defaults | |
| F222 | INSERT statement: DEFAULT VALUES clause | ||
| F231 | Privilege Tables | ||
| F231-01 | TABLE_PRIVILEGES view | ||
| F231-02 | COLUMN_PRIVILEGES view | ||
| F231-03 | USAGE_PRIVILEGES view | ||
| F251 | Domain support | ||
| F261 | Cœur | CASE expression | |
| F261-01 | Cœur | Simple CASE | |
| F261-02 | Cœur | Searched CASE | |
| F261-03 | Cœur | NULLIF | |
| F261-04 | Cœur | COALESCE | |
| F271 | Compound character literals | ||
| F281 | LIKE enhancements | ||
| F302 | OLAP facilities | INTERSECT table operator | |
| F302-01 | OLAP facilities | INTERSECT DISTINCT table operator | |
| F302-02 | OLAP facilities | INTERSECT ALL table operator | |
| F304 | OLAP facilities | EXCEPT ALL table operator | |
| F311-01 | Cœur | CREATE SCHEMA | |
| F311-02 | Cœur | CREATE TABLE for persistent base tables | |
| F311-03 | Cœur | CREATE VIEW | |
| F311-05 | Cœur | GRANT statement | |
| F321 | User authorization | ||
| F361 | Subprogram support | ||
| F381 | Extended schema manipulation | ||
| F381-01 | ALTER TABLE statement: ALTER COLUMN clause | ||
| F381-02 | ALTER TABLE statement: ADD CONSTRAINT clause | ||
| F381-03 | ALTER TABLE statement: DROP CONSTRAINT clause | ||
| F391 | Long identifiers | ||
| F401 | OLAP facilities | Extended joined table | |
| F401-01 | OLAP facilities | NATURAL JOIN | |
| F401-02 | OLAP facilities | FULL OUTER JOIN | |
| F401-03 | OLAP facilities | UNION JOIN | |
| F401-04 | OLAP facilities | CROSS JOIN | |
| F411 | Enhanced datetime facilities | Time zone specification | |
| F421 | National character | ||
| F431 | Read-only scrollable cursors | ||
| F431-01 | FETCH with explicit NEXT | ||
| F431-02 | FETCH FIRST | ||
| F431-03 | FETCH LAST | ||
| F431-04 | FETCH PRIOR | ||
| F431-05 | FETCH ABSOLUTE | ||
| F431-06 | FETCH RELATIVE | ||
| F441 | Extended set function support | ||
| F471 | Cœur | Scalar subquery values | |
| F481 | Cœur | Expanded NULL predicate | |
| F491 | Enhanced integrity management | Constraint management | |
| F501 | Cœur | Features and conformance views | |
| F501-01 | Cœur | SQL_FEATURES view | |
| F501-02 | Cœur | SQL_SIZING view | |
| F501-03 | Cœur | SQL_LANGUAGES view | |
| F502 | Enhanced documentation tables | ||
| F502-01 | SQL_SIZING_PROFILES view | ||
| F502-02 | SQL_IMPLEMENTATION_INFO view | ||
| F502-03 | SQL_PACKAGES view | ||
| F511 | BIT data type | ||
| F531 | Temporary tables | ||
| F555 | Enhanced datetime facilities | Enhanced seconds precision | |
| F561 | Full value expressions | ||
| F571 | Truth value tests | ||
| F591 | OLAP facilities | Derived tables | |
| F611 | Indicator data types | ||
| F651 | Catalog name qualifiers | ||
| F701 | Enhanced integrity management | Referential update actions | |
| F711 | ALTER domain | ||
| F761 | Session management | ||
| F771 | Connection management | ||
| F781 | Self-referencing operations | ||
| F791 | Insensitive cursors | ||
| F801 | Full set function | ||
| S071 | Enhanced object support | SQL paths in function and type name resolution | |
| S111 | Enhanced object support | ONLY in query expressions | |
| S211 | Enhanced object support, SQL/MM support | User-defined cast functions | |
| T031 | BOOLEAN data type | ||
| T141 | SIMILAR predicate | ||
| T151 | DISTINCT predicate | ||
| T171 | LIKE clause in table definition | ||
| T191 | Enhanced integrity management | Referential action RESTRICT | |
| T201 | Enhanced integrity management | Comparable data types for referential constraints | |
| T211-01 | Enhanced integrity management, Active database | Triggers activated on UPDATE, INSERT, or DELETE of one base table | |
| T211-02 | Enhanced integrity management, Active database | BEFORE triggers | |
| T211-03 | Enhanced integrity management, Active database | AFTER triggers | |
| T211-04 | Enhanced integrity management, Active database | FOR EACH ROW triggers | |
| T211-07 | Enhanced integrity management, Active database | TRIGGER privilege | |
| T212 | Enhanced integrity management | Enhanced trigger capability | |
| T231 | SENSITIVE cursors | ||
| T241 | START TRANSACTION statement | ||
| T312 | OVERLAY function | ||
| T321-01 | Cœur | User-defined functions with no overloading | |
| T321-03 | Cœur | Function invocation | |
| T321-06 | Cœur | ROUTINES view | |
| T321-07 | Cœur | PARAMETERS view | |
| T322 | PSM, SQL/MM support | Overloading of SQL-invoked functions and procedures | |
| T323 | Explicit security for external routines | ||
| T351 | Bracketed SQL comments (/*...*/ comments) | ||
| T441 | ABS and MOD functions | ||
| T501 | Enhanced EXISTS predicate | ||
| T551 | Optional key words for default syntax | ||
| T581 | Regular expression substring function | ||
| T591 | UNIQUE constraints of possibly NULL columns |
| Pr�c�dent | Sommaire | Suivant |
| Mots-cl� SQL | Niveau sup�rieur | Fonctionnalit�s non support�es |