title: ALTER_LANGUAGE(7)

text:
# ALTER_LANGUAGE(7)

## NAME
ALTER_LANGUAGE - change the definition of a procedural language

## SYNOPSIS

```
ALTER [ PROCEDURAL ] LANGUAGE name RENAME TO new_name
ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
```

## DESCRIPTION
ALTER LANGUAGE changes the definition of a procedural language. The only functionality is to rename the language or assign a new owner. You must be superuser or owner of the language to use ALTER LANGUAGE.

## PARAMETERS
name

Name of a language

new_name

The new name of the language

new_owner

The new owner of the language

## COMPATIBILITY
There is no ALTER LANGUAGE statement in the SQL standard.

## SEE ALSO
CREATE LANGUAGE (CREATE_LANGUAGE(7)), DROP LANGUAGE (DROP_LANGUAGE(7))


title: ALTER_LARGE_OBJECT(7)

summary: change the definition of a large object

text:
# ALTER_LARGE_OBJECT(7)

## NAME
ALTER_LARGE_OBJECT - change the definition of a large object

## SYNOPSIS

```
ALTER LARGE OBJECT large_object_oid OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
```

## DESCRIPTION
ALTER LARGE OBJECT changes the definition of a large object.

You must own the large object to use ALTER LARGE OBJECT. To alter the owner, you must also be able to SET ROLE to the new owning role. (However, a superuser can alter any large object anyway.) Currently, the only functionality is to assign a new owner, so both restrictions always apply.

## PARAMETERS
large_object_oid

OID of the large object to be altered

new_owner

The new owner of the large object

## COMPATIBILITY
There is no ALTER LARGE OBJECT statement in the SQL standard.

## SEE ALSO
Chapter 33

branch: sisyphus


title: ALTER_MATERIALIZED_VIEW(7)

text:
# ALTER_MATERIALIZED_VIEW(7)

## NAME
ALTER_MATERIALIZED_VIEW - change the definition of a materialized view

## SYNOPSIS

```
ALTER MATERIALIZED VIEW [ IF EXISTS ] name

    action [, ... ]
ALTER MATERIALIZED VIEW name

    [ NO ] DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name

    RENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name

    RENAME TO new_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name

    SET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]

    SET TABLESPACE new_tablespace [ NOWAIT ]
where action is one of:

    ALTER [ COLUMN ] column_name SET STATISTICS integer

    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )

    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }

    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method

    CLUSTER ON index_name

    SET WITHOUT CLUSTER

    SET ACCESS METHOD new_access_method

    SET TABLESPACE new_tablespace

    SET ( storage_parameter [= value] [, ... ] )

    RESET ( storage_parameter [, ... ] )

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
```

## DESCRIPTION
ALTER MATERIALIZED VIEW changes various auxiliary properties of an existing materialized view.

You must own the materialized view to use ALTER MATERIALIZED VIEW. To change a materialized view's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the materialized view's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the materialized view. However, a superuser can alter ownership of any view anyway.)

The statement subforms and actions available for ALTER MATERIALIZED VIEW are a subset of those available for ALTER TABLE, and have the same meaning when used for materialized views. See the descriptions for ALTER TABLE for details.

## PARAMETERS
name

The name (optionally schema-qualified) of an existing materialized view.

column_name

Name of an existing column.

extension_name

The name of the extension that the materialized view is to depend on (or no longer dependent on, if NO is specified). A materialized view that's marked as dependent on an extension is automatically dropped when the extension is dropped.

new_column_name

New name for an existing column.

new_owner

The user name of the new owner of the materialized view.

new_name

The new name for the materialized view.

new_schema

The new schema for the materialized view.

## EXAMPLES
To rename the materialized view foo to bar:

```
ALTER MATERIALIZED VIEW foo RENAME TO bar;
```

## COMPATIBILITY
ALTER MATERIALIZED VIEW is a PostgreSQL extension.

## SEE ALSO
CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), DROP MATERIALIZED VIEW (DROP_MATERIALIZED_VIEW(7)), REFRESH MATERIALIZED VIEW (REFRESH_MATERIALIZED_VIEW(7))


title: ALTER_OPERATOR(7)

summary: change the definition of an operator

text:
# ALTER_OPERATOR(7)

## NAME
ALTER_OPERATOR - change the definition of an operator

## SYNOPSIS

```
ALTER OPERATOR name ( { left_type | NONE } , right_type )

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER OPERATOR name ( { left_type | NONE } , right_type )

    SET SCHEMA new_schema
ALTER OPERATOR name ( { left_type | NONE } , right_type )

    SET ( {  RESTRICT = { res_proc | NONE }

           | JOIN = { join_proc | NONE }

           | COMMUTATOR = com_op

           | NEGATOR = neg_op

           | HASHES

           | MERGES

          } [, ... ] )
```

## DESCRIPTION
ALTER OPERATOR changes the definition of an operator.

You must own the operator to use ALTER OPERATOR. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the operator's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the operator. However, a superuser can alter ownership of any operator anyway.)

## PARAMETERS
name

The name (optionally schema-qualified) of an existing operator.

left_type

The data type of the operator's left operand; write NONE if the operator has no left operand.

right_type

The data type of the operator's right operand.

new_owner

The new owner of the operator.

new_schema

The new schema for the operator.

res_proc

The restriction selectivity estimator function for this operator; write NONE to remove existing selectivity estimator.

join_proc

The join selectivity estimator function for this operator; write NONE to remove existing selectivity estimator.

com_op

The commutator of this operator. Can only be changed if the operator does not have an existing commutator.

neg_op

The negator of this operator. Can only be changed if the operator does not have an existing negator.

HASHES

Indicates this operator can support a hash join. Can only be enabled and not disabled.

MERGES

Indicates this operator can support a merge join. Can only be enabled and not disabled.

## NOTES
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 commutator of the com_op to be the target operator. Likewise, ALTER OPERATOR SET NEGATOR will also set the negator of the neg_op to be the target operator. Therefore, you must own the commutator or negator operator as well as the target operator.

