Search PostgreSQL documentation

NotesManual ChaptersPG18Notes Use DROP SEQUENCE to remove a sequence. Sequences are based on bigint arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 922337203685477…Notes NotesManual ChaptersPG18Notes When using the dblink module, a foreign server's name can be used as an argument of the dblink_connect function to indicate the connection parameters. It is necessary to have the USAGE…Notes NotesManual ChaptersPG18Notes You must be the owner of a table to create a statistics object reading it. Once created, however, the ownership of the statistics object is independent of the underlying table(s). Expr…Notes NotesManual ChaptersPG18Notes See Section 29.11 for details on how to configure access control between the subscription and the publication instance. When creating a replication slot (the default behavior), CREATE …Notes NotesManual ChaptersPG18Notes PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for prima…Notes NotesManual ChaptersPG18Notes This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS …Notes NotesManual ChaptersPG18Notes CREATE TABLESPACE cannot be executed inside a transaction block.Notes NotesManual ChaptersPG18Notes Use DROP TRANSFORM to remove transforms.Notes NotesManual ChaptersPG18Notes To create or replace a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function. Use DROP TRIGGER to…Notes NotesManual ChaptersPG18Notes The PARSER and COPY options are mutually exclusive, because when an existing configuration is copied, its parser selection is copied too.Notes NotesManual ChaptersPG18Notes Because there are no restrictions on use of a data type once it's been created, creating a base type or range type is tantamount to granting public execute permission on the functions …Notes 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