Search PostgreSQL documentation

NotesManual ChaptersPG18DROP ROUTINEThe lookup rules used by DROP ROUTINE are fundamentally the same as for DROP PROCEDURE; in particular, DROP ROUTINE shares that command's behavior of considering an argument list that has no… NotesManual ChaptersPG18DROP SCHEMAUsing the CASCADE option might make the command remove objects in other schemas besides the one(s) named. NotesManual ChaptersPG18DROP SUBSCRIPTIONWhen dropping a subscription that is associated with a replication slot on the remote host (the normal state), DROP SUBSCRIPTION will connect to the remote host and try to drop the replicati… NotesManual ChaptersPG18DROP TABLESPACEDROP TABLESPACE cannot be executed inside a transaction block. NotesManual ChaptersPG18ENDUse ROLLBACK to abort a transaction. Issuing END when not inside a transaction does no harm, but it will provoke a warning message. NotesManual ChaptersPG18EXPLAINIn order to allow the PostgreSQL query planner to make reasonably informed decisions when optimizing queries, the pg_statistic data should be up-to-date for all tables used in the query. Nor… NotesManual ChaptersPG18FETCHThe cursor should be declared with the SCROLL option if one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count. For simple queries PostgreSQL w… NotesManual ChaptersPG18GRANTThe REVOKE command is used to revoke access privileges. Since PostgreSQL 8.1, the concepts of users and groups have been unified into a single kind of entity called a role. It is therefore n… NotesManual ChaptersPG18INSERTIf the specified table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the specified table is a partition, an error will occur if one of the … NotesManual ChaptersPG18LISTENLISTEN takes effect at transaction commit. If LISTEN or UNLISTEN is executed within a transaction that later rolls back, the set of notification channels being listened to is unchanged. A tr… NotesManual ChaptersPG18LOCKTo lock a table, the user must have the right privilege for the specified lockmode. If the user has MAINTAIN, UPDATE, DELETE, or TRUNCATE privileges on the table, any lockmode is permitted. … NotesManual ChaptersPG18MERGEThe following steps take place during the execution of MERGE. Perform any BEFORE STATEMENT triggers for all actions specified, whether or not their WHEN clauses match. Perform a join from so… NotesManual ChaptersPG18NOTIFYThere is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. Th… NotesManual ChaptersPG18PREPARE TRANSACTIONPREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across mult… NotesManual ChaptersPG18PREPAREA prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution … NotesManual ChaptersPG18REASSIGN OWNEDREASSIGN OWNED is often used to prepare for the removal of one or more roles. Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this c… NotesManual ChaptersPG18REFRESH MATERIALIZED VIEWIf there is an ORDER BY clause in the materialized view's defining query, the original contents of the materialized view will be ordered that way; but REFRESH MATERIALIZED VIEW does not guar… NotesManual ChaptersPG18REINDEXIf you suspect corruption of an index on a user table, you can simply rebuild that index, or all indexes on the table, using REINDEX INDEX or REINDEX TABLE. Things are more difficult if you … NotesManual ChaptersPG18RELEASE SAVEPOINTSpecifying a savepoint name that was not previously defined is an error. It is not possible to release a savepoint when the transaction is in an aborted state; to do that, use ROLLBACK TO SA… NotesManual ChaptersPG18REVOKEA user can only revoke privileges that were granted directly by that user. If, for example, user A has granted a privilege with grant option to user B, and user B has in turn granted it to u… NotesManual ChaptersPG18ROLLBACK PREPAREDTo roll back a prepared transaction, you must be either the same user that executed the transaction originally, or a superuser. But you do not have to be in the same session that executed th… NotesManual ChaptersPG18ROLLBACK TO SAVEPOINTUse RELEASE SAVEPOINT to destroy a savepoint without discarding the effects of commands executed after it was established. Specifying a savepoint name that has not been established is an err… NotesManual ChaptersPG18ROLLBACKUse COMMIT to successfully terminate a transaction. Issuing ROLLBACK outside of a transaction block emits a warning and otherwise has no effect. ROLLBACK AND CHAIN outside of a transaction b… NotesManual ChaptersPG18SAVEPOINTUse ROLLBACK TO to rollback to a savepoint. Use RELEASE SAVEPOINT to destroy a savepoint, keeping the effects of commands executed after it was established. Savepoints can only be establishe… NotesManual ChaptersPG18SELECT INTOCREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret… NotesManual ChaptersPG18SET CONSTRAINTSBecause PostgreSQL does not require constraint names to be unique within a schema (but only per-table), it is possible that there is more than one match for a specified constraint name. In t… NotesManual ChaptersPG18SET ROLEUsing this command, it is possible to either add privileges or restrict one's privileges. If the session user role has been granted memberships WITH INHERIT TRUE, it automatically has all th… NotesManual ChaptersPG18SET SESSION AUTHORIZATIONSET SESSION AUTHORIZATION cannot be used within a SECURITY DEFINER function. NotesManual ChaptersPG18SET TRANSACTIONIf SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect. It is possible to dispense with SET TRANSACTION by instead specifyi… NotesManual ChaptersPG18SETThe function set_config provides equivalent functionality; see Section 9.28.1. Also, it is possible to UPDATE the pg_settings system view to perform the equivalent of SET. NotesManual ChaptersPG18SHOWThe function current_setting produces equivalent output; see Section 9.28.1. Also, the pg_settings system view produces the same information. NotesManual ChaptersPG18TRUNCATEYou must have the TRUNCATE privilege on a table to truncate it. TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the t… NotesManual ChaptersPG18UNLISTENYou can unlisten something you were not listening for; no warning or error will appear. At the end of each session, UNLISTEN * is automatically executed. A transaction that has executed UNLI… NotesManual ChaptersPG18UPDATEWhen a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update… NotesManual ChaptersPG18VACUUMTo vacuum a table, one must ordinarily have the MAINTAIN privilege on the table. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. VACUUM … NotesManual ChaptersPG18VALUESVALUES lists with very large numbers of rows should be avoided, as you might encounter out-of-memory failures or poor performance. VALUES appearing within INSERT is a special case (because t… NotesManual ChaptersPG18vacuumlovacuumlo works by the following method: First, vacuumlo builds a temporary table which contains all of the OIDs of the large objects in the selected database. It then scans through all colum… Notes for Windows UsersManual ChaptersPG18psqlpsql is built as a “console application”. Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters withi… Notice ProcessingManual ChaptersPG18Notice and warning messages generated by the server are not returned by the query execution functions, since they do not imply failure of the query. Instead they are passed to a notice handl… NOTIFYManual ChaptersPG18generate a notification Synopsis NOTIFY channel [ , payload ]