## EXAMPLES
Change the owner of a custom operator a @@ b for type text:

```
ALTER OPERATOR @@ (text, text) OWNER TO joe;
```

Change the restriction and join selectivity estimator functions of a custom operator a && b for type int[]:

```
ALTER OPERATOR && (int[], int[]) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
```

Mark the && operator as being its own commutator:

```
ALTER OPERATOR && (int[], int[]) SET (COMMUTATOR = &&);
```

## COMPATIBILITY
There is no ALTER OPERATOR statement in the SQL standard.

## SEE ALSO
CREATE OPERATOR (CREATE_OPERATOR(7)), DROP OPERATOR (DROP_OPERATOR(7))


title: ALTER_OPERATOR_CLASS(7)

text:
# ALTER_OPERATOR_CLASS(7)

## NAME
ALTER_OPERATOR_CLASS - change the definition of an operator class

## SYNOPSIS

```
ALTER OPERATOR CLASS name USING index_method

    RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER OPERATOR CLASS name USING index_method

    SET SCHEMA new_schema
```

## DESCRIPTION
ALTER OPERATOR CLASS changes the definition of an operator class.

You must own the operator class to use ALTER OPERATOR CLASS. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the operator class's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the operator class. However, a superuser can alter ownership of any operator class anyway.)

## PARAMETERS
name

The name (optionally schema-qualified) of an existing operator class.

index_method

The name of the index method this operator class is for.

new_name

The new name of the operator class.

new_owner

The new owner of the operator class.

new_schema

The new schema for the operator class.

## COMPATIBILITY
There is no ALTER OPERATOR CLASS statement in the SQL standard.

## SEE ALSO
CREATE OPERATOR CLASS (CREATE_OPERATOR_CLASS(7)), DROP OPERATOR CLASS (DROP_OPERATOR_CLASS(7)), ALTER OPERATOR FAMILY (ALTER_OPERATOR_FAMILY(7))


title: ALTER_OPERATOR_FAMILY(7)

text:
# ALTER_OPERATOR_FAMILY(7)

## NAME
ALTER_OPERATOR_FAMILY - change the definition of an operator family

## SYNOPSIS

```
ALTER OPERATOR FAMILY name USING index_method ADD

  {  OPERATOR strategy_number operator_name ( op_type, op_type )

              [ FOR SEARCH | FOR ORDER BY sort_family_name ]

   | FUNCTION support_number [ ( op_type [ , op_type ] ) ]

              function_name [ ( argument_type [, ...] ) ]

  } [, ... ]
ALTER OPERATOR FAMILY name USING index_method DROP

  {  OPERATOR strategy_number ( op_type [ , op_type ] )

   | FUNCTION support_number ( op_type [ , op_type ] )

  } [, ... ]
ALTER OPERATOR FAMILY name USING index_method

    RENAME TO new_name
ALTER OPERATOR FAMILY name USING index_method

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER OPERATOR FAMILY name USING index_method

    SET SCHEMA new_schema
```

## DESCRIPTION
ALTER OPERATOR FAMILY changes the definition of an operator family. You can add operators and support functions to the family, remove them from the family, or change the family's name or owner.

When operators and support functions are added to a family with ALTER OPERATOR FAMILY, they are not part of any specific operator class within the family, but are just “loose” within the family. This indicates that these operators and functions are compatible with the family's semantics, but are not required for correct functioning of any specific index. (Operators and functions that are so required should be declared as part of an operator class, instead; see CREATE OPERATOR CLASS (CREATE_OPERATOR_CLASS(7)).) PostgreSQL will allow loose members of a family to be dropped from the family at any time, but members of an operator class cannot be dropped without dropping the whole class and any indexes that depend on it. Typically, single-data-type operators and functions are part of operator classes because they are needed to support an index on that specific data type, while cross-data-type operators and functions are made loose members of the family.

You must be a superuser to use ALTER OPERATOR FAMILY. (This restriction is made because an erroneous operator family definition could confuse or even crash the server.)

ALTER OPERATOR FAMILY does not presently check whether the operator family definition includes all the operators and functions required by the index method, nor whether the operators and functions form a self-consistent set. It is the user's responsibility to define a valid operator family.

Refer to Section 36.16 for further information.

## PARAMETERS
name

The name (optionally schema-qualified) of an existing operator family.

index_method

The name of the index method this operator family is for.

strategy_number

The index method's strategy number for an operator associated with the operator family.

operator_name

The name (optionally schema-qualified) of an operator associated with the operator family.

op_type

In an OPERATOR clause, the operand data type(s) of the operator, or NONE to signify a prefix operator. Unlike the comparable syntax in CREATE OPERATOR CLASS, the operand data types must always be specified. In an ADD FUNCTION clause, the operand data type(s) the function is intended to support, if different from the input data type(s) of the function. For B-tree comparison functions and hash functions it is not necessary to specify op_type since the function's input data type(s) are always the correct ones to use. For B-tree sort support functions, B-Tree equal image functions, and all functions in GiST, SP-GiST and GIN operator classes, it is necessary to specify the operand data type(s) the function is to be used with.

In a DROP FUNCTION clause, the operand data type(s) the function is intended to support must be specified.

sort_family_name

The name (optionally schema-qualified) of an existing btree operator family that describes the sort ordering associated with an ordering operator. If neither FOR SEARCH nor FOR ORDER BY is specified, FOR SEARCH is the default.

support_number

The index method's support function number for a function associated with the operator family.

function_name

The name (optionally schema-qualified) of a function that is an index method support function for the operator family. If no argument list is specified, the name must be unique in its schema.

argument_type

The parameter data type(s) of the function.

new_name

The new name of the operator family.

new_owner

The new owner of the operator family.

new_schema

The new schema for the operator family.

The OPERATOR and FUNCTION clauses can appear in any order.

## NOTES
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 slot is not mentioned. Also, for DROP FUNCTION the type(s) to specify are the input data type(s) the function is intended to support; for GiST, SP-GiST and GIN indexes this might have nothing to do with the actual input argument types of the function.

