Search PostgreSQL documentation

NotesManual ChaptersPG18Notes These operations are also possible using ALTER TABLE. ALTER INDEX is in fact just an alias for the forms of ALTER TABLE that apply to indexes. There was formerly an ALTER INDEX OWNER v…Notes NotesManual ChaptersPG18Notes Refer to Section 36.14 and Section 36.15 for further information. Since commutators come in pairs that are commutators of each other, ALTER OPERATOR SET COMMUTATOR will also set the co…Notes NotesManual ChaptersPG18Notes Notice that the DROP syntax only specifies the “slot” in the operator family, by strategy or support number and input data type(s). The name of the operator or function occupying the s…Notes NotesManual ChaptersPG18Notes Use CREATE ROLE to add new roles, and DROP ROLE to remove a role. ALTER ROLE cannot change a role's memberships. Use GRANT and REVOKE to do that. Caution must be exercised when specify…Notes NotesManual ChaptersPG18Notes ALTER SEQUENCE will not immediately affect nextval results in backends, other than the current one, that have preallocated (cached) sequence values. They will use up all cached values …Notes NotesManual ChaptersPG18Notes This command can't be used to set data_directory, allow_alter_system, nor parameters that are not allowed in postgresql.conf (e.g., preset options). See Section 19.1 for other ways to …Notes NotesManual ChaptersPG18Notes The key word COLUMN is noise and can be omitted. When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default value is evaluated at the time of the state…Notes NotesManual ChaptersPG18Notes The ability to temporarily enable or disable a trigger is provided by ALTER TABLE, not by ALTER TRIGGER, because ALTER TRIGGER has no convenient way to express the option of enabling o…Notes NotesManual ChaptersPG18Notes If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been c…Notes NotesManual ChaptersPG18Notes For historical reasons, ALTER TABLE can be used with views too; but the only variants of ALTER TABLE that are allowed with views are equivalent to the ones shown above.Notes NotesManual ChaptersPG18Notes To analyze a table, one must ordinarily have the MAINTAIN privilege on the table. However, database owners are allowed to analyze all tables in their databases, except shared catalogs.…Notes 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