Search PostgreSQL documentation

NotesManual ChaptersPG18Notes START TRANSACTION has the same functionality as BEGIN. Use COMMIT or ROLLBACK to terminate a transaction block. Issuing BEGIN when already inside a transaction block will provoke a war…Notes NotesManual ChaptersPG18Notes The user must have EXECUTE privilege on the procedure in order to be allowed to invoke it. To call a function (not a procedure), use SELECT instead. If CALL is executed in a transactio…Notes NotesManual ChaptersPG18Notes PostgreSQL does not have an explicit OPEN cursor statement; a cursor is considered open when it is declared. Use the DECLARE statement to declare a cursor. You can see all available cu…Notes NotesManual ChaptersPG18Notes To cluster a table, one must have the MAINTAIN privilege on the table. In cases where you are accessing single rows randomly within a table, the actual order of the data in the table i…Notes NotesManual ChaptersPG18Notes There is presently no security mechanism for viewing comments: any user connected to a database can see all the comments for objects in that database. For shared objects such as databa…Notes NotesManual ChaptersPG18Notes To commit 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…Notes NotesManual ChaptersPG18Notes Use ROLLBACK to abort a transaction. Issuing COMMIT when not inside a transaction does no harm, but it will provoke a warning message. COMMIT AND CHAIN when not inside a transaction is…Notes NotesManual ChaptersPG18Notes COPY TO can be used with plain tables and populated materialized views. For example, COPY table TO copies the same rows as SELECT * FROM ONLY table. However it doesn't directly support…Notes NotesManual ChaptersPG18Notes In parameters that specify support function names, you can write a schema name if needed, for example SFUNC = public.sum. Do not write argument types there, however — the argument type…Notes NotesManual ChaptersPG18Notes Use DROP CAST to remove user-defined casts. Remember that if you want to be able to convert types both ways you need to declare casts both ways explicitly. It is normally not necessary…Notes NotesManual ChaptersPG18Notes CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock, which is self-conflicting, on the pg_collation system catalog, so only one CREATE COLLATION command can run at a time. Use DROP COLLA…Notes NotesManual ChaptersPG18Notes Neither the source nor the destination encoding can be SQL_ASCII, as the server's behavior for cases involving the SQL_ASCII “encoding” is hard-wired. Use DROP CONVERSION to remove use…Notes NotesManual ChaptersPG18Notes CREATE DATABASE cannot be executed inside a transaction block. Errors along the line of “could not initialize database directory” are most likely related to insufficient permissions on…Notes NotesManual ChaptersPG18Notes Domain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null desp…Notes NotesManual ChaptersPG18Notes Only superusers can create event triggers. Event triggers are disabled in single-user mode (see postgres) as well as when event_triggers is set to false. If an erroneous event trigger …Notes NotesManual ChaptersPG18Notes Before you can use CREATE EXTENSION to load an extension into a database, the extension's supporting files must be installed. Information about installing the extensions supplied with …Notes NotesManual ChaptersPG18Notes PostgreSQL's foreign-data functionality is still under active development. Optimization of queries is primitive (and mostly left to the wrapper, too). Thus, there is considerable room …Notes NotesManual ChaptersPG18Notes Constraints on foreign tables (such as CHECK or NOT NULL clauses) are not enforced by the core PostgreSQL system, and most foreign data wrappers do not attempt to enforce them either; …Notes NotesManual ChaptersPG18Notes The full SQL type syntax is allowed for declaring a function's arguments and return value. However, parenthesized type modifiers (e.g., the precision field for type numeric) are discar…Notes NotesManual ChaptersPG18Notes See Chapter 11 for information about when indexes can be used, when they are not used, and in which particular situations they can be useful. Currently, only the B-tree, GiST, GIN, and…Notes NotesManual ChaptersPG18Notes Use DROP LANGUAGE to drop procedural languages. The system catalog pg_language (see Section 52.29) records information about the currently installed languages. Also, the psql command \…Notes NotesManual ChaptersPG18Notes Because the index machinery does not check access permissions on functions before using them, including a function or operator in an operator class is tantamount to granting public exe…Notes NotesManual ChaptersPG18Notes Refer to Section 36.14 and Section 36.15 for further information. When you are defining a self-commutative operator, you just do it. When you are defining a pair of commutative operato…Notes NotesManual ChaptersPG18Notes You must be the owner of a table to create or change policies for it. While policies will be applied for explicit queries against tables in the database, they are not applied when the …Notes NotesManual ChaptersPG18Notes See CREATE FUNCTION for more details on function creation that also apply to procedures. Use CALL to execute a procedure.Notes NotesManual ChaptersPG18Notes If FOR TABLE, FOR ALL TABLES or FOR TABLES IN SCHEMA are not specified, then the publication starts out with an empty set of tables. That is useful if tables or schemas are to be added…Notes NotesManual ChaptersPG18Notes Use ALTER ROLE to change the attributes of a role, and DROP ROLE to remove a role. All the attributes specified by CREATE ROLE can be modified by later ALTER ROLE commands. The preferr…Notes NotesManual ChaptersPG18Notes You must be the owner of a table to create or change rules for it. In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING clause that emits the view's columns. This…Notes NotesManual ChaptersPG18Notes To create a schema, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)Notes 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