Because the index machinery does not check access permissions on functions before using them, including a function or operator in an operator family is tantamount to granting public execute permission on it. This is usually not an issue for the sorts of functions that are useful in an operator family.

The operators should not be defined by SQL functions. An SQL function is likely to be inlined into the calling query, which will prevent the optimizer from recognizing that the query matches an index.

Before PostgreSQL 8.4, the OPERATOR clause could include a RECHECK option. This is no longer supported because whether an index operator is “lossy” is now determined on-the-fly at run time. This allows efficient handling of cases where an operator might or might not be lossy.

## EXAMPLES
The following example command adds cross-data-type operators and support functions to an operator family that already contains B-tree operator classes for data types int4 and int2.

```
ALTER OPERATOR FAMILY integer_ops USING btree ADD

  -- int4 vs int2

  OPERATOR 1 < (int4, int2) ,

  OPERATOR 2 <= (int4, int2) ,

  OPERATOR 3 = (int4, int2) ,

  OPERATOR 4 >= (int4, int2) ,

  OPERATOR 5 > (int4, int2) ,

  FUNCTION 1 btint42cmp(int4, int2) ,

  -- int2 vs int4

  OPERATOR 1 < (int2, int4) ,

  OPERATOR 2 <= (int2, int4) ,

  OPERATOR 3 = (int2, int4) ,

  OPERATOR 4 >= (int2, int4) ,

  OPERATOR 5 > (int2, int4) ,

  FUNCTION 1 btint24cmp(int2, int4) ;
```

To remove these entries again:

```
ALTER OPERATOR FAMILY integer_ops USING btree DROP

  -- int4 vs int2

  OPERATOR 1 (int4, int2) ,

  OPERATOR 2 (int4, int2) ,

  OPERATOR 3 (int4, int2) ,

  OPERATOR 4 (int4, int2) ,

  OPERATOR 5 (int4, int2) ,

  FUNCTION 1 (int4, int2) ,

  -- int2 vs int4

  OPERATOR 1 (int2, int4) ,

  OPERATOR 2 (int2, int4) ,

  OPERATOR 3 (int2, int4) ,

  OPERATOR 4 (int2, int4) ,

  OPERATOR 5 (int2, int4) ,

  FUNCTION 1 (int2, int4) ;
```

## COMPATIBILITY
There is no ALTER OPERATOR FAMILY statement in the SQL standard.

## SEE ALSO
CREATE OPERATOR FAMILY (CREATE_OPERATOR_FAMILY(7)), DROP OPERATOR FAMILY (DROP_OPERATOR_FAMILY(7)), CREATE OPERATOR CLASS (CREATE_OPERATOR_CLASS(7)), ALTER OPERATOR CLASS (ALTER_OPERATOR_CLASS(7)), DROP OPERATOR CLASS (DROP_OPERATOR_CLASS(7))


title: ALTER_POLICY(7)

summary: change the definition of a row\-level security policy

text:
# ALTER_POLICY(7)

## NAME
ALTER_POLICY - change the definition of a row-level security policy

## SYNOPSIS

```
ALTER POLICY name ON table_name RENAME TO new_name
ALTER POLICY name ON table_name

    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]

    [ USING ( using_expression ) ]

    [ WITH CHECK ( check_expression ) ]
```

## DESCRIPTION
ALTER POLICY changes the definition of an existing row-level security policy. Note that ALTER POLICY only allows the set of roles to which the policy applies and the USING and WITH CHECK expressions to be modified. To change other properties of a policy, such as the command to which it applies or whether it is permissive or restrictive, the policy must be dropped and recreated.

To use ALTER POLICY, you must own the table that the policy applies to.

In the second form of ALTER POLICY, the role list, using_expression, and check_expression are replaced independently if specified. When one of those clauses is omitted, the corresponding part of the policy is unchanged.

## PARAMETERS
name

The name of an existing policy to alter.

table_name

The name (optionally schema-qualified) of the table that the policy is on.

new_name

The new name for the policy.

role_name

The role(s) to which the policy applies. Multiple roles can be specified at one time. To apply the policy to all roles, use PUBLIC.

using_expression

The USING expression for the policy. See CREATE POLICY (CREATE_POLICY(7)) for details.

check_expression

The WITH CHECK expression for the policy. See CREATE POLICY (CREATE_POLICY(7)) for details.

## COMPATIBILITY
ALTER POLICY is a PostgreSQL extension.

## SEE ALSO
CREATE POLICY (CREATE_POLICY(7)), DROP POLICY (DROP_POLICY(7))


title: ALTER_PROCEDURE(7)

summary: change the definition of a procedure

text:
# ALTER_PROCEDURE(7)

## NAME
ALTER_PROCEDURE - change the definition of a procedure

## SYNOPSIS

```
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    action [ ... ] [ RESTRICT ]
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    RENAME TO new_name
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    SET SCHEMA new_schema
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    [ NO ] DEPENDS ON EXTENSION extension_name
where action is one of:

    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

    SET configuration_parameter { TO | = } { value | DEFAULT }

    SET configuration_parameter FROM CURRENT

    RESET configuration_parameter

    RESET ALL
```

## DESCRIPTION
ALTER PROCEDURE changes the definition of a procedure.

You must own the procedure to use ALTER PROCEDURE. To change a procedure's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the procedure's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the procedure. However, a superuser can alter ownership of any procedure anyway.)

## PARAMETERS
name

The name (optionally schema-qualified) of an existing procedure. If no argument list is specified, the name must be unique in its schema.

argmode

The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN.

argname

The name of an argument. Note that ALTER PROCEDURE does not actually pay any attention to argument names, since only the argument data types are used to determine the procedure's identity.

argtype

The data type(s) of the procedure's arguments (optionally schema-qualified), if any. See DROP PROCEDURE (DROP_PROCEDURE(7)) for the details of how the procedure is looked up using the argument data type(s).

new_name

The new name of the procedure.

new_owner

The new owner of the procedure. Note that if the procedure is marked SECURITY DEFINER, it will subsequently execute as the new owner.

new_schema

