Search PostgreSQL documentation

DROP TYPESQL CommandsReferenceDROP TYPE Types & Operators 10 remove a data type DROP TYPE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] DROP TYPE removes a user-defined data type. Only the owner of a type can remove …SQL Commands · Types & Operators DROP USERSQL CommandsReferenceDROP USER Roles & Privileges 10 remove a database role DROP USER [ IF EXISTS ] name [, ...] DROP USER is simply an alternate spelling of DROP ROLE . The DROP USER statement is a PostgreSQL e…SQL Commands · Roles & Privileges DROP USER MAPPINGSQL CommandsReferenceDROP USER MAPPING Foreign Data 10 remove a user mapping for a foreign server DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name DROP USER MAP…SQL Commands · Foreign Data DROP VIEWSQL CommandsReferenceDROP VIEW Tables & Views 10 remove a view DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] DROP VIEW drops an existing view. To execute this command you must be the owner of the v…SQL Commands · Tables & Views ENDSQL CommandsReferenceEND Transactions 10 commit the current transaction END [ WORK | TRANSACTION ] END commits the current transaction. All changes made by the transaction become visible to others and are guaran…SQL Commands · Transactions EXCEPTSQL CommandsPG187.4. Combining Queries (UNION, INTERSECT, EXCEPT) # The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is query1 UNION [ALL] …Combining Queries (UNION, INTERSECT, EXCEPT) EXCLUDESQL CommandsPG18EXCLUDE Constraint The EXCLUDE constraint type is a PostgreSQL extension.CREATE TABLE › Compatibility › EXCLUDE Constraint EXECUTESQL CommandsReferenceEXECUTE Cursors & Prepared Statements 10 execute a prepared statement EXECUTE name [ ( parameter [, ...] ) ] EXECUTE is used to execute a previously prepared statement. Since prepared statem…SQL Commands · Cursors & Prepared Statements EXISTSSQL CommandsPG189.24.1. EXISTS # EXISTS (subquery) The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at …Subquery Expressions › EXISTS FETCHSQL CommandsReferenceFETCH Cursors & Prepared Statements 10 retrieve rows from a query using a cursor FETCH [ direction ] [ FROM | IN ] cursor_name where direction can be one of: NEXT PRIOR FIRST LAST ABSOLUTE …SQL Commands · Cursors & Prepared Statements FOR KEY SHARESQL CommandsPG18FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in a…SELECT › Compatibility › FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE FOR NO KEY UPDATESQL CommandsPG18FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in a…SELECT › Compatibility › FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE FOR SHARESQL CommandsPG18FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in a…SELECT › Compatibility › FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE FOR UPDATESQL CommandsPG18FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in a…SELECT › Compatibility › FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE FROMSQL CommandsPG187.2.1. The FROM Clause # The FROM clause derives a table from one or more other tables given in a comma-separated table reference list. FROM table_reference [, table_reference [, ...]] A tab…Table Expressions › The FROM Clause GRANTSQL CommandsReferenceGRANT Roles & Privileges 10 define access privileges GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name …SQL Commands · Roles & Privileges GREATESTSQL CommandsPG189.18.4. GREATEST and LEAST # GREATEST(value [, ...]) LEAST(value [, ...]) The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The …Conditional Expressions › GREATEST and LEAST GROUP BYSQL CommandsPG187.2.3. The GROUP BY and HAVING Clauses # After passing the WHERE filter, the derived input table might be subject to grouping, using the GROUP BY clause, and elimination of group rows using …Table Expressions › The GROUP BY and HAVING Clauses GROUPINGSQL CommandsPG18GROUPING ( group_by_expression(s) ) → integer Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argu…Aggregate Functions GROUPING SETSSQL CommandsPG187.2.4. GROUPING SETS, CUBE, and ROLLUP # More complex grouping operations than those described above are possible using the concept of grouping sets. The data selected by the FROM and WHERE …Table Expressions › GROUPING SETS, CUBE, and ROLLUP HAVINGSQL CommandsPG187.2.3. The GROUP BY and HAVING Clauses # After passing the WHERE filter, the derived input table might be subject to grouping, using the GROUP BY clause, and elimination of group rows using …Table Expressions › The GROUP BY and HAVING Clauses IMPORT FOREIGN SCHEMASQL CommandsReferenceIMPORT FOREIGN SCHEMA Foreign Data 10 import table definitions from a foreign server IMPORT FOREIGN SCHEMA remote_schema [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] FROM SERVER server_n…SQL Commands · Foreign Data INSQL CommandsPG189.25.1. IN # expression IN (value [, ...]) The right-hand side is a parenthesized list of expressions. The result is “true” if the left-hand expression's result is equal to any of the right-…Row and Array Comparisons › IN INSERTSQL CommandsReferenceINSERT Queries & Data 10 create new rows in a table [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] [ OVERRIDING { SYSTEM | USER } V…SQL Commands · Queries & Data INTERSECTSQL CommandsPG187.4. Combining Queries (UNION, INTERSECT, EXCEPT) # The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is query1 UNION [ALL] …Combining Queries (UNION, INTERSECT, EXCEPT) IS DOCUMENTSQL CommandsPG189.15.2.1. IS DOCUMENT # xml IS DOCUMENT → boolean The expression IS DOCUMENT returns true if the argument XML value is a proper XML document, false if it is not (that is, it is a content fra…XML Functions › XML Predicates › IS DOCUMENT IS NOT DOCUMENTSQL CommandsPG189.15.2.2. IS NOT DOCUMENT # xml IS NOT DOCUMENT → boolean The expression IS NOT DOCUMENT returns false if the argument XML value is a proper XML document, true if it is not (that is, it is a…XML Functions › XML Predicates › IS NOT DOCUMENT LATERALSQL CommandsPG187.2.1.5. LATERAL Subqueries # Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, …Table Expressions › The FROM Clause › LATERAL Subqueries LEASTSQL CommandsPG189.18.4. GREATEST and LEAST # GREATEST(value [, ...]) LEAST(value [, ...]) The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The …Conditional Expressions › GREATEST and LEAST LIKESQL CommandsPG18LIKE Clause While a LIKE clause exists in the SQL standard, many of the options that PostgreSQL accepts for it are not in the standard, and some of the standard's options are not implemented…CREATE TABLE › Compatibility › LIKE Clause LIMITSQL CommandsPG187.6. LIMIT and OFFSET # LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query: SELECT select_list FROM table_expression [ ORDER BY ...…LIMIT and OFFSET LISTENSQL CommandsReferenceLISTEN Sessions & Settings 10 listen for a notification LISTEN channel LISTEN registers the current session as a listener on the notification channel named channel . If the current session i…SQL Commands · Sessions & Settings LOADSQL CommandsReferenceLOAD Extensions & Access Methods 10 load a shared library file LOAD 'filename' This command loads a shared library file into the PostgreSQL server's address space. If the file has been loade…SQL Commands · Extensions & Access Methods localtimeSQL CommandsPG18localtime → time Current time of day; see Section 9.9.5 localtime → 14:39:53.662522Date/Time Functions and Operators localtimestampSQL CommandsPG18localtimestamp → timestamp Current date and time (start of current transaction); see Section 9.9.5 localtimestamp → 2019-12-23 14:39:53.662522Date/Time Functions and Operators LOCKSQL CommandsReferenceLOCK Queries & Data 10 lock a table LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ] where lockmode is one of: ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UP…SQL Commands · Queries & Data MERGESQL CommandsReferenceMERGE Queries & Data 15 conditionally insert, update, or delete rows of a table [ WITH with_query [, ...] ] MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ] USING data_sou…SQL Commands · Queries & Data MOVESQL CommandsReferenceMOVE Cursors & Prepared Statements 10 position a cursor MOVE [ direction ] [ FROM | IN ] cursor_name where direction can be one of: NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count…SQL Commands · Cursors & Prepared Statements NOT INSQL CommandsPG189.25.2. NOT IN # expression NOT IN (value [, ...]) The right-hand side is a parenthesized list of expressions. The result is “true” if the left-hand expression's result is unequal to all of …Row and Array Comparisons › NOT IN NOTIFYSQL CommandsReferenceNOTIFY Sessions & Settings 10 generate a notification NOTIFY channel [ , payload ] The NOTIFY command sends a notification event together with an optional “ payload ” string to each client a…SQL Commands · Sessions & Settings