{
    "mode": "man",
    "parameter": "SELECT",
    "section": "7",
    "url": "https://www.chedong.com/phpMan.php/man/SELECT/7/json",
    "generated": "2026-05-30T05:14:19Z",
    "synopsis": "[ WITH [ RECURSIVE ] withquery [, ...] ]\nSELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n[ { * | expression [ [ AS ] outputname ] } [, ...] ]\n[ FROM fromitem [, ...] ]\n[ WHERE condition ]\n[ GROUP BY [ ALL | DISTINCT ] groupingelement [, ...] ]\n[ HAVING condition ]\n[ WINDOW windowname AS ( windowdefinition ) [, ...] ]\n[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]\n[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]\n[ LIMIT { count | ALL } ]\n[ OFFSET start [ ROW | ROWS ] ]\n[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]\n[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF tablename [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]\nwhere fromitem can be one of:\n[ ONLY ] tablename [ * ] [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\n[ TABLESAMPLE samplingmethod ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]\n[ LATERAL ] ( select ) [ AS ] alias [ ( columnalias [, ...] ) ]\nwithqueryname [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\n[ LATERAL ] functionname ( [ argument [, ...] ] )\n[ WITH ORDINALITY ] [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\n[ LATERAL ] functionname ( [ argument [, ...] ] ) [ AS ] alias ( columndefinition [, ...] )\n[ LATERAL ] functionname ( [ argument [, ...] ] ) AS ( columndefinition [, ...] )\n[ LATERAL ] ROWS FROM( functionname ( [ argument [, ...] ] ) [ AS ( columndefinition [, ...] ) ] [, ...] )\n[ WITH ORDINALITY ] [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\nfromitem jointype fromitem { ON joincondition | USING ( joincolumn [, ...] ) [ AS joinusingalias ] }\nfromitem NATURAL jointype fromitem\nfromitem CROSS JOIN fromitem\nand groupingelement can be one of:\n( )\nexpression\n( expression [, ...] )\nROLLUP ( { expression | ( expression [, ...] ) } [, ...] )\nCUBE ( { expression | ( expression [, ...] ) } [, ...] )\nGROUPING SETS ( groupingelement [, ...] )\nand withquery is:\nwithqueryname [ ( columnname [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )\n[ SEARCH { BREADTH | DEPTH } FIRST BY columnname [, ...] SET searchseqcolname ]\n[ CYCLE columnname [, ...] SET cyclemarkcolname [ TO cyclemarkvalue DEFAULT cyclemarkdefault ] USING cyclepathcolname ]\nTABLE [ ONLY ] tablename [ * ]",
    "sections": {
        "NAME": {
            "content": "SELECT, TABLE, WITH - retrieve rows from a table or view\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "[ WITH [ RECURSIVE ] withquery [, ...] ]\nSELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n[ { * | expression [ [ AS ] outputname ] } [, ...] ]\n[ FROM fromitem [, ...] ]\n[ WHERE condition ]\n[ GROUP BY [ ALL | DISTINCT ] groupingelement [, ...] ]\n[ HAVING condition ]\n[ WINDOW windowname AS ( windowdefinition ) [, ...] ]\n[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]\n[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]\n[ LIMIT { count | ALL } ]\n[ OFFSET start [ ROW | ROWS ] ]\n[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]\n[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF tablename [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]\n\nwhere fromitem can be one of:\n\n[ ONLY ] tablename [ * ] [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\n[ TABLESAMPLE samplingmethod ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]\n[ LATERAL ] ( select ) [ AS ] alias [ ( columnalias [, ...] ) ]\nwithqueryname [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\n[ LATERAL ] functionname ( [ argument [, ...] ] )\n[ WITH ORDINALITY ] [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\n[ LATERAL ] functionname ( [ argument [, ...] ] ) [ AS ] alias ( columndefinition [, ...] )\n[ LATERAL ] functionname ( [ argument [, ...] ] ) AS ( columndefinition [, ...] )\n[ LATERAL ] ROWS FROM( functionname ( [ argument [, ...] ] ) [ AS ( columndefinition [, ...] ) ] [, ...] )\n[ WITH ORDINALITY ] [ [ AS ] alias [ ( columnalias [, ...] ) ] ]\nfromitem jointype fromitem { ON joincondition | USING ( joincolumn [, ...] ) [ AS joinusingalias ] }\nfromitem NATURAL jointype fromitem\nfromitem CROSS JOIN fromitem\n\nand groupingelement can be one of:\n\n( )\nexpression\n( expression [, ...] )\nROLLUP ( { expression | ( expression [, ...] ) } [, ...] )\nCUBE ( { expression | ( expression [, ...] ) } [, ...] )\nGROUPING SETS ( groupingelement [, ...] )\n\nand withquery is:\n\nwithqueryname [ ( columnname [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )\n[ SEARCH { BREADTH | DEPTH } FIRST BY columnname [, ...] SET searchseqcolname ]\n[ CYCLE columnname [, ...] SET cyclemarkcolname [ TO cyclemarkvalue DEFAULT cyclemarkdefault ] USING cyclepathcolname ]\n\nTABLE [ ONLY ] tablename [ * ]\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "SELECT retrieves rows from zero or more tables. The general processing of SELECT is as\nfollows:\n\n1. All queries in the WITH list are computed. These effectively serve as temporary tables\nthat can be referenced in the FROM list. A WITH query that is referenced more than once\nin FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. (See\nWITH Clause below.)\n\n2. All elements in the FROM list are computed. (Each element in the FROM list is a real or\nvirtual table.) If more than one element is specified in the FROM list, they are\ncross-joined together. (See FROM Clause below.)\n\n3. If the WHERE clause is specified, all rows that do not satisfy the condition are\neliminated from the output. (See WHERE Clause below.)\n\n4. If the GROUP BY clause is specified, or if there are aggregate function calls, the output\nis combined into groups of rows that match on one or more values, and the results of\naggregate functions are computed. If the HAVING clause is present, it eliminates groups\nthat do not satisfy the given condition. (See GROUP BY Clause and HAVING Clause below.)\nAlthough query output columns are nominally computed in the next step, they can also be\nreferenced (by name or ordinal number) in the GROUP BY clause.\n\n5. The actual output rows are computed using the SELECT output expressions for each selected\nrow or row group. (See SELECT List below.)\n\n6. SELECT DISTINCT eliminates duplicate rows from the result.  SELECT DISTINCT ON eliminates\nrows that match on all the specified expressions.  SELECT ALL (the default) will return\nall candidate rows, including duplicates. (See DISTINCT Clause below.)\n\n7. Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one SELECT\nstatement can be combined to form a single result set. The UNION operator returns all\nrows that are in one or both of the result sets. The INTERSECT operator returns all rows\nthat are strictly in both result sets. The EXCEPT operator returns the rows that are in\nthe first result set but not in the second. In all three cases, duplicate rows are\neliminated unless ALL is specified. The noise word DISTINCT can be added to explicitly\nspecify eliminating duplicate rows. Notice that DISTINCT is the default behavior here,\neven though ALL is the default for SELECT itself. (See UNION Clause, INTERSECT Clause,\nand EXCEPT Clause below.)\n\n8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order.\nIf ORDER BY is not given, the rows are returned in whatever order the system finds\nfastest to produce. (See ORDER BY Clause below.)\n\n9. If the LIMIT (or FETCH FIRST) or OFFSET clause is specified, the SELECT statement only\nreturns a subset of the result rows. (See LIMIT Clause below.)\n\n10. If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE is specified, the SELECT\nstatement locks the selected rows against concurrent updates. (See The Locking Clause\nbelow.)\n\nYou must have SELECT privilege on each column used in a SELECT command. The use of FOR NO KEY\nUPDATE, FOR UPDATE, FOR SHARE or FOR KEY SHARE requires UPDATE privilege as well (for at\nleast one column of each table so selected).\n",
            "subsections": []
        },
        "PARAMETERS": {
            "content": "",
            "subsections": [
                {
                    "name": "WITH Clause",
                    "content": "The WITH clause allows you to specify one or more subqueries that can be referenced by name\nin the primary query. The subqueries effectively act as temporary tables or views for the\nduration of the primary query. Each subquery can be a SELECT, TABLE, VALUES, INSERT, UPDATE\nor DELETE statement. When writing a data-modifying statement (INSERT, UPDATE or DELETE) in\nWITH, it is usual to include a RETURNING clause. It is the output of RETURNING, not the\nunderlying table that the statement modifies, that forms the temporary table that is read by\nthe primary query. If RETURNING is omitted, the statement is still executed, but it produces\nno output so it cannot be referenced as a table by the primary query.\n\nA name (without schema qualification) must be specified for each WITH query. Optionally, a\nlist of column names can be specified; if this is omitted, the column names are inferred from\nthe subquery.\n\nIf RECURSIVE is specified, it allows a SELECT subquery to reference itself by name. Such a\nsubquery must have the form\n\nnonrecursiveterm UNION [ ALL | DISTINCT ] recursiveterm\n\nwhere the recursive self-reference must appear on the right-hand side of the UNION. Only one\nrecursive self-reference is permitted per query. Recursive data-modifying statements are not\nsupported, but you can use the results of a recursive SELECT query in a data-modifying\nstatement. See Section 7.8 for an example.\n\nAnother effect of RECURSIVE is that WITH queries need not be ordered: a query can reference\nanother one that is later in the list. (However, circular references, or mutual recursion,\nare not implemented.) Without RECURSIVE, WITH queries can only reference sibling WITH queries\nthat are earlier in the WITH list.\n\nWhen there are multiple queries in the WITH clause, RECURSIVE should be written only once,\nimmediately after WITH. It applies to all queries in the WITH clause, though it has no effect\non queries that do not use recursion or forward references.\n\nThe optional SEARCH clause computes a search sequence column that can be used for ordering\nthe results of a recursive query in either breadth-first or depth-first order. The supplied\ncolumn name list specifies the row key that is to be used for keeping track of visited rows.\nA column named searchseqcolname will be added to the result column list of the WITH query.\nThis column can be ordered by in the outer query to achieve the respective ordering. See\nSection 7.8.2.1 for examples.\n\nThe optional CYCLE clause is used to detect cycles in recursive queries. The supplied column\nname list specifies the row key that is to be used for keeping track of visited rows. A\ncolumn named cyclemarkcolname will be added to the result column list of the WITH query.\nThis column will be set to cyclemarkvalue when a cycle has been detected, else to\ncyclemarkdefault. Furthermore, processing of the recursive union will stop when a cycle has\nbeen detected.  cyclemarkvalue and cyclemarkdefault must be constants and they must be\ncoercible to a common data type, and the data type must have an inequality operator. (The SQL\nstandard requires that they be Boolean constants or character strings, but PostgreSQL does\nnot require that.) By default, TRUE and FALSE (of type boolean) are used. Furthermore, a\ncolumn named cyclepathcolname will be added to the result column list of the WITH query.\nThis column is used internally for tracking visited rows. See Section 7.8.2.2 for examples.\n\nBoth the SEARCH and the CYCLE clause are only valid for recursive WITH queries. The\nwithquery must be a UNION (or UNION ALL) of two SELECT (or equivalent) commands (no nested\nUNIONs). If both clauses are used, the column added by the SEARCH clause appears before the\ncolumns added by the CYCLE clause.\n\nThe primary query and the WITH queries are all (notionally) executed at the same time. This\nimplies that the effects of a data-modifying statement in WITH cannot be seen from other\nparts of the query, other than by reading its RETURNING output. If two such data-modifying\nstatements attempt to modify the same row, the results are unspecified.\n\nA key property of WITH queries is that they are normally evaluated only once per execution of\nthe primary query, even if the primary query refers to them more than once. In particular,\ndata-modifying statements are guaranteed to be executed once and only once, regardless of\nwhether the primary query reads all or any of their output.\n\nHowever, a WITH query can be marked NOT MATERIALIZED to remove this guarantee. In that case,\nthe WITH query can be folded into the primary query much as though it were a simple\nsub-SELECT in the primary query's FROM clause. This results in duplicate computations if the\nprimary query refers to that WITH query more than once; but if each such use requires only a\nfew rows of the WITH query's total output, NOT MATERIALIZED can provide a net savings by\nallowing the queries to be optimized jointly.  NOT MATERIALIZED is ignored if it is attached\nto a WITH query that is recursive or is not side-effect-free (i.e., is not a plain SELECT\ncontaining no volatile functions).\n\nBy default, a side-effect-free WITH query is folded into the primary query if it is used\nexactly once in the primary query's FROM clause. This allows joint optimization of the two\nquery levels in situations where that should be semantically invisible. However, such folding\ncan be prevented by marking the WITH query as MATERIALIZED. That might be useful, for\nexample, if the WITH query is being used as an optimization fence to prevent the planner from\nchoosing a bad plan.  PostgreSQL versions before v12 never did such folding, so queries\nwritten for older versions might rely on WITH to act as an optimization fence.\n\nSee Section 7.8 for additional information.\n"
                },
                {
                    "name": "FROM Clause",
                    "content": "The FROM clause specifies one or more source tables for the SELECT. If multiple sources are\nspecified, the result is the Cartesian product (cross join) of all the sources. But usually\nqualification conditions are added (via WHERE) to restrict the returned rows to a small\nsubset of the Cartesian product.\n\nThe FROM clause can contain the following elements:\n\ntablename\nThe name (optionally schema-qualified) of an existing table or view. If ONLY is specified\nbefore the table name, only that table is scanned. If ONLY is not specified, the table\nand all its descendant tables (if any) are scanned. Optionally, * can be specified after\nthe table name to explicitly indicate that descendant tables are included.\n\nalias\nA substitute name for the FROM item containing the alias. An alias is used for brevity or\nto eliminate ambiguity for self-joins (where the same table is scanned multiple times).\nWhen an alias is provided, it completely hides the actual name of the table or function;\nfor example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item\nas f not foo. If an alias is written, a column alias list can also be written to provide\nsubstitute names for one or more columns of the table.\n\nTABLESAMPLE samplingmethod ( argument [, ...] ) [ REPEATABLE ( seed ) ]\nA TABLESAMPLE clause after a tablename indicates that the specified samplingmethod\nshould be used to retrieve a subset of the rows in that table. This sampling precedes the\napplication of any other filters such as WHERE clauses. The standard PostgreSQL\ndistribution includes two sampling methods, BERNOULLI and SYSTEM, and other sampling\nmethods can be installed in the database via extensions.\n\nThe BERNOULLI and SYSTEM sampling methods each accept a single argument which is the\nfraction of the table to sample, expressed as a percentage between 0 and 100. This\nargument can be any real-valued expression. (Other sampling methods might accept more or\ndifferent arguments.) These two methods each return a randomly-chosen sample of the table\nthat will contain approximately the specified percentage of the table's rows. The\nBERNOULLI method scans the whole table and selects or ignores individual rows\nindependently with the specified probability. The SYSTEM method does block-level sampling\nwith each block having the specified chance of being selected; all rows in each selected\nblock are returned. The SYSTEM method is significantly faster than the BERNOULLI method\nwhen small sampling percentages are specified, but it may return a less-random sample of\nthe table as a result of clustering effects.\n\nThe optional REPEATABLE clause specifies a seed number or expression to use for\ngenerating random numbers within the sampling method. The seed value can be any non-null\nfloating-point value. Two queries that specify the same seed and argument values will\nselect the same sample of the table, if the table has not been changed meanwhile. But\ndifferent seed values will usually produce different samples. If REPEATABLE is not given\nthen a new random sample is selected for each query, based upon a system-generated seed.\nNote that some add-on sampling methods do not accept REPEATABLE, and will always produce\nnew samples on each use.\n\nselect\nA sub-SELECT can appear in the FROM clause. This acts as though its output were created\nas a temporary table for the duration of this single SELECT command. Note that the\nsub-SELECT must be surrounded by parentheses, and an alias must be provided for it. A\nVALUES command can also be used here.\n\nwithqueryname\nA WITH query is referenced by writing its name, just as though the query's name were a\ntable name. (In fact, the WITH query hides any real table of the same name for the\npurposes of the primary query. If necessary, you can refer to a real table of the same\nname by schema-qualifying the table's name.) An alias can be provided in the same way as\nfor a table.\n\nfunctionname\nFunction calls can appear in the FROM clause. (This is especially useful for functions\nthat return result sets, but any function can be used.) This acts as though the\nfunction's output were created as a temporary table for the duration of this single\nSELECT command. If the function's result type is composite (including the case of a\nfunction with multiple OUT parameters), each attribute becomes a separate column in the\nimplicit table.\n\nWhen the optional WITH ORDINALITY clause is added to the function call, an additional\ncolumn of type bigint will be appended to the function's result column(s). This column\nnumbers the rows of the function's result set, starting from 1. By default, this column\nis named ordinality.\n\nAn alias can be provided in the same way as for a table. If an alias is written, a column\nalias list can also be written to provide substitute names for one or more attributes of\nthe function's composite return type, including the ordinality column if present.\n\nMultiple function calls can be combined into a single FROM-clause item by surrounding\nthem with ROWS FROM( ... ). The output of such an item is the concatenation of the first\nrow from each function, then the second row from each function, etc. If some of the\nfunctions produce fewer rows than others, null values are substituted for the missing\ndata, so that the total number of rows returned is always the same as for the function\nthat produced the most rows.\n\nIf the function has been defined as returning the record data type, then an alias or the\nkey word AS must be present, followed by a column definition list in the form (\ncolumnname datatype [, ... ]). The column definition list must match the actual number\nand types of columns returned by the function.\n\nWhen using the ROWS FROM( ... ) syntax, if one of the functions requires a column\ndefinition list, it's preferred to put the column definition list after the function call\ninside ROWS FROM( ... ). A column definition list can be placed after the ROWS FROM( ...\n) construct only if there's just a single function and no WITH ORDINALITY clause.\n\nTo use ORDINALITY together with a column definition list, you must use the ROWS FROM( ...\n) syntax and put the column definition list inside ROWS FROM( ... ).\n\njointype\nOne of\n\n•   [ INNER ] JOIN\n\n•   LEFT [ OUTER ] JOIN\n\n•   RIGHT [ OUTER ] JOIN\n\n•   FULL [ OUTER ] JOIN\n\nFor the INNER and OUTER join types, a join condition must be specified, namely exactly\none of ON joincondition, USING (joincolumn [, ...]), or NATURAL. See below for the\nmeaning.\n\nA JOIN clause combines two FROM items, which for convenience we will refer to as\n“tables”, though in reality they can be any type of FROM item. Use parentheses if\nnecessary to determine the order of nesting. In the absence of parentheses, JOINs nest\nleft-to-right. In any case JOIN binds more tightly than the commas separating FROM-list\nitems. All the JOIN options are just a notational convenience, since they do nothing you\ncouldn't do with plain FROM and WHERE.\n\nLEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e., all combined\nrows that pass its join condition), plus one copy of each row in the left-hand table for\nwhich there was no right-hand row that passed the join condition. This left-hand row is\nextended to the full width of the joined table by inserting null values for the\nright-hand columns. Note that only the JOIN clause's own condition is considered while\ndeciding which rows have matches. Outer conditions are applied afterwards.\n\nConversely, RIGHT OUTER JOIN returns all the joined rows, plus one row for each unmatched\nright-hand row (extended with nulls on the left). This is just a notational convenience,\nsince you could convert it to a LEFT OUTER JOIN by switching the left and right tables.\n\nFULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand\nrow (extended with nulls on the right), plus one row for each unmatched right-hand row\n(extended with nulls on the left).\n\nON joincondition\njoincondition is an expression resulting in a value of type boolean (similar to a WHERE\nclause) that specifies which rows in a join are considered to match.\n\nUSING ( joincolumn [, ...] ) [ AS joinusingalias ]\nA clause of the form USING ( a, b, ... ) is shorthand for ON lefttable.a = righttable.a\nAND lefttable.b = righttable.b .... Also, USING implies that only one of each pair of\nequivalent columns will be included in the join output, not both.\n\nIf a joinusingalias name is specified, it provides a table alias for the join columns.\nOnly the join columns listed in the USING clause are addressable by this name. Unlike a\nregular alias, this does not hide the names of the joined tables from the rest of the\nquery. Also unlike a regular alias, you cannot write a column alias list — the output\nnames of the join columns are the same as they appear in the USING list.\n\nNATURAL\nNATURAL is shorthand for a USING list that mentions all columns in the two tables that\nhave matching names. If there are no common column names, NATURAL is equivalent to ON\nTRUE.\n\nCROSS JOIN\nCROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by\nqualification. They produce a simple Cartesian product, the same result as you get from\nlisting the two tables at the top level of FROM, but restricted by the join condition (if\nany).\n\nLATERAL\nThe LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to\nrefer to columns of FROM items that appear before it in the FROM list. (Without LATERAL,\neach sub-SELECT is evaluated independently and so cannot cross-reference any other FROM\nitem.)\n\nLATERAL can also precede a function-call FROM item, but in this case it is a noise word,\nbecause the function expression can refer to earlier FROM items in any case.\n\nA LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the\nlatter case it can also refer to any items that are on the left-hand side of a JOIN that\nit is on the right-hand side of.\n\nWhen a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for\neach row of the FROM item providing the cross-referenced column(s), or set of rows of\nmultiple FROM items providing the columns, the LATERAL item is evaluated using that row\nor row set's values of the columns. The resulting row(s) are joined as usual with the\nrows they were computed from. This is repeated for each row or set of rows from the\ncolumn source table(s).\n\nThe column source table(s) must be INNER or LEFT joined to the LATERAL item, else there\nwould not be a well-defined set of rows from which to compute each set of rows for the\nLATERAL item. Thus, although a construct such as X RIGHT JOIN LATERAL Y is syntactically\nvalid, it is not actually allowed for Y to reference X.\n"
                },
                {
                    "name": "WHERE Clause",
                    "content": "The optional WHERE clause has the general form\n\nWHERE condition\n\nwhere condition is any expression that evaluates to a result of type boolean. Any row that\ndoes not satisfy this condition will be eliminated from the output. A row satisfies the\ncondition if it returns true when the actual row values are substituted for any variable\nreferences.\n"
                },
                {
                    "name": "GROUP BY Clause",
                    "content": "The optional GROUP BY clause has the general form\n\nGROUP BY [ ALL | DISTINCT ] groupingelement [, ...]\n\nGROUP BY will condense into a single row all selected rows that share the same values for the\ngrouped expressions. An expression used inside a groupingelement can be an input column\nname, or the name or ordinal number of an output column (SELECT list item), or an arbitrary\nexpression formed from input-column values. In case of ambiguity, a GROUP BY name will be\ninterpreted as an input-column name rather than an output column name.\n\nIf any of GROUPING SETS, ROLLUP or CUBE are present as grouping elements, then the GROUP BY\nclause as a whole defines some number of independent grouping sets. The effect of this is\nequivalent to constructing a UNION ALL between subqueries with the individual grouping sets\nas their GROUP BY clauses. The optional DISTINCT clause removes duplicate sets before\nprocessing; it does not transform the UNION ALL into a UNION DISTINCT. For further details on\nthe handling of grouping sets see Section 7.2.4.\n\nAggregate functions, if any are used, are computed across all rows making up each group,\nproducing a separate value for each group. (If there are aggregate functions but no GROUP BY\nclause, the query is treated as having a single group comprising all the selected rows.) The\nset of rows fed to each aggregate function can be further filtered by attaching a FILTER\nclause to the aggregate function call; see Section 4.2.7 for more information. When a FILTER\nclause is present, only those rows matching it are included in the input to that aggregate\nfunction.\n\nWhen GROUP BY is present, or any aggregate functions are present, it is not valid for the\nSELECT list expressions to refer to ungrouped columns except within aggregate functions or\nwhen the ungrouped column is functionally dependent on the grouped columns, since there would\notherwise be more than one possible value to return for an ungrouped column. A functional\ndependency exists if the grouped columns (or a subset thereof) are the primary key of the\ntable containing the ungrouped column.\n\nKeep in mind that all aggregate functions are evaluated before evaluating any “scalar”\nexpressions in the HAVING clause or SELECT list. This means that, for example, a CASE\nexpression cannot be used to skip evaluation of an aggregate function; see Section 4.2.14.\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\nwith GROUP BY.\n"
                },
                {
                    "name": "HAVING Clause",
                    "content": "The optional HAVING clause has the general form\n\nHAVING condition\n\nwhere condition is the same as specified for the WHERE clause.\n\nHAVING eliminates group rows that do not satisfy the condition.  HAVING is different from\nWHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters\ngroup rows created by GROUP BY. Each column referenced in condition must unambiguously\nreference a grouping column, unless the reference appears within an aggregate function or the\nungrouped column is functionally dependent on the grouping columns.\n\nThe presence of HAVING turns a query into a grouped query even if there is no GROUP BY\nclause. This is the same as what happens when the query contains aggregate functions but no\nGROUP BY clause. All the selected rows are considered to form a single group, and the SELECT\nlist and HAVING clause can only reference table columns from within aggregate functions. Such\na query will emit a single row if the HAVING condition is true, zero rows if it is not true.\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\nwith HAVING.\n"
                },
                {
                    "name": "WINDOW Clause",
                    "content": "The optional WINDOW clause has the general form\n\nWINDOW windowname AS ( windowdefinition ) [, ...]\n\nwhere windowname is a name that can be referenced from OVER clauses or subsequent window\ndefinitions, and windowdefinition is\n\n[ existingwindowname ]\n[ PARTITION BY expression [, ...] ]\n[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]\n[ frameclause ]\n\nIf an existingwindowname is specified it must refer to an earlier entry in the WINDOW list;\nthe new window copies its partitioning clause from that entry, as well as its ordering clause\nif any. In this case the new window cannot specify its own PARTITION BY clause, and it can\nspecify ORDER BY only if the copied window does not have one. The new window always uses its\nown frame clause; the copied window must not specify a frame clause.\n\nThe elements of the PARTITION BY list are interpreted in much the same fashion as elements of\na GROUP BY clause, except that they are always simple expressions and never the name or\nnumber of an output column. Another difference is that these expressions can contain\naggregate function calls, which are not allowed in a regular GROUP BY clause. They are\nallowed here because windowing occurs after grouping and aggregation.\n\nSimilarly, the elements of the ORDER BY list are interpreted in much the same fashion as\nelements of a statement-level ORDER BY clause, except that the expressions are always taken\nas simple expressions and never the name or number of an output column.\n\nThe optional frameclause defines the window frame for window functions that depend on the\nframe (not all do). The window frame is a set of related rows for each row of the query\n(called the current row). The frameclause can be one of\n\n{ RANGE | ROWS | GROUPS } framestart [ frameexclusion ]\n{ RANGE | ROWS | GROUPS } BETWEEN framestart AND frameend [ frameexclusion ]\n\nwhere framestart and frameend can be one of\n\nUNBOUNDED PRECEDING\noffset PRECEDING\nCURRENT ROW\noffset FOLLOWING\nUNBOUNDED FOLLOWING\n\nand frameexclusion can be one of\n\nEXCLUDE CURRENT ROW\nEXCLUDE GROUP\nEXCLUDE TIES\nEXCLUDE NO OTHERS\n\nIf frameend is omitted it defaults to CURRENT ROW. Restrictions are that framestart cannot\nbe UNBOUNDED FOLLOWING, frameend cannot be UNBOUNDED PRECEDING, and the frameend choice\ncannot appear earlier in the above list of framestart and frameend options than the\nframestart choice does — for example RANGE BETWEEN CURRENT ROW AND offset PRECEDING is not\nallowed.\n\nThe default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN\nUNBOUNDED PRECEDING AND CURRENT ROW; it sets the frame to be all rows from the partition\nstart up through the current row's last peer (a row that the window's ORDER BY clause\nconsiders equivalent to the current row; all rows are peers if there is no ORDER BY). In\ngeneral, UNBOUNDED PRECEDING means that the frame starts with the first row of the partition,\nand similarly UNBOUNDED FOLLOWING means that the frame ends with the last row of the\npartition, regardless of RANGE, ROWS or GROUPS mode. In ROWS mode, CURRENT ROW means that the\nframe starts or ends with the current row; but in RANGE or GROUPS mode it means that the\nframe starts or ends with the current row's first or last peer in the ORDER BY ordering. The\noffset PRECEDING and offset FOLLOWING options vary in meaning depending on the frame mode. In\nROWS mode, the offset is an integer indicating that the frame starts or ends that many rows\nbefore or after the current row. In GROUPS mode, the offset is an integer indicating that the\nframe starts or ends that many peer groups before or after the current row's peer group,\nwhere a peer group is a group of rows that are equivalent according to the window's ORDER BY\nclause. In RANGE mode, use of an offset option requires that there be exactly one ORDER BY\ncolumn in the window definition. Then the frame contains those rows whose ordering column\nvalue is no more than offset less than (for PRECEDING) or more than (for FOLLOWING) the\ncurrent row's ordering column value. In these cases the data type of the offset expression\ndepends on the data type of the ordering column. For numeric ordering columns it is typically\nof the same type as the ordering column, but for datetime ordering columns it is an interval.\nIn all these cases, the value of the offset must be non-null and non-negative. Also, while\nthe offset does not have to be a simple constant, it cannot contain variables, aggregate\nfunctions, or window functions.\n\nThe frameexclusion option allows rows around the current row to be excluded from the frame,\neven if they would be included according to the frame start and frame end options.  EXCLUDE\nCURRENT ROW excludes the current row from the frame.  EXCLUDE GROUP excludes the current row\nand its ordering peers from the frame.  EXCLUDE TIES excludes any peers of the current row\nfrom the frame, but not the current row itself.  EXCLUDE NO OTHERS simply specifies\nexplicitly the default behavior of not excluding the current row or its peers.\n\nBeware that the ROWS mode can produce unpredictable results if the ORDER BY ordering does not\norder the rows uniquely. The RANGE and GROUPS modes are designed to ensure that rows that are\npeers in the ORDER BY ordering are treated alike: all rows of a given peer group will be in\nthe frame or excluded from it.\n\nThe purpose of a WINDOW clause is to specify the behavior of window functions appearing in\nthe query's SELECT list or ORDER BY clause. These functions can reference the WINDOW clause\nentries by name in their OVER clauses. A WINDOW clause entry does not have to be referenced\nanywhere, however; if it is not used in the query it is simply ignored. It is possible to use\nwindow functions without any WINDOW clause at all, since a window function call can specify\nits window definition directly in its OVER clause. However, the WINDOW clause saves typing\nwhen the same window definition is needed for more than one window function.\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\nwith WINDOW.\n\nWindow functions are described in detail in Section 3.5, Section 4.2.8, and Section 7.2.5.\n"
                },
                {
                    "name": "SELECT List",
                    "content": "The SELECT list (between the key words SELECT and FROM) specifies expressions that form the\noutput rows of the SELECT statement. The expressions can (and usually do) refer to columns\ncomputed in the FROM clause.\n\nJust as in a table, every output column of a SELECT has a name. In a simple SELECT this name\nis just used to label the column for display, but when the SELECT is a sub-query of a larger\nquery, the name is seen by the larger query as the column name of the virtual table produced\nby the sub-query. To specify the name to use for an output column, write AS outputname after\nthe column's expression. (You can omit AS, but only if the desired output name does not match\nany PostgreSQL keyword (see Appendix C). For protection against possible future keyword\nadditions, it is recommended that you always either write AS or double-quote the output\nname.) If you do not specify a column name, a name is chosen automatically by PostgreSQL. If\nthe column's expression is a simple column reference then the chosen name is the same as that\ncolumn's name. In more complex cases a function or type name may be used, or the system may\nfall back on a generated name such as ?column?.\n\nAn output column's name can be used to refer to the column's value in ORDER BY and GROUP BY\nclauses, but not in the WHERE or HAVING clauses; there you must write out the expression\ninstead.\n\nInstead of an expression, * can be written in the output list as a shorthand for all the\ncolumns of the selected rows. Also, you can write tablename.*  as a shorthand for the\ncolumns coming from just that table. In these cases it is not possible to specify new names\nwith AS; the output column names will be the same as the table columns' names.\n\nAccording to the SQL standard, the expressions in the output list should be computed before\napplying DISTINCT, ORDER BY, or LIMIT. This is obviously necessary when using DISTINCT, since\notherwise it's not clear what values are being made distinct. However, in many cases it is\nconvenient if output expressions are computed after ORDER BY and LIMIT; particularly if the\noutput list contains any volatile or expensive functions. With that behavior, the order of\nfunction evaluations is more intuitive and there will not be evaluations corresponding to\nrows that never appear in the output.  PostgreSQL will effectively evaluate output\nexpressions after sorting and limiting, so long as those expressions are not referenced in\nDISTINCT, ORDER BY or GROUP BY. (As a counterexample, SELECT f(x) FROM tab ORDER BY 1 clearly\nmust evaluate f(x) before sorting.) Output expressions that contain set-returning functions\nare effectively evaluated after sorting and before limiting, so that LIMIT will act to cut\noff the output from a set-returning function.\n\nNote\nPostgreSQL versions before 9.6 did not provide any guarantees about the timing of\nevaluation of output expressions versus sorting and limiting; it depended on the form of\nthe chosen query plan.\n"
                },
                {
                    "name": "DISTINCT Clause",
                    "content": "If SELECT DISTINCT is specified, all duplicate rows are removed from the result set (one row\nis kept from each group of duplicates).  SELECT ALL specifies the opposite: all rows are\nkept; that is the default.\n\nSELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where\nthe given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using\nthe same rules as for ORDER BY (see above). Note that the “first row” of each set is\nunpredictable unless ORDER BY is used to ensure that the desired row appears first. For\nexample:\n\nSELECT DISTINCT ON (location) location, time, report\nFROM weatherreports\nORDER BY location, time DESC;\n\nretrieves the most recent weather report for each location. But if we had not used ORDER BY\nto force descending order of time values for each location, we'd have gotten a report from an\nunpredictable time for each location.\n\nThe DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY\nclause will normally contain additional expression(s) that determine the desired precedence\nof rows within each DISTINCT ON group.\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\nwith DISTINCT.\n"
                },
                {
                    "name": "UNION Clause",
                    "content": "The UNION clause has this general form:\n\nselectstatement UNION [ ALL | DISTINCT ] selectstatement\n\nselectstatement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR\nUPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a\nsubexpression if it is enclosed in parentheses. Without parentheses, these clauses will be\ntaken to apply to the result of the UNION, not to its right-hand input expression.)\n\nThe UNION operator computes the set union of the rows returned by the involved SELECT\nstatements. A row is in the set union of two result sets if it appears in at least one of the\nresult sets. The two SELECT statements that represent the direct operands of the UNION must\nproduce the same number of columns, and corresponding columns must be of compatible data\ntypes.\n\nThe result of UNION does not contain any duplicate rows unless the ALL option is specified.\nALL prevents elimination of duplicates. (Therefore, UNION ALL is usually significantly\nquicker than UNION; use ALL when you can.)  DISTINCT can be written to explicitly specify the\ndefault behavior of eliminating duplicate rows.\n\nMultiple UNION operators in the same SELECT statement are evaluated left to right, unless\notherwise indicated by parentheses.\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\neither for a UNION result or for any input of a UNION.\n"
                },
                {
                    "name": "INTERSECT Clause",
                    "content": "The INTERSECT clause has this general form:\n\nselectstatement INTERSECT [ ALL | DISTINCT ] selectstatement\n\nselectstatement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR\nUPDATE, FOR SHARE, or FOR KEY SHARE clause.\n\nThe INTERSECT operator computes the set intersection of the rows returned by the involved\nSELECT statements. A row is in the intersection of two result sets if it appears in both\nresult sets.\n\nThe result of INTERSECT does not contain any duplicate rows unless the ALL option is\nspecified. With ALL, a row that has m duplicates in the left table and n duplicates in the\nright table will appear min(m,n) times in the result set.  DISTINCT can be written to\nexplicitly specify the default behavior of eliminating duplicate rows.\n\nMultiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless\nparentheses dictate otherwise.  INTERSECT binds more tightly than UNION. That is, A UNION B\nINTERSECT C will be read as A UNION (B INTERSECT C).\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\neither for an INTERSECT result or for any input of an INTERSECT.\n"
                },
                {
                    "name": "EXCEPT Clause",
                    "content": "The EXCEPT clause has this general form:\n\nselectstatement EXCEPT [ ALL | DISTINCT ] selectstatement\n\nselectstatement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR\nUPDATE, FOR SHARE, or FOR KEY SHARE clause.\n\nThe EXCEPT operator computes the set of rows that are in the result of the left SELECT\nstatement but not in the result of the right one.\n\nThe result of EXCEPT does not contain any duplicate rows unless the ALL option is specified.\nWith ALL, a row that has m duplicates in the left table and n duplicates in the right table\nwill appear max(m-n,0) times in the result set.  DISTINCT can be written to explicitly\nspecify the default behavior of eliminating duplicate rows.\n\nMultiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless\nparentheses dictate otherwise.  EXCEPT binds at the same level as UNION.\n\nCurrently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified\neither for an EXCEPT result or for any input of an EXCEPT.\n"
                },
                {
                    "name": "ORDER BY Clause",
                    "content": "The optional ORDER BY clause has this general form:\n\nORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]\n\nThe ORDER BY clause causes the result rows to be sorted according to the specified\nexpression(s). If two rows are equal according to the leftmost expression, they are compared\naccording to the next expression and so on. If they are equal according to all specified\nexpressions, they are returned in an implementation-dependent order.\n\nEach expression can be the name or ordinal number of an output column (SELECT list item), or\nit can be an arbitrary expression formed from input-column values.\n\nThe ordinal number refers to the ordinal (left-to-right) position of the output column. This\nfeature makes it possible to define an ordering on the basis of a column that does not have a\nunique name. This is never absolutely necessary because it is always possible to assign a\nname to an output column using the AS clause.\n\nIt is also possible to use arbitrary expressions in the ORDER BY clause, including columns\nthat do not appear in the SELECT output list. Thus the following statement is valid:\n\nSELECT name FROM distributors ORDER BY code;\n\nA limitation of this feature is that an ORDER BY clause applying to the result of a UNION,\nINTERSECT, or EXCEPT clause can only specify an output column name or number, not an\nexpression.\n\nIf an ORDER BY expression is a simple name that matches both an output column name and an\ninput column name, ORDER BY will interpret it as the output column name. This is the opposite\nof the choice that GROUP BY will make in the same situation. This inconsistency is made to be\ncompatible with the SQL standard.\n\nOptionally one can add the key word ASC (ascending) or DESC (descending) after any expression\nin the ORDER BY clause. If not specified, ASC is assumed by default. Alternatively, a\nspecific ordering operator name can be specified in the USING clause. An ordering operator\nmust be a less-than or greater-than member of some B-tree operator family.  ASC is usually\nequivalent to USING < and DESC is usually equivalent to USING >. (But the creator of a\nuser-defined data type can define exactly what the default sort ordering is, and it might\ncorrespond to operators with other names.)\n\nIf NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is\nspecified, null values sort before all non-null values. If neither is specified, the default\nbehavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is\nspecified (thus, the default is to act as though nulls are larger than non-nulls). When USING\nis specified, the default nulls ordering depends on whether the operator is a less-than or\ngreater-than operator.\n\nNote that ordering options apply only to the expression they follow; for example ORDER BY x,\ny DESC does not mean the same thing as ORDER BY x DESC, y DESC.\n\nCharacter-string data is sorted according to the collation that applies to the column being\nsorted. That can be overridden at need by including a COLLATE clause in the expression, for\nexample ORDER BY mycolumn COLLATE \"enUS\". For more information see Section 4.2.10 and\nSection 24.2.\n"
                },
                {
                    "name": "LIMIT Clause",
                    "content": "The LIMIT clause consists of two independent sub-clauses:\n\nLIMIT { count | ALL }\nOFFSET start\n\nThe parameter count specifies the maximum number of rows to return, while start specifies the\nnumber of rows to skip before starting to return rows. When both are specified, start rows\nare skipped before starting to count the count rows to be returned.\n\nIf the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If\nstart evaluates to NULL, it is treated the same as OFFSET 0.\n\nSQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also\nsupports. It is:\n\nOFFSET start { ROW | ROWS }\nFETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }\n\nIn this syntax, the start or count value is required by the standard to be a literal\nconstant, a parameter, or a variable name; as a PostgreSQL extension, other expressions are\nallowed, but will generally need to be enclosed in parentheses to avoid ambiguity. If count\nis omitted in a FETCH clause, it defaults to 1. The WITH TIES option is used to return any\nadditional rows that tie for the last place in the result set according to the ORDER BY\nclause; ORDER BY is mandatory in this case, and SKIP LOCKED is not allowed.  ROW and ROWS as\nwell as FIRST and NEXT are noise words that don't influence the effects of these clauses.\nAccording to the standard, the OFFSET clause must come before the FETCH clause if both are\npresent; but PostgreSQL is laxer and allows either order.\n\nWhen using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows\ninto a unique order. Otherwise you will get an unpredictable subset of the query's rows — you\nmight be asking for the tenth through twentieth rows, but tenth through twentieth in what\nordering? You don't know what ordering unless you specify ORDER BY.\n\nThe query planner takes LIMIT into account when generating a query plan, so you are very\nlikely to get different plans (yielding different row orders) depending on what you use for\nLIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a\nquery result will give inconsistent results unless you enforce a predictable result ordering\nwith ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not\npromise to deliver the results of a query in any particular order unless ORDER BY is used to\nconstrain the order.\n\nIt is even possible for repeated executions of the same LIMIT query to return different\nsubsets of the rows of a table, if there is not an ORDER BY to enforce selection of a\ndeterministic subset. Again, this is not a bug; determinism of the results is simply not\nguaranteed in such a case.\n"
                },
                {
                    "name": "The Locking Clause",
                    "content": "FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE are locking clauses; they affect\nhow SELECT locks rows as they are obtained from the table.\n\nThe locking clause has the general form\n\nFOR lockstrength [ OF tablename [, ...] ] [ NOWAIT | SKIP LOCKED ]\n\nwhere lockstrength can be one of\n\nUPDATE\nNO KEY UPDATE\nSHARE\nKEY SHARE\n\nFor more information on each row-level lock mode, refer to Section 13.3.2.\n\nTo prevent the operation from waiting for other transactions to commit, use either the NOWAIT\nor SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a\nselected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be\nimmediately locked are skipped. Skipping locked rows provides an inconsistent view of the\ndata, so this is not suitable for general purpose work, but can be used to avoid lock\ncontention with multiple consumers accessing a queue-like table. Note that NOWAIT and SKIP\nLOCKED apply only to the row-level lock(s) — the required ROW SHARE table-level lock is still\ntaken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if\nyou need to acquire the table-level lock without waiting.\n\nIf specific tables are named in a locking clause, then only rows coming from those tables are\nlocked; any other tables used in the SELECT are simply read as usual. A locking clause\nwithout a table list affects all tables used in the statement. If a locking clause is applied\nto a view or sub-query, it affects all tables used in the view or sub-query. However, these\nclauses do not apply to WITH queries referenced by the primary query. If you want row locking\nto occur within a WITH query, specify a locking clause within the WITH query.\n\nMultiple locking clauses can be written if it is necessary to specify different locking\nbehavior for different tables. If the same table is mentioned (or implicitly affected) by\nmore than one locking clause, then it is processed as if it was only specified by the\nstrongest one. Similarly, a table is processed as NOWAIT if that is specified in any of the\nclauses affecting it. Otherwise, it is processed as SKIP LOCKED if that is specified in any\nof the clauses affecting it.\n\nThe locking clauses cannot be used in contexts where returned rows cannot be clearly\nidentified with individual table rows; for example they cannot be used with aggregation.\n\nWhen a locking clause appears at the top level of a SELECT query, the rows that are locked\nare exactly those that are returned by the query; in the case of a join query, the rows\nlocked are those that contribute to returned join rows. In addition, rows that satisfied the\nquery conditions as of the query snapshot will be locked, although they will not be returned\nif they were updated after the snapshot and no longer satisfy the query conditions. If a\nLIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but\nnote that rows skipped over by OFFSET will get locked). Similarly, if a locking clause is\nused in a cursor's query, only rows actually fetched or stepped past by the cursor will be\nlocked.\n\nWhen a locking clause appears in a sub-SELECT, the rows locked are those returned to the\nouter query by the sub-query. This might involve fewer rows than inspection of the sub-query\nalone would suggest, since conditions from the outer query might be used to optimize\nexecution of the sub-query. For example,\n\nSELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;\n\nwill lock only rows having col1 = 5, even though that condition is not textually within the\nsub-query.\n\nPrevious releases failed to preserve a lock which is upgraded by a later savepoint. For\nexample, this code:\n\nBEGIN;\nSELECT * FROM mytable WHERE key = 1 FOR UPDATE;\nSAVEPOINT s;\nUPDATE mytable SET ... WHERE key = 1;\nROLLBACK TO s;\n\nwould fail to preserve the FOR UPDATE lock after the ROLLBACK TO. This has been fixed in\nrelease 9.3.\n\nCaution\nIt is possible for a SELECT command running at the READ COMMITTED transaction isolation\nlevel and using ORDER BY and a locking clause to return rows out of order. This is\nbecause ORDER BY is applied first. The command sorts the result, but might then block\ntrying to obtain a lock on one or more of the rows. Once the SELECT unblocks, some of the\nordering column values might have been modified, leading to those rows appearing to be\nout of order (though they are in order in terms of the original column values). This can\nbe worked around at need by placing the FOR UPDATE/SHARE clause in a sub-query, for\nexample\n\nSELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;\n\nNote that this will result in locking all rows of mytable, whereas FOR UPDATE at the top\nlevel would lock only the actually returned rows. This can make for a significant\nperformance difference, particularly if the ORDER BY is combined with LIMIT or other\nrestrictions. So this technique is recommended only if concurrent updates of the ordering\ncolumns are expected and a strictly sorted result is required.\n\nAt the REPEATABLE READ or SERIALIZABLE transaction isolation level this would cause a\nserialization failure (with a SQLSTATE of '40001'), so there is no possibility of\nreceiving rows out of order under these isolation levels.\n"
                },
                {
                    "name": "TABLE Command",
                    "content": "The command\n\nTABLE name\n\nis equivalent to\n\nSELECT * FROM name\n\nIt can be used as a top-level command or as a space-saving syntax variant in parts of complex\nqueries. Only the WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH and FOR\nlocking clauses can be used with TABLE; the WHERE clause and any form of aggregation cannot\nbe used.\n"
                }
            ]
        },
        "EXAMPLES": {
            "content": "To join the table films with the table distributors:\n\nSELECT f.title, f.did, d.name, f.dateprod, f.kind\nFROM distributors d JOIN films f USING (did);\n\ntitle       | did |     name     | dateprod  |   kind\n-------------------+-----+--------------+------------+----------\nThe Third Man     | 101 | British Lion | 1949-12-23 | Drama\nThe African Queen | 101 | British Lion | 1951-08-11 | Romantic\n...\n\nTo sum the column len of all films and group the results by kind:\n\nSELECT kind, sum(len) AS total FROM films GROUP BY kind;\n\nkind   | total\n----------+-------\nAction   | 07:34\nComedy   | 02:58\nDrama    | 14:28\nMusical  | 06:42\nRomantic | 04:38\n\nTo sum the column len of all films, group the results by kind and show those group totals\nthat are less than 5 hours:\n\nSELECT kind, sum(len) AS total\nFROM films\nGROUP BY kind\nHAVING sum(len) < interval '5 hours';\n\nkind   | total\n----------+-------\nComedy   | 02:58\nRomantic | 04:38\n\nThe following two examples are identical ways of sorting the individual results according to\nthe contents of the second column (name):\n\nSELECT * FROM distributors ORDER BY name;\nSELECT * FROM distributors ORDER BY 2;\n\ndid |       name\n-----+------------------\n109 | 20th Century Fox\n110 | Bavaria Atelier\n101 | British Lion\n107 | Columbia\n102 | Jean Luc Godard\n113 | Luso films\n104 | Mosfilm\n103 | Paramount\n106 | Toho\n105 | United Artists\n111 | Walt Disney\n112 | Warner Bros.\n108 | Westward\n\nThe next example shows how to obtain the union of the tables distributors and actors,\nrestricting the results to those that begin with the letter W in each table. Only distinct\nrows are wanted, so the key word ALL is omitted.\n\ndistributors:               actors:\ndid |     name              id |     name\n-----+--------------        ----+----------------\n108 | Westward               1 | Woody Allen\n111 | Walt Disney            2 | Warren Beatty\n112 | Warner Bros.           3 | Walter Matthau\n...                         ...\n\nSELECT distributors.name\nFROM distributors\nWHERE distributors.name LIKE 'W%'\nUNION\nSELECT actors.name\nFROM actors\nWHERE actors.name LIKE 'W%';\n\nname\n----------------\nWalt Disney\nWalter Matthau\nWarner Bros.\nWarren Beatty\nWestward\nWoody Allen\n\nThis example shows how to use a function in the FROM clause, both with and without a column\ndefinition list:\n\nCREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$\nSELECT * FROM distributors WHERE did = $1;\n$$ LANGUAGE SQL;\n\nSELECT * FROM distributors(111);\ndid |    name\n-----+-------------\n111 | Walt Disney\n\nCREATE FUNCTION distributors2(int) RETURNS SETOF record AS $$\nSELECT * FROM distributors WHERE did = $1;\n$$ LANGUAGE SQL;\n\nSELECT * FROM distributors2(111) AS (f1 int, f2 text);\nf1  |     f2\n-----+-------------\n111 | Walt Disney\n\nHere is an example of a function with an ordinality column added:\n\nSELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;\nunnest | ordinality\n--------+----------\na      |        1\nb      |        2\nc      |        3\nd      |        4\ne      |        5\nf      |        6\n(6 rows)\n\nThis example shows how to use a simple WITH clause:\n\nWITH t AS (\nSELECT random() as x FROM generateseries(1, 3)\n)\nSELECT * FROM t\nUNION ALL\nSELECT * FROM t;\nx\n--------------------\n0.534150459803641\n0.520092216785997\n0.0735620250925422\n0.534150459803641\n0.520092216785997\n0.0735620250925422\n\nNotice that the WITH query was evaluated only once, so that we got two sets of the same three\nrandom values.\n\nThis example uses WITH RECURSIVE to find all subordinates (direct or indirect) of the\nemployee Mary, and their level of indirectness, from a table that shows only direct\nsubordinates:\n\nWITH RECURSIVE employeerecursive(distance, employeename, managername) AS (\nSELECT 1, employeename, managername\nFROM employee\nWHERE managername = 'Mary'\nUNION ALL\nSELECT er.distance + 1, e.employeename, e.managername\nFROM employeerecursive er, employee e\nWHERE er.employeename = e.managername\n)\nSELECT distance, employeename FROM employeerecursive;\n\nNotice the typical form of recursive queries: an initial condition, followed by UNION,\nfollowed by the recursive part of the query. Be sure that the recursive part of the query\nwill eventually return no tuples, or else the query will loop indefinitely. (See Section 7.8\nfor more examples.)\n\nThis example uses LATERAL to apply a set-returning function getproductnames() for each row\nof the manufacturers table:\n\nSELECT m.name AS mname, pname\nFROM manufacturers m, LATERAL getproductnames(m.id) pname;\n\nManufacturers not currently having any products would not appear in the result, since it is\nan inner join. If we wished to include the names of such manufacturers in the result, we\ncould do:\n\nSELECT m.name AS mname, pname\nFROM manufacturers m LEFT JOIN LATERAL getproductnames(m.id) pname ON true;\n",
            "subsections": []
        },
        "COMPATIBILITY": {
            "content": "Of course, the SELECT statement is compatible with the SQL standard. But there are some\nextensions and some missing features.\n",
            "subsections": [
                {
                    "name": "Omitted FROM Clauses",
                    "content": "PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the\nresults of simple expressions:\n\nSELECT 2+2;\n\n?column?\n----------\n4\n\nSome other SQL databases cannot do this except by introducing a dummy one-row table from\nwhich to do the SELECT.\n"
                },
                {
                    "name": "Empty SELECT Lists",
                    "content": "The list of output expressions after SELECT can be empty, producing a zero-column result\ntable. This is not valid syntax according to the SQL standard.  PostgreSQL allows it to be\nconsistent with allowing zero-column tables. However, an empty list is not allowed when\nDISTINCT is used.\n"
                },
                {
                    "name": "Omitting the AS Key Word",
                    "content": "In the SQL standard, the optional key word AS can be omitted before an output column name\nwhenever the new column name is a valid column name (that is, not the same as any reserved\nkeyword).  PostgreSQL is slightly more restrictive: AS is required if the new column name\nmatches any keyword at all, reserved or not. Recommended practice is to use AS or\ndouble-quote output column names, to prevent any possible conflict against future keyword\nadditions.\n\nIn FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that\nis an unreserved keyword. But this is impractical for output column names, because of\nsyntactic ambiguities.\n"
                },
                {
                    "name": "ONLY and Inheritance",
                    "content": "The SQL standard requires parentheses around the table name when writing ONLY, for example\nSELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ....  PostgreSQL considers these parentheses to\nbe optional.\n\nPostgreSQL allows a trailing * to be written to explicitly specify the non-ONLY behavior of\nincluding child tables. The standard does not allow this.\n\n(These points apply equally to all SQL commands supporting the ONLY option.)\n"
                },
                {
                    "name": "TABLESAMPLE Clause Restrictions",
                    "content": "The TABLESAMPLE clause is currently accepted only on regular tables and materialized views.\nAccording to the SQL standard it should be possible to apply it to any FROM item.\n"
                },
                {
                    "name": "Function Calls in FROM",
                    "content": "PostgreSQL allows a function call to be written directly as a member of the FROM list. In the\nSQL standard it would be necessary to wrap such a function call in a sub-SELECT; that is, the\nsyntax FROM func(...) alias is approximately equivalent to FROM LATERAL (SELECT func(...))\nalias. Note that LATERAL is considered to be implicit; this is because the standard requires\nLATERAL semantics for an UNNEST() item in FROM.  PostgreSQL treats UNNEST() the same as other\nset-returning functions.\n"
                },
                {
                    "name": "Namespace Available to GROUP BY and ORDER BY",
                    "content": "In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while\na GROUP BY clause can only use expressions based on input column names.  PostgreSQL extends\neach of these clauses to allow the other choice as well (but it uses the standard's\ninterpretation if there is ambiguity).  PostgreSQL also allows both clauses to specify\narbitrary expressions. Note that names appearing in an expression will always be taken as\ninput-column names, not as output-column names.\n\nSQL:1999 and later use a slightly different definition which is not entirely upward\ncompatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or\nGROUP BY expression the same way SQL:1999 does.\n"
                },
                {
                    "name": "Functional Dependencies",
                    "content": "PostgreSQL recognizes functional dependency (allowing columns to be omitted from GROUP BY)\nonly when a table's primary key is included in the GROUP BY list. The SQL standard specifies\nadditional conditions that should be recognized.\n"
                },
                {
                    "name": "LIMIT and OFFSET",
                    "content": "The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used by MySQL. The SQL:2008\nstandard has introduced the clauses OFFSET ... FETCH {FIRST|NEXT} ...  for the same\nfunctionality, as shown above in LIMIT Clause. This syntax is also used by IBM DB2.\n(Applications written for Oracle frequently use a workaround involving the automatically\ngenerated rownum column, which is not available in PostgreSQL, to implement the effects of\nthese clauses.)\n"
                },
                {
                    "name": "FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE",
                    "content": "Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of\nDECLARE CURSOR.  PostgreSQL allows it in any SELECT query as well as in sub-SELECTs, but this\nis an extension. The FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE variants, as well as the\nNOWAIT and SKIP LOCKED options, do not appear in the standard.\n"
                },
                {
                    "name": "Data-Modifying Statements in WITH",
                    "content": "PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This is not found in\nthe SQL standard.\n"
                },
                {
                    "name": "Nonstandard Clauses",
                    "content": "DISTINCT ON ( ... ) is an extension of the SQL standard.\n\nROWS FROM( ... ) is an extension of the SQL standard.\n\nThe MATERIALIZED and NOT MATERIALIZED options of WITH are extensions of the SQL standard.\n\n\n\nPostgreSQL 14.23                                2026                                       SELECT(7)"
                }
            ]
        }
    },
    "summary": "SELECT, TABLE, WITH - retrieve rows from a table or view",
    "flags": [],
    "examples": [
        "To join the table films with the table distributors:",
        "SELECT f.title, f.did, d.name, f.dateprod, f.kind",
        "FROM distributors d JOIN films f USING (did);",
        "title       | did |     name     | dateprod  |   kind",
        "-------------------+-----+--------------+------------+----------",
        "The Third Man     | 101 | British Lion | 1949-12-23 | Drama",
        "The African Queen | 101 | British Lion | 1951-08-11 | Romantic",
        "...",
        "To sum the column len of all films and group the results by kind:",
        "SELECT kind, sum(len) AS total FROM films GROUP BY kind;",
        "kind   | total",
        "----------+-------",
        "Action   | 07:34",
        "Comedy   | 02:58",
        "Drama    | 14:28",
        "Musical  | 06:42",
        "Romantic | 04:38",
        "To sum the column len of all films, group the results by kind and show those group totals",
        "that are less than 5 hours:",
        "SELECT kind, sum(len) AS total",
        "FROM films",
        "GROUP BY kind",
        "HAVING sum(len) < interval '5 hours';",
        "kind   | total",
        "----------+-------",
        "Comedy   | 02:58",
        "Romantic | 04:38",
        "The following two examples are identical ways of sorting the individual results according to",
        "the contents of the second column (name):",
        "SELECT * FROM distributors ORDER BY name;",
        "SELECT * FROM distributors ORDER BY 2;",
        "did |       name",
        "-----+------------------",
        "109 | 20th Century Fox",
        "110 | Bavaria Atelier",
        "101 | British Lion",
        "107 | Columbia",
        "102 | Jean Luc Godard",
        "113 | Luso films",
        "104 | Mosfilm",
        "103 | Paramount",
        "106 | Toho",
        "105 | United Artists",
        "111 | Walt Disney",
        "112 | Warner Bros.",
        "108 | Westward",
        "The next example shows how to obtain the union of the tables distributors and actors,",
        "restricting the results to those that begin with the letter W in each table. Only distinct",
        "rows are wanted, so the key word ALL is omitted.",
        "distributors:               actors:",
        "did |     name              id |     name",
        "-----+--------------        ----+----------------",
        "108 | Westward               1 | Woody Allen",
        "111 | Walt Disney            2 | Warren Beatty",
        "112 | Warner Bros.           3 | Walter Matthau",
        "...                         ...",
        "SELECT distributors.name",
        "FROM distributors",
        "WHERE distributors.name LIKE 'W%'",
        "UNION",
        "SELECT actors.name",
        "FROM actors",
        "WHERE actors.name LIKE 'W%';",
        "name",
        "----------------",
        "Walt Disney",
        "Walter Matthau",
        "Warner Bros.",
        "Warren Beatty",
        "Westward",
        "Woody Allen",
        "This example shows how to use a function in the FROM clause, both with and without a column",
        "definition list:",
        "CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$",
        "SELECT * FROM distributors WHERE did = $1;",
        "$$ LANGUAGE SQL;",
        "SELECT * FROM distributors(111);",
        "did |    name",
        "-----+-------------",
        "111 | Walt Disney",
        "CREATE FUNCTION distributors2(int) RETURNS SETOF record AS $$",
        "SELECT * FROM distributors WHERE did = $1;",
        "$$ LANGUAGE SQL;",
        "SELECT * FROM distributors2(111) AS (f1 int, f2 text);",
        "f1  |     f2",
        "-----+-------------",
        "111 | Walt Disney",
        "Here is an example of a function with an ordinality column added:",
        "SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;",
        "unnest | ordinality",
        "--------+----------",
        "a      |        1",
        "b      |        2",
        "c      |        3",
        "d      |        4",
        "e      |        5",
        "f      |        6",
        "(6 rows)",
        "This example shows how to use a simple WITH clause:",
        "WITH t AS (",
        "SELECT random() as x FROM generateseries(1, 3)",
        "SELECT * FROM t",
        "UNION ALL",
        "SELECT * FROM t;",
        "--------------------",
        "0.534150459803641",
        "0.520092216785997",
        "0.0735620250925422",
        "0.534150459803641",
        "0.520092216785997",
        "0.0735620250925422",
        "Notice that the WITH query was evaluated only once, so that we got two sets of the same three",
        "random values.",
        "This example uses WITH RECURSIVE to find all subordinates (direct or indirect) of the",
        "employee Mary, and their level of indirectness, from a table that shows only direct",
        "subordinates:",
        "WITH RECURSIVE employeerecursive(distance, employeename, managername) AS (",
        "SELECT 1, employeename, managername",
        "FROM employee",
        "WHERE managername = 'Mary'",
        "UNION ALL",
        "SELECT er.distance + 1, e.employeename, e.managername",
        "FROM employeerecursive er, employee e",
        "WHERE er.employeename = e.managername",
        "SELECT distance, employeename FROM employeerecursive;",
        "Notice the typical form of recursive queries: an initial condition, followed by UNION,",
        "followed by the recursive part of the query. Be sure that the recursive part of the query",
        "will eventually return no tuples, or else the query will loop indefinitely. (See Section 7.8",
        "for more examples.)",
        "This example uses LATERAL to apply a set-returning function getproductnames() for each row",
        "of the manufacturers table:",
        "SELECT m.name AS mname, pname",
        "FROM manufacturers m, LATERAL getproductnames(m.id) pname;",
        "Manufacturers not currently having any products would not appear in the result, since it is",
        "an inner join. If we wished to include the names of such manufacturers in the result, we",
        "could do:",
        "SELECT m.name AS mname, pname",
        "FROM manufacturers m LEFT JOIN LATERAL getproductnames(m.id) pname ON true;"
    ],
    "see_also": []
}