The new schema for the procedure.

extension_name

This form marks the procedure as dependent on the extension, or no longer dependent on the extension if NO is specified. A procedure that's marked as dependent on an extension is dropped when the extension is dropped, even if cascade is not specified. A procedure can depend upon multiple extensions, and will be dropped when any one of those extensions is dropped.

[ EXTERNAL ] SECURITY INVOKER

[ EXTERNAL ] SECURITY DEFINER

Change whether the procedure is a security definer or not. The key word EXTERNAL is ignored for SQL conformance. See CREATE PROCEDURE (CREATE_PROCEDURE(7)) for more information about this capability.

configuration_parameter

value

Add or change the assignment to be made to a configuration parameter when the procedure is called. If value is DEFAULT or, equivalently, RESET is used, the procedure-local setting is removed, so that the procedure executes with the value present in its environment. Use RESET ALL to clear all procedure-local settings. SET FROM CURRENT saves the value of the parameter that is current when ALTER PROCEDURE is executed as the value to be applied when the procedure is entered. See SET(7) and Chapter 19 for more information about allowed parameter names and values.

RESTRICT

Ignored for conformance with the SQL standard.

## EXAMPLES
To rename the procedure insert_data with two arguments of type integer to insert_record:

```
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
```

To change the owner of the procedure insert_data with two arguments of type integer to joe:

```
ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
```

To change the schema of the procedure insert_data with two arguments of type integer to accounting:

```
ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
```

To mark the procedure insert_data(integer, integer) as being dependent on the extension myext:

```
ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
```

To adjust the search path that is automatically set for a procedure:

```
ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
```

To disable automatic setting of search_path for a procedure:

```
ALTER PROCEDURE check_password(text) RESET search_path;
```

The procedure will now execute with whatever search path is used by its caller.

## COMPATIBILITY
This statement is partially compatible with the ALTER PROCEDURE statement in the SQL standard. The standard allows more properties of a procedure to be modified, but does not provide the ability to rename a procedure, make a procedure a security definer, attach configuration parameter values to a procedure, or change the owner, schema, or volatility of a procedure. The standard also requires the RESTRICT key word, which is optional in PostgreSQL.

## SEE ALSO
CREATE PROCEDURE (CREATE_PROCEDURE(7)), DROP PROCEDURE (DROP_PROCEDURE(7)), ALTER FUNCTION (ALTER_FUNCTION(7)), ALTER ROUTINE (ALTER_ROUTINE(7))


title: ALTER_PUBLICATION(7)

summary: change the definition of a publication

text:
# ALTER_PUBLICATION(7)

## NAME
ALTER_PUBLICATION - change the definition of a publication

## SYNOPSIS

```
ALTER PUBLICATION name ADD publication_object [, ...]
ALTER PUBLICATION name SET publication_object [, ...]
ALTER PUBLICATION name DROP publication_drop_object [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name
where publication_object is one of:

    TABLE table_and_columns [, ... ]

    TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
and publication_drop_object is one of:

    TABLE [ ONLY ] table_name [ * ] [, ... ]

    TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
and table_and_columns is:

    [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]
```

## DESCRIPTION
The command ALTER PUBLICATION can change the attributes of a publication.

The first three variants change which tables/schemas are part of the publication. The SET clause will replace the list of tables/schemas in the publication with the specified list; the existing tables/schemas that were present in the publication will be removed. The ADD and DROP clauses will add and remove one or more tables/schemas from the publication. Note that adding tables/schemas to a publication that is already subscribed to will require an ALTER SUBSCRIPTION ... REFRESH PUBLICATION action on the subscribing side in order to become effective. Note also that DROP TABLES IN SCHEMA will not drop any schema tables that were specified using FOR TABLE/ ADD TABLE.

The fourth variant of this command listed in the synopsis can change all of the publication properties specified in CREATE PUBLICATION (CREATE_PUBLICATION(7)). Properties not mentioned in the command retain their previous settings.

The remaining variants change the owner and the name of the publication.

You must own the publication to use ALTER PUBLICATION. Adding a table to a publication additionally requires owning that table. The ADD TABLES IN SCHEMA and SET TABLES IN SCHEMA to a publication requires the invoking user to be a superuser. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the database. Also, the new owner of a FOR ALL TABLES or FOR TABLES IN SCHEMA publication must be a superuser. However, a superuser can change the ownership of a publication regardless of these restrictions.

Adding/Setting any schema when the publication also publishes a table with a column list, and vice versa is not supported.

## PARAMETERS
name

The name of an existing publication whose definition is to be altered.

table_name

Name of an existing table. If ONLY is specified before the table name, only that table is affected. If ONLY is not specified, the table and all its descendant tables (if any) are affected. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included. Optionally, a column list can be specified. See CREATE PUBLICATION (CREATE_PUBLICATION(7)) for details. Note that a subscription having several publications in which the same table has been published with different column lists is not supported. See Warning: Combining Column Lists from Multiple Publications for details of potential problems when altering column lists.

If the optional WHERE clause is specified, rows for which the expression evaluates to false or null will not be published. Note that parentheses are required around the expression. The expression is evaluated with the role used for the replication connection.

schema_name

Name of an existing schema.

SET ( publication_parameter [= value] [, ... ] )

This clause alters publication parameters originally set by CREATE PUBLICATION (CREATE_PUBLICATION(7)). See there for more information.

new_owner

The user name of the new owner of the publication.

new_name

The new name for the publication.

## EXAMPLES
Change the publication to publish only deletes and updates:

```
ALTER PUBLICATION noinsert SET (publish = 'update, delete');
```

Add some tables to the publication:

```
ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), departments;
```

Change the set of columns published for a table:

```
ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
```

Add schemas marketing and sales to the publication sales_publication:

```
ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
```

Add tables users, departments and schema production to the publication production_publication:

```
ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
```

## COMPATIBILITY
ALTER PUBLICATION is a PostgreSQL extension.

## SEE ALSO
CREATE PUBLICATION (CREATE_PUBLICATION(7)), DROP PUBLICATION (DROP_PUBLICATION(7)), CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)), ALTER SUBSCRIPTION (ALTER_SUBSCRIPTION(7))


