Search PostgreSQL documentation

NotesManual ChaptersPG18Notes Use the DROP VIEW statement to drop views. Be careful that the names and types of the view's columns will be assigned the way you want. For example: CREATE VIEW vista AS SELECT 'Hello …Notes NotesManual ChaptersPG18Notes Normal cursors return data in text format, the same as a SELECT would produce. The BINARY option specifies that the cursor should return data in binary format. This reduces conversion …Notes NotesManual ChaptersPG18Notes PostgreSQL lets you reference columns of other tables in the WHERE condition by specifying the other tables in the USING clause. For example, to delete all films produced by a given pr…Notes NotesManual ChaptersPG18Notes DISCARD ALL cannot be executed inside a transaction block.Notes NotesManual ChaptersPG18Notes The procedural language to be used must already have been installed into the current database by means of CREATE EXTENSION. plpgsql is installed by default, but other languages are not…Notes NotesManual ChaptersPG18Notes DROP OWNED is often used to prepare for the removal of one or more roles. Because DROP OWNED only affects the objects in the current database, it is usually necessary to execute this c…Notes NotesManual ChaptersPG18Notes Alternative syntaxes for referencing ordered-set aggregates are described under ALTER AGGREGATE.Notes NotesManual ChaptersPG18Notes DROP DATABASE cannot be executed inside a transaction block. This command cannot be executed while connected to the target database. Thus, it might be more convenient to use the progra…Notes NotesManual ChaptersPG18Notes DROP OPERATOR CLASS will not drop the operator family containing the class, even if there is nothing else left in the family (in particular, in the case where the family was implicitly…Notes NotesManual ChaptersPG18Notes If there is only one procedure of the given name, the argument list can be omitted. Omit the parentheses too in this case. In PostgreSQL, it's sufficient to list the input (including I…Notes NotesManual ChaptersPG18Notes PostgreSQL includes a program dropuser that has the same functionality as this command (in fact, it calls this command) but can be run from the command shell.Notes NotesManual ChaptersPG18Notes The 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 …Notes NotesManual ChaptersPG18Notes Using the CASCADE option might make the command remove objects in other schemas besides the one(s) named.Notes NotesManual ChaptersPG18Notes When 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 rep…Notes NotesManual ChaptersPG18Notes DROP TABLESPACE cannot be executed inside a transaction block.Notes NotesManual ChaptersPG18Notes Use ROLLBACK to abort a transaction. Issuing END when not inside a transaction does no harm, but it will provoke a warning message.Notes NotesManual ChaptersPG18Notes In 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 quer…Notes NotesManual ChaptersPG18Notes The 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 Postgr…Notes NotesManual ChaptersPG18Notes The 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 there…Notes NotesManual ChaptersPG18Notes If 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 o…Notes NotesManual ChaptersPG18Notes LISTEN 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…Notes NotesManual ChaptersPG18Notes To 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 permi…Notes NotesManual ChaptersPG18Notes The 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 f…Notes NotesManual ChaptersPG18Notes There 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 comm…Notes NotesManual ChaptersPG18Notes PREPARE 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 acros…Notes NotesManual ChaptersPG18Notes A 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 exec…Notes NotesManual ChaptersPG18Notes REASSIGN 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 …Notes NotesManual ChaptersPG18Notes If 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 no…Notes NotesManual ChaptersPG18Notes If 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 i…Notes NotesManual ChaptersPG18Notes Specifying 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…Notes NotesManual ChaptersPG18Notes A 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 i…Notes NotesManual ChaptersPG18Notes To 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 execu…Notes NotesManual ChaptersPG18Notes Use 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 …Notes NotesManual ChaptersPG18Notes Use 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 transac…Notes NotesManual ChaptersPG18Notes Use 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 esta…Notes NotesManual ChaptersPG18Notes CREATE 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 int…Notes NotesManual ChaptersPG18Notes Because 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…Notes NotesManual ChaptersPG18Notes Using 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 …Notes NotesManual ChaptersPG18Notes SET SESSION AUTHORIZATION cannot be used within a SECURITY DEFINER function.Notes NotesManual ChaptersPG18Notes If 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 sp…Notes