Search PostgreSQL documentation

NULLSQL CommandsPG18NULL “Constraint” The NULL “constraint” (actually a non-constraint) is a PostgreSQL extension to the SQL standard that is included for compatibility with some other database systems (and for…CREATE TABLE › Compatibility › NULL “Constraint” NULLIFSQL CommandsPG189.18.3. NULLIF # NULLIF(value1, value2) The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of th…Conditional Expressions › NULLIF OFFSETSQL 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 ON CONFLICTSQL CommandsPG18ON CONFLICT Clause The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for…INSERT › Parameters › ON CONFLICT Clause ONLYSQL CommandsPG18ONLY and Inheritance The SQL standard requires parentheses around the table name when writing ONLY, for example SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL considers these p…SELECT › Compatibility › ONLY and Inheritance ORDER BYSQL CommandsPG187.5. Sorting Rows (ORDER BY) # After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be r…Sorting Rows (ORDER BY) PARTITION BYSQL CommandsPG18PARTITION BY Clause The PARTITION BY clause is a PostgreSQL extension.CREATE TABLE › Compatibility › PARTITION BY Clause PARTITION OFSQL CommandsPG18PARTITION OF Clause The PARTITION OF clause is a PostgreSQL extension.CREATE TABLE › Compatibility › PARTITION OF Clause PREPARESQL CommandsReferencePREPARE Cursors & Prepared Statements 10 prepare a statement for execution PREPARE name [ ( data_type [, ...] ) ] AS statement PREPARE creates a prepared statement. A prepared statement is a…SQL Commands · Cursors & Prepared Statements PREPARE TRANSACTIONSQL CommandsReferencePREPARE TRANSACTION Transactions 10 prepare the current transaction for two-phase commit PREPARE TRANSACTION transaction_id PREPARE TRANSACTION prepares the current transaction for two-phase…SQL Commands · Transactions REASSIGN OWNEDSQL CommandsReferenceREASSIGN OWNED Roles & Privileges 10 change the ownership of database objects owned by a database role REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...] TO { new_role | CU…SQL Commands · Roles & Privileges REFRESH MATERIALIZED VIEWSQL CommandsReferenceREFRESH MATERIALIZED VIEW Tables & Views 10 replace the contents of a materialized view REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ] REFRESH MATERIALIZED VIEW complet…SQL Commands · Tables & Views REINDEXSQL CommandsReferenceREINDEX Indexes & Statistics 10 rebuild indexes REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name REINDEX rebuilds an index using the data stored in the index's tab…SQL Commands · Indexes & Statistics RELEASE SAVEPOINTSQL CommandsReferenceRELEASE SAVEPOINT Transactions 10 destroy a previously defined savepoint RELEASE [ SAVEPOINT ] savepoint_name RELEASE SAVEPOINT destroys a savepoint previously defined in the current transac…SQL Commands · Transactions REPACKSQL CommandsReferenceREPACK Other Objects 19 rewrite a table to reclaim disk space REPACK [ ( option [, ...] ) ] [ table_and_columns [ USING INDEX [ index_name ] ] ] REPACK [ ( option [, ...] ) ] USING INDEX wh…SQL Commands · Other Objects RESETSQL CommandsReferenceRESET Sessions & Settings 10 restore the value of a run-time parameter to the default value RESET configuration_parameter RESET ALL RESET restores run-time parameters to their default values…SQL Commands · Sessions & Settings REVOKESQL CommandsReferenceREVOKE Roles & Privileges 10 remove access privileges REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON …SQL Commands · Roles & Privileges ROLLBACKSQL CommandsReferenceROLLBACK Transactions 10 abort the current transaction ROLLBACK [ WORK | TRANSACTION ] ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be di…SQL Commands · Transactions ROLLBACK PREPAREDSQL CommandsReferenceROLLBACK PREPARED Transactions 10 cancel a transaction that was earlier prepared for two-phase commit ROLLBACK PREPARED transaction_id ROLLBACK PREPARED rolls back a transaction that is in p…SQL Commands · Transactions ROLLBACK TO SAVEPOINTSQL CommandsReferenceROLLBACK TO SAVEPOINT Transactions 10 roll back to a savepoint ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name Roll back all commands that were executed after the savepoint w…SQL Commands · Transactions ROLLUPSQL 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 SAVEPOINTSQL CommandsReferenceSAVEPOINT Transactions 10 define a new savepoint within the current transaction SAVEPOINT savepoint_name SAVEPOINT establishes a new savepoint within the current transaction. A savepoint is …SQL Commands · Transactions SECURITY DEFINERSQL CommandsPG18Writing SECURITY DEFINER Functions Safely Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be m…CREATE FUNCTION › Writing SECURITY DEFINER Functions Safely SECURITY LABELSQL CommandsReferenceSECURITY LABEL Roles & Privileges 10 define or change a security label applied to an object SECURITY LABEL [ FOR provider ] ON { TABLE object_name | COLUMN table_name.column_name | AGGREGATE…SQL Commands · Roles & Privileges SELECT INTOSQL CommandsReferenceSELECT INTO Queries & Data 10 define a new table from the results of a query [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression …SQL Commands · Queries & Data session_userSQL CommandsPG18session_user → name Returns the session user's name.System Information Functions and Operators › Session Information Functions SETSQL CommandsReferenceSET Sessions & Settings 10 change a run-time parameter SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT } SET [ SESSION | LOCAL ] TIME ZONE { value | 'v…SQL Commands · Sessions & Settings SET CONSTRAINTSSQL CommandsReferenceSET CONSTRAINTS Transactions 10 set constraint check timing for the current transaction SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE } SET CONSTRAINTS sets the behavior of c…SQL Commands · Transactions SET ROLESQL CommandsReferenceSET ROLE Roles & Privileges 10 set the current user identifier of the current session SET [ SESSION | LOCAL ] ROLE role_name SET [ SESSION | LOCAL ] ROLE NONE RESET ROLE This command sets th…SQL Commands · Roles & Privileges SET SESSION AUTHORIZATIONSQL CommandsReferenceSET SESSION AUTHORIZATION Roles & Privileges 10 set the session user identifier and the current user identifier of the current session SET [ SESSION | LOCAL ] SESSION AUTHORIZATION user_name…SQL Commands · Roles & Privileges SET TRANSACTIONSQL CommandsReferenceSET TRANSACTION Transactions 10 set the characteristics of the current transaction SET TRANSACTION transaction_mode [, ...] SET TRANSACTION SNAPSHOT snapshot_id SET SESSION CHARACTERISTICS A…SQL Commands · Transactions SHOWSQL CommandsReferenceSHOW Sessions & Settings 10 show the value of a run-time parameter SHOW name SHOW ALL SHOW will display the current setting of run-time parameters. These variables can be set using the SET s…SQL Commands · Sessions & Settings SOMESQL CommandsPG189.25.3. ANY/SOME (array) # expression operator ANY (array expression) expression operator SOME (array expression) The right-hand side is a parenthesized expression, which must yield an array…Row and Array Comparisons › ANY/SOME (array) START TRANSACTIONSQL CommandsReferenceSTART TRANSACTION Transactions 10 start a transaction block START TRANSACTION [ transaction_mode [, ...] ] where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE REA…SQL Commands · Transactions TABLESQL CommandsPG18TABLE Command The command TABLE name is equivalent to SELECT * FROM name It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. Only the WITH,…SELECT › Parameters › TABLE Command TABLESAMPLESQL CommandsPG18TABLESAMPLE Clause Restrictions The TABLESAMPLE clause is currently accepted only on regular tables and materialized views. According to the SQL standard it should be possible to apply it to…SELECT › Compatibility › TABLESAMPLE Clause Restrictions TRUNCATESQL CommandsReferenceTRUNCATE Tables & Views 10 empty a table or set of tables TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] TRUNCATE quickly rem…SQL Commands · Tables & Views UNIONSQL 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) UNLISTENSQL CommandsReferenceUNLISTEN Sessions & Settings 10 stop listening for a notification UNLISTEN { channel | * } UNLISTEN is used to remove an existing registration for NOTIFY events. UNLISTEN cancels any existin…SQL Commands · Sessions & Settings UPDATESQL CommandsReferenceUPDATE Queries & Data 10 update rows of a table [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( c…SQL Commands · Queries & Data