title: ALTER_ROLE(7)

summary: change a database role

text:
# ALTER_ROLE(7)

## NAME
ALTER_ROLE - change a database role

## SYNOPSIS

```
ALTER ROLE role_specification [ WITH ] option [ ... ]
where option can be:

      SUPERUSER | NOSUPERUSER

    | CREATEDB | NOCREATEDB

    | CREATEROLE | NOCREATEROLE

    | INHERIT | NOINHERIT

    | LOGIN | NOLOGIN

    | REPLICATION | NOREPLICATION

    | BYPASSRLS | NOBYPASSRLS

    | CONNECTION LIMIT connlimit

    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL

    | VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:

    role_name

  | CURRENT_ROLE

  | CURRENT_USER

  | SESSION_USER
```

## DESCRIPTION
ALTER ROLE changes the attributes of a PostgreSQL role.

The first variant of this command listed in the synopsis can change many of the role attributes that can be specified in CREATE ROLE. (All the possible attributes are covered, except that there are no options for adding or removing memberships; use GRANT and REVOKE for that.) Attributes not mentioned in the command retain their previous settings. Database superusers can change any of these settings for any role, except for changing the SUPERUSER property for the bootstrap superuser. Non-superuser roles having CREATEROLE privilege can change most of these properties, but only for non-superuser and non-replication roles for which they have been granted ADMIN OPTION. Non-superusers cannot change the SUPERUSER property and can change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they possess the corresponding property themselves. Ordinary roles can only change their own password.

The second variant changes the name of the role. Database superusers can rename any role. Roles having CREATEROLE privilege can rename non-superuser roles for which they have been granted ADMIN OPTION. The current session user cannot be renamed. (Connect as a different user if you need to do that.) Because MD5-encrypted passwords use the role name as cryptographic salt, renaming a role clears its password if the password is MD5-encrypted.

The remaining variants change a role's session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. If ALL is specified instead of a role name, this changes the setting for all roles. Using ALL with IN DATABASE is effectively the same as using the command ALTER DATABASE ... SET ....

Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set. Settings set for all databases are overridden by database-specific settings attached to a role. Settings for specific databases or specific roles override settings for all roles.

Superusers can change anyone's session defaults. Roles having CREATEROLE privilege can change defaults for non-superuser roles for which they have been granted ADMIN OPTION. Ordinary roles can only set defaults for themselves. Certain configuration variables cannot be set this way, or can only be set if a superuser issues the command. Only superusers can change a setting for all roles in all databases.

## PARAMETERS
name

The name of the role whose attributes are to be altered.

CURRENT_ROLE

CURRENT_USER

Alter the current user instead of an explicitly identified role.

SESSION_USER

Alter the current session user instead of an explicitly identified role.

SUPERUSER

NOSUPERUSER

CREATEDB

NOCREATEDB

CREATEROLE

NOCREATEROLE

INHERIT

NOINHERIT

LOGIN

NOLOGIN

REPLICATION

NOREPLICATION

BYPASSRLS

NOBYPASSRLS

CONNECTION LIMIT connlimit

[ ENCRYPTED ] PASSWORD 'password'

PASSWORD NULL

VALID UNTIL 'timestamp'

These clauses alter attributes originally set by CREATE ROLE. For more information, see the CREATE ROLE reference page.

new_name

The new name of the role.

database_name

The name of the database the configuration variable should be set in.

configuration_parameter

value

Set this role's session default for the specified configuration parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the role-specific variable setting is removed, so the role will inherit the system-wide default setting in new sessions. Use RESET ALL to clear all role-specific settings. SET FROM CURRENT saves the session's current value of the parameter as the role-specific value. If IN DATABASE is specified, the configuration parameter is set or removed for the given role and database only. Role-specific variable settings take effect only at login; SET ROLE and SET SESSION AUTHORIZATION do not process role-specific variable settings.

See SET(7) and Chapter 19 for more information about allowed parameter names and values.

## NOTES
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 specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client's command history or the server log. psql(1) contains a command \password that can be used to change a role's password without exposing the cleartext password.

It is also possible to tie a session default to a specific database rather than to a role; see ALTER DATABASE (ALTER_DATABASE(7)). If there is a conflict, database-role-specific settings override role-specific ones, which in turn override database-specific ones.

## EXAMPLES
Change a role's password:

```
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
```

Remove a role's password:

```
ALTER ROLE davide WITH PASSWORD NULL;
```

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:

```
ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
```

Make a password valid forever:

```
ALTER ROLE fred VALID UNTIL 'infinity';
```

Give a role the ability to manage other roles and create new databases:

```
ALTER ROLE miriam CREATEROLE CREATEDB;
```

Give a role a non-default setting of the maintenance_work_mem parameter:

```
ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
```

Give a role a non-default, database-specific setting of the client_min_messages parameter:

```
ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
```

## COMPATIBILITY
The ALTER ROLE statement is a PostgreSQL extension.

## SEE ALSO
CREATE ROLE (CREATE_ROLE(7)), DROP ROLE (DROP_ROLE(7)), ALTER DATABASE (ALTER_DATABASE(7)), SET(7)


title: ALTER_ROUTINE(7)

summary: change the definition of a routine

text:
# ALTER_ROUTINE(7)

## NAME
ALTER_ROUTINE - change the definition of a routine

## SYNOPSIS

```
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    action [ ... ] [ RESTRICT ]
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    RENAME TO new_name
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    SET SCHEMA new_schema
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]

    [ NO ] DEPENDS ON EXTENSION extension_name
where action is one of:

    IMMUTABLE | STABLE | VOLATILE

    [ NOT ] LEAKPROOF

    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

    PARALLEL { UNSAFE | RESTRICTED | SAFE }

    COST execution_cost

    ROWS result_rows

    SET configuration_parameter { TO | = } { value | DEFAULT }

    SET configuration_parameter FROM CURRENT

    RESET configuration_parameter

    RESET ALL
```

## DESCRIPTION
ALTER ROUTINE changes the definition of a routine, which can be an aggregate function, a normal function, or a procedure. See under ALTER AGGREGATE (ALTER_AGGREGATE(7)), ALTER FUNCTION (ALTER_FUNCTION(7)), and ALTER PROCEDURE (ALTER_PROCEDURE(7)) for the description of the parameters, more examples, and further details.

## EXAMPLES
To rename the routine foo for type integer to foobar:

```
ALTER ROUTINE foo(integer) RENAME TO foobar;
```

This command will work independent of whether foo is an aggregate, function, or procedure.

## COMPATIBILITY
This statement is partially compatible with the ALTER ROUTINE statement in the SQL standard. See under ALTER FUNCTION (ALTER_FUNCTION(7)) and ALTER PROCEDURE (ALTER_PROCEDURE(7)) for more details. Allowing routine names to refer to aggregate functions is a PostgreSQL extension.

## SEE ALSO
ALTER AGGREGATE (ALTER_AGGREGATE(7)), ALTER FUNCTION (ALTER_FUNCTION(7)), ALTER PROCEDURE (ALTER_PROCEDURE(7)), DROP ROUTINE (DROP_ROUTINE(7))

Note that there is no CREATE ROUTINE command.


title: ALTER_RULE(7)

summary: change the definition of a rule

text:
# ALTER_RULE(7)

## NAME
ALTER_RULE - change the definition of a rule

## SYNOPSIS

```
ALTER RULE name ON table_name RENAME TO new_name
```

## DESCRIPTION
ALTER RULE changes properties of an existing rule. Currently, the only available action is to change the rule's name.

To use ALTER RULE, you must own the table or view that the rule applies to.

## PARAMETERS
name

The name of an existing rule to alter.

table_name

The name (optionally schema-qualified) of the table or view that the rule applies to.

new_name

The new name for the rule.

## EXAMPLES
To rename an existing rule:

```
ALTER RULE notify_all ON emp RENAME TO notify_me;
```

## COMPATIBILITY
ALTER RULE is a PostgreSQL language extension, as is the entire query rewrite system.

## SEE ALSO
CREATE RULE (CREATE_RULE(7)), DROP RULE (DROP_RULE(7))


title: ALTER_SCHEMA(7)

summary: change the definition of a schema

text:
# ALTER_SCHEMA(7)

## NAME
ALTER_SCHEMA - change the definition of a schema

## SYNOPSIS

```
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
```

## DESCRIPTION
ALTER SCHEMA changes the definition of a schema.

You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have the CREATE privilege for the database. (Note that superusers have all these privileges automatically.)

## PARAMETERS
name

The name of an existing schema.

new_name

The new name of the schema. The new name cannot begin with pg_, as such names are reserved for system schemas.

new_owner

The new owner of the schema.

## COMPATIBILITY
There is no ALTER SCHEMA statement in the SQL standard.

## SEE ALSO
CREATE SCHEMA (CREATE_SCHEMA(7)), DROP SCHEMA (DROP_SCHEMA(7))


title: ALTER_SEQUENCE(7)

text:
# ALTER_SEQUENCE(7)

## NAME
ALTER_SEQUENCE - change the definition of a sequence generator

## SYNOPSIS

```
ALTER SEQUENCE [ IF EXISTS ] name

    [ AS data_type ]

    [ INCREMENT [ BY ] increment ]

    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]

    [ START [ WITH ] start ]

    [ RESTART [ [ WITH ] restart ] ]

    [ CACHE cache ] [ [ NO ] CYCLE ]

    [ OWNED BY { table_name.column_name | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] name SET { LOGGED | UNLOGGED }
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema
```

## DESCRIPTION
ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings.

You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the sequence's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.)

## PARAMETERS
name

The name (optionally schema-qualified) of a sequence to be altered.

IF EXISTS

Do not throw an error if the sequence does not exist. A notice is issued in this case.

data_type

The optional clause AS data_type changes the data type of the sequence. Valid types are smallint, integer, and bigint. Changing the data type automatically changes the minimum and maximum values of the sequence if and only if the previous minimum and maximum values were the minimum or maximum value of the old data type (in other words, if the sequence had been created using NO MINVALUE or NO MAXVALUE, implicitly or explicitly). Otherwise, the minimum and maximum values are preserved, unless new values are given as part of the same command. If the minimum and maximum values do not fit into the new data type, an error will be generated.

increment

The clause INCREMENT BY increment is optional. A positive value will make an ascending sequence, a negative one a descending sequence. If unspecified, the old increment value will be maintained.

minvalue

NO MINVALUE

The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If NO MINVALUE is specified, the defaults of 1 and the minimum value of the data type for ascending and descending sequences, respectively, will be used. If neither option is specified, the current minimum value will be maintained.

maxvalue

NO MAXVALUE

The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If NO MAXVALUE is specified, the defaults of the maximum value of the data type and -1 for ascending and descending sequences, respectively, will be used. If neither option is specified, the current maximum value will be maintained.

start

The optional clause START WITH start changes the recorded start value of the sequence. This has no effect on the current sequence value; it simply sets the value that future ALTER SEQUENCE RESTART commands will use.

restart

The optional clause RESTART [ WITH restart ] changes the current value of the sequence. This is similar to calling the setval function with is_called = false: the specified value will be returned by the next call of nextval. Writing RESTART with no restart value is equivalent to supplying the start value that was recorded by CREATE SEQUENCE or last set by ALTER SEQUENCE START WITH. In contrast to a setval call, a RESTART operation on a sequence is transactional and blocks concurrent transactions from obtaining numbers from the same sequence. If that's not the desired mode of operation, setval should be used.

cache

The clause CACHE cache enables sequence numbers to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache). If unspecified, the old cache value will be maintained.

CYCLE

The optional CYCLE key word can be used to enable the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.

NO CYCLE

If the optional NO CYCLE key word is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If neither CYCLE or NO CYCLE are specified, the old cycle behavior will be maintained.

SET { LOGGED | UNLOGGED }

This form changes the sequence from unlogged to logged or vice-versa (see CREATE SEQUENCE (CREATE_SEQUENCE(7))). It cannot be applied to a temporary sequence.

OWNED BY table_name.column_name

OWNED BY NONE

The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. If specified, this association replaces any previously specified association for the sequence. The specified table must have the same owner and be in the same schema as the sequence. Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

new_owner

The user name of the new owner of the sequence.

new_name

The new name for the sequence.

new_schema

The new schema for the sequence.

## NOTES
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 prior to noticing the changed sequence generation parameters. The current backend will be affected immediately.

ALTER SEQUENCE does not affect the currval status for the sequence. (Before PostgreSQL 8.3, it sometimes did.)

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

For historical reasons, ALTER TABLE can be used with sequences too; but the only variants of ALTER TABLE that are allowed with sequences are equivalent to the forms shown above.

## EXAMPLES
Restart a sequence called serial, at 105:

```
ALTER SEQUENCE serial RESTART WITH 105;
```

## COMPATIBILITY
ALTER SEQUENCE conforms to the SQL standard, except for the AS, START WITH, OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses, which are PostgreSQL extensions.

## SEE ALSO
CREATE SEQUENCE (CREATE_SEQUENCE(7)), DROP SEQUENCE (DROP_SEQUENCE(7))


title: ALTER_SERVER(7)

text:
# ALTER_SERVER(7)

## NAME
ALTER_SERVER - change the definition of a foreign server

## SYNOPSIS

```
ALTER SERVER name [ VERSION 'new_version' ]

    [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
ALTER SERVER name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SERVER name RENAME TO new_name
```

## DESCRIPTION
ALTER SERVER changes the definition of a foreign server. The first form changes the server version string or the generic options of the server (at least one clause is required). The second form changes the owner of the server.

To alter the server you must be the owner of the server. Additionally to alter the owner, you must be able to SET ROLE to the new owning role, and you must have USAGE privilege on the server's foreign-data wrapper. (Note that superusers satisfy all these criteria automatically.)

## PARAMETERS
name

The name of an existing server.

new_version

New server version.

OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )

Change options for the server. ADD, SET, and DROP specify the action to be performed. ADD is assumed if no operation is explicitly specified. Option names must be unique; names and values are also validated using the server's foreign-data wrapper library.

new_owner

The user name of the new owner of the foreign server.

new_name

The new name for the foreign server.

## EXAMPLES
Alter server foo, add connection options:

```
ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');
```

Alter server foo, change version, change host option:

```
ALTER SERVER foo VERSION '8.4' OPTIONS (SET host 'baz');
```

## COMPATIBILITY
ALTER SERVER conforms to ISO/IEC 9075-9 (SQL/MED). The OWNER TO and RENAME forms are PostgreSQL extensions.

## SEE ALSO
CREATE SERVER (CREATE_SERVER(7)), DROP SERVER (DROP_SERVER(7))


title: ALTER_STATISTICS(7)

text:
# ALTER_STATISTICS(7)

## NAME
ALTER_STATISTICS - change the definition of an extended statistics object

## SYNOPSIS

```
ALTER STATISTICS name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER STATISTICS name RENAME TO new_name
ALTER STATISTICS name SET SCHEMA new_schema
ALTER STATISTICS name SET STATISTICS { new_target | DEFAULT }
```

## DESCRIPTION
ALTER STATISTICS changes the parameters of an existing extended statistics object. Any parameters not specifically set in the ALTER STATISTICS command retain their prior settings.

You must own the statistics object to use ALTER STATISTICS. To change a statistics object's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the statistics object's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the statistics object. However, a superuser can alter ownership of any statistics object anyway.)

## PARAMETERS
name

The name (optionally schema-qualified) of the statistics object to be altered.

new_owner

The user name of the new owner of the statistics object.

new_name

The new name for the statistics object.

new_schema

The new schema for the statistics object.

new_target

The statistic-gathering target for this statistics object for subsequent ANALYZE operations. The target can be set in the range 0 to 10000. Set it to DEFAULT to revert to using the system default statistics target (default_statistics_target). (Setting to a value of -1 is an obsolete way spelling to get the same outcome.) For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.

## COMPATIBILITY
There is no ALTER STATISTICS command in the SQL standard.

## SEE ALSO
CREATE STATISTICS (CREATE_STATISTICS(7)), DROP STATISTICS (DROP_STATISTICS(7))


title: ALTER_SUBSCRIPTION(7)

summary: change the definition of a subscription

text:
# ALTER_SUBSCRIPTION(7)

## NAME
ALTER_SUBSCRIPTION - change the definition of a subscription

## SYNOPSIS

```
ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name SKIP ( skip_option = value )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name
```

## DESCRIPTION
ALTER SUBSCRIPTION can change most of the subscription properties that can be specified in CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)).

You must own the subscription to use ALTER SUBSCRIPTION. To rename a subscription or alter the owner, you must have CREATE permission on the database. In addition, to alter the owner, you must be able to SET ROLE to the new owning role. If the subscription has password_required=false, only superusers can modify it.

When refreshing a publication we remove the relations that are no longer part of the publication and we also remove the table synchronization slots if there are any. It is necessary to remove these slots so that the resources allocated for the subscription on the remote host are released. If due to network breakdown or some other error, PostgreSQL is unable to remove the slots, an error will be reported. To proceed in this situation, the user either needs to retry the operation or disassociate the slot from the subscription and drop the subscription as explained in DROP SUBSCRIPTION (DROP_SUBSCRIPTION(7)).

Commands ALTER SUBSCRIPTION ... REFRESH PUBLICATION, ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ... with refresh option as true and ALTER SUBSCRIPTION ... SET (failover = true|false) cannot be executed inside a transaction block.

Commands ALTER SUBSCRIPTION ... REFRESH PUBLICATION and ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ... with refresh option as true also cannot be executed when the subscription has two_phase commit enabled, unless copy_data is false. See column subtwophasestate of pg_subscription to know the actual two-phase state.

## PARAMETERS
name

The name of a subscription whose properties are to be altered.

CONNECTION 'conninfo'

This clause replaces the connection string originally set by CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)). See there for more information.

SET PUBLICATION publication_name

ADD PUBLICATION publication_name

DROP PUBLICATION publication_name

These forms change the list of subscribed publications. SET replaces the entire list of publications with a new list, ADD adds additional publications to the list of publications, and DROP removes the publications from the list of publications. We allow non-existent publications to be specified in ADD and SET variants so that users can add those later. See CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)) for more information. By default, this command will also act like REFRESH PUBLICATION. publication_option specifies additional options for this operation. The supported options are:

refresh (boolean)

When false, the command will not try to refresh table information. REFRESH PUBLICATION should then be executed separately. The default is true.

Additionally, the options described under REFRESH PUBLICATION may be specified, to control the implicit refresh operation.

REFRESH PUBLICATION

Fetch missing table information from publisher. This will start replication of tables that were added to the subscribed-to publications since CREATE SUBSCRIPTION or the last invocation of REFRESH PUBLICATION. refresh_option specifies additional options for the refresh operation. The supported options are:

copy_data (boolean)

Specifies whether to copy pre-existing data in the publications that are being subscribed to when the replication starts. The default is true. Previously subscribed tables are not copied, even if a table's row filter WHERE clause has since been modified.

See Notes for details of how copy_data = true can interact with the origin parameter.

See the binary parameter of CREATE SUBSCRIPTION for details about copying pre-existing data in binary format.

ENABLE

Enables a previously disabled subscription, starting the logical replication worker at the end of the transaction.

DISABLE

Disables a running subscription, stopping the logical replication worker at the end of the transaction.

SET ( subscription_parameter [= value] [, ... ] )

This clause alters parameters originally set by CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)). See there for more information. The parameters that can be altered are slot_name, synchronous_commit, binary, streaming, disable_on_error, password_required, run_as_owner, origin, and failover. Only a superuser can set password_required = false. When altering the slot_name, the failover and two_phase property values of the named slot may differ from the counterpart failover and two_phase parameters specified in the subscription. When creating the slot, ensure the slot properties failover and two_phase match their counterpart parameters of the subscription. Otherwise, the slot on the publisher may behave differently from what these subscription options say: for example, the slot on the publisher could either be synced to the standbys even when the subscription's failover option is disabled or could be disabled for sync even when the subscription's failover option is enabled.

SKIP ( skip_option = value )

Skips applying all changes of the remote transaction. If incoming data violates any constraints, logical replication will stop until it is resolved. By using the ALTER SUBSCRIPTION ... SKIP command, the logical replication worker skips all data modification changes within the transaction. This option has no effect on the transactions that are already prepared by enabling two_phase on the subscriber. After the logical replication worker successfully skips the transaction or finishes a transaction, the LSN (stored in pg_subscription.subskiplsn) is cleared. See Section 29.6 for the details of logical replication conflicts. skip_option specifies options for this operation. The supported option is:

lsn (pg_lsn)

Specifies the finish LSN of the remote transaction whose changes are to be skipped by the logical replication worker. The finish LSN is the LSN at which the transaction is either committed or prepared. Skipping individual subtransactions is not supported. Setting NONE resets the LSN.

new_owner

The user name of the new owner of the subscription.

new_name

The new name for the subscription.

When specifying a parameter of type boolean, the = value part can be omitted, which is equivalent to specifying TRUE.

## EXAMPLES
Change the publication subscribed by a subscription to insert_only:

```
ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;
```

Disable (stop) the subscription:

```
ALTER SUBSCRIPTION mysub DISABLE;
```

## COMPATIBILITY
ALTER SUBSCRIPTION is a PostgreSQL extension.

## SEE ALSO
CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)), DROP SUBSCRIPTION (DROP_SUBSCRIPTION(7)), CREATE PUBLICATION (CREATE_PUBLICATION(7)), ALTER PUBLICATION (ALTER_PUBLICATION(7))


title: ALTER_SYSTEM(7)

summary: change a server configuration parameter

text:
# ALTER_SYSTEM(7)

## NAME
ALTER_SYSTEM - change a server configuration parameter

## SYNOPSIS

```
ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
```

## DESCRIPTION
ALTER SYSTEM is used for changing server configuration parameters across the entire database cluster. It can be more convenient than the traditional method of manually editing the postgresql.conf file. ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf. Setting a parameter to DEFAULT, or using the RESET variant, removes that configuration entry from the postgresql.auto.conf file. Use RESET ALL to remove all such configuration entries.

Values set with ALTER SYSTEM will be effective after the next server configuration reload, or after the next server restart in the case of parameters that can only be changed at server start. A server configuration reload can be commanded by calling the SQL function pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal to the main server process.

Only superusers and users granted ALTER SYSTEM privilege on a parameter can change it using ALTER SYSTEM. Also, since this command acts directly on the file system and cannot be rolled back, it is not allowed inside a transaction block or function.

## PARAMETERS
configuration_parameter

Name of a settable configuration parameter. Available parameters are documented in Chapter 19.

value

New value of the parameter. Values can be specified as string constants, identifiers, numbers, or comma-separated lists of these, as appropriate for the particular parameter. Values that are neither numbers nor valid identifiers must be quoted. DEFAULT can be written to specify removing the parameter and its value from postgresql.auto.conf. For some list-accepting parameters, quoted values will produce double-quoted output to preserve whitespace and commas; for others, double-quotes must be used inside single-quoted strings to get this effect.

## NOTES
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 set the parameters.

ALTER SYSTEM can be disabled by setting allow_alter_system to off, but this is not a security mechanism (as explained in detail in the documentation for this parameter).

## EXAMPLES
Set the wal_level:

```
ALTER SYSTEM SET wal_level = replica;
```

Undo that, restoring whatever setting was effective in postgresql.conf:

```
ALTER SYSTEM RESET wal_level;
```

## COMPATIBILITY
The ALTER SYSTEM statement is a PostgreSQL extension.

## SEE ALSO
SET(7), SHOW(7)