UPDATE

title: UPDATE(7)

summary: update rows of a table

text:
# UPDATE(7)

## NAME
UPDATE - update rows of a table

## SYNOPSIS

```
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]

    SET { column_name = { expression | DEFAULT } |

          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |

          ( column_name [, ...] ) = ( sub-SELECT )

        } [, ...]

    [ FROM from_item [, ...] ]

    [ WHERE condition | WHERE CURRENT OF cursor_name ]

    [ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
```

## DESCRIPTION
UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM clause. Which technique is more appropriate depends on the specific circumstances.

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.

You must have the UPDATE privilege on the table, or at least on the column(s) that are listed to be updated. You must also have the SELECT privilege on any column whose values are read in the expressions or condition.

## PARAMETERS
with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the UPDATE query. See Section 7.8 and SELECT(7) for details.

table_name

The name (optionally schema-qualified) of the table to update. If ONLY is specified before the table name, matching rows are updated in the named table only. If ONLY is not specified, matching rows are also updated in any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo.

column_name

The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid.

expression

An expression to assign to the column. The expression can use the old values of this and other columns in the table.

DEFAULT

Set the column to its default value (which will be NULL if no specific default expression has been assigned to it). An identity column will be set to a new value generated by the associated sequence. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression.

sub-SELECT

A SELECT sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to old values of the current row of the table being updated.

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

condition

An expression that returns a value of type boolean. Only rows for which this expression returns true will be updated.

cursor_name

The name of the cursor to use in a WHERE CURRENT OF condition. The row to be updated is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the UPDATE's target table. Note that WHERE CURRENT OF cannot be specified together with a Boolean condition. See DECLARE(7) for more information about using cursors with WHERE CURRENT OF.

output_expression

An expression to be computed and returned by the UPDATE command after each row is updated. The expression can use any column names of the table named by table_name or table(s) listed in FROM. Write * to return all columns.

output_name

A name to use for a returned column.

## OUTPUTS
On successful completion, an UPDATE command returns a command tag of the form

```
UPDATE count
```

The count is the number of rows updated, including matched rows whose values did not change. Note that the number may be less than the number of rows that matched the condition when updates were suppressed by a BEFORE UPDATE trigger. If count is 0, no rows were updated by the query (this is not considered an error).

If the UPDATE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) updated by the command.

## NOTES
When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

In the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition. In that case, if there is some other partition in the partition tree for which this row satisfies its partition constraint, then the row is moved to that partition. If there is no such partition, an error will occur. Behind the scenes, the row movement is actually a DELETE and INSERT operation.

There is a possibility that a concurrent UPDATE or DELETE on the row being moved will get a serialization failure error. Suppose session 1 is performing an UPDATE on a partition key, and meanwhile a concurrent session 2 for which this row is visible performs an UPDATE or DELETE operation on this row. In such case, session 2's UPDATE or DELETE will detect the row movement and raise a serialization failure error (which always returns with an SQLSTATE code '40001'). Applications may wish to retry the transaction if this occurs. In the usual case where the table is not partitioned, or where there is no row movement, session 2 would have identified the newly updated row and carried out the UPDATE/DELETE on this new row version.

Note that while rows can be moved from local partitions to a foreign-table partition (provided the foreign data wrapper supports tuple routing), they cannot be moved from a foreign-table partition to another partition.

## EXAMPLES
Change the word Drama to Dramatic in the column kind of the table films:

```
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
```

Adjust temperature entries and reset precipitation to its default value in one row of the table weather:

```
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT

  WHERE city = 'San Francisco' AND date = '2003-07-03';
```

Perform the same operation and return the updated entries:

```
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT

  WHERE city = 'San Francisco' AND date = '2003-07-03'

  RETURNING temp_lo, temp_hi, prcp;
```

Use the alternative column-list syntax to do the same update:

```
UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)

  WHERE city = 'San Francisco' AND date = '2003-07-03';
```

Increment the sales count of the salesperson who manages the account for Acme Corporation, using the FROM clause syntax:

```
UPDATE employees SET sales_count = sales_count + 1 FROM accounts

  WHERE accounts.name = 'Acme Corporation'

  AND employees.id = accounts.sales_person;
```

Perform the same operation, using a sub-select in the WHERE clause:

```
UPDATE employees SET sales_count = sales_count + 1 WHERE id =

  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
```

Update contact names in an accounts table to match the currently assigned salesmen:

```
UPDATE accounts SET (contact_first_name, contact_last_name) =

    (SELECT first_name, last_name FROM salesmen

     WHERE salesmen.id = accounts.sales_id);
```

A similar result could be accomplished with a join:

```
UPDATE accounts SET contact_first_name = first_name,

                    contact_last_name = last_name

  FROM salesmen WHERE salesmen.id = accounts.sales_id;
```

However, the second query may give unexpected results if salesmen.id is not a unique key, whereas the first query is guaranteed to raise an error if there are multiple id matches. Also, if there is no match for a particular accounts.sales_id entry, the first query will set the corresponding name fields to NULL, whereas the second query will not update that row at all.

Update statistics in a summary table to match the current data:

```
UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =

    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d

     WHERE d.group_id = s.group_id);
```

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. To do this without failing the entire transaction, use savepoints:

```
BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;
```

Change the kind column of the table films in the row on which the cursor c_films is currently positioned:

```
UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
```

## COMPATIBILITY
This command conforms to the SQL standard, except that the FROM and RETURNING clauses are PostgreSQL extensions, as is the ability to use WITH with UPDATE.

Some other database systems offer a FROM option in which the target table is supposed to be listed again within FROM. That is not how PostgreSQL interprets FROM. Be careful when porting applications that use this extension.

According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. PostgreSQL only allows the source value to be a row constructor or a sub-SELECT. An individual column's updated value can be specified as DEFAULT in the row-constructor case, but not inside a sub-SELECT.


VACUUM

title: VACUUM(7)

summary: garbage\-collect and optionally analyze a database

text:
# VACUUM(7)

## NAME
VACUUM - garbage-collect and optionally analyze a database

## SYNOPSIS

```
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
where option can be one of:

    FULL [ boolean ]

    FREEZE [ boolean ]

    VERBOSE [ boolean ]

    ANALYZE [ boolean ]

    DISABLE_PAGE_SKIPPING [ boolean ]

    SKIP_LOCKED [ boolean ]

    INDEX_CLEANUP [ boolean ]

    TRUNCATE [ boolean ]

    PARALLEL integer
and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]
```

## DESCRIPTION
VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

Without a table_and_columns list, VACUUM processes every table and materialized view in the current database that the current user has permission to vacuum. With a list, VACUUM processes only those table(s).

VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. See ANALYZE(7) for more details about its processing.

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. It also allows us to leverage multiple CPUs in order to process indexes. This feature is known as parallel vacuum. To disable this feature, one can use PARALLEL option and specify parallel workers as zero. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed.

When the option list is surrounded by parentheses, the options can be written in any order. Without parentheses, options must be specified in exactly the order shown above. The parenthesized syntax was added in PostgreSQL 9.0; the unparenthesized syntax is deprecated.

## PARAMETERS
FULL

Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

FREEZE

Selects aggressive “freezing” of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

VERBOSE

Prints a detailed vacuum activity report for each table.

ANALYZE

Updates statistics used by the planner to determine the most efficient way to execute a query.

DISABLE_PAGE_SKIPPING

Normally, VACUUM will skip pages based on the visibility map. Pages where all tuples are known to be frozen can always be skipped, and those where all tuples are known to be visible to all transactions may be skipped except when performing an aggressive vacuum. Furthermore, except when performing an aggressive vacuum, some pages may be skipped in order to avoid waiting for other sessions to finish using them. This option disables all page-skipping behavior, and is intended to be used only when the contents of the visibility map are suspect, which should happen only if there is a hardware or software issue causing database corruption.

SKIP_LOCKED

Specifies that VACUUM should not wait for any conflicting locks to be released when beginning work on a relation: if a relation cannot be locked immediately without waiting, the relation is skipped. Note that even with this option, VACUUM may still block when opening the relation's indexes. Additionally, VACUUM ANALYZE may still block when acquiring sample rows from partitions, table inheritance children, and some types of foreign tables. Also, while VACUUM ordinarily processes all partitions of specified partitioned tables, this option will cause VACUUM to skip all partitions if there is a conflicting lock on the partitioned table.

INDEX_CLEANUP

Specifies that VACUUM should attempt to remove index entries pointing to dead tuples. This is normally the desired behavior and is the default unless the vacuum_index_cleanup option has been set to false for the table to be vacuumed. Setting this option to false may be useful when it is necessary to make vacuum run as quickly as possible, for example to avoid imminent transaction ID wraparound (see Section 24.1.5). However, if index cleanup is not performed regularly, performance may suffer, because as the table is modified, indexes will accumulate dead tuples and the table itself will accumulate dead line pointers that cannot be removed until index cleanup is completed. This option has no effect for tables that do not have an index and is ignored if the FULL option is used.

TRUNCATE

Specifies that VACUUM should attempt to truncate off any empty pages at the end of the table and allow the disk space for the truncated pages to be returned to the operating system. This is normally the desired behavior and is the default unless the vacuum_truncate option has been set to false for the table to be vacuumed. Setting this option to false may be useful to avoid ACCESS EXCLUSIVE lock on the table that the truncation requires. This option is ignored if the FULL option is used.

PARALLEL

Perform index vacuum and index cleanup phases of VACUUM in parallel using integer background workers (for the details of each vacuum phase, please refer to Table 27.37). The number of workers used to perform the operation is equal to the number of indexes on the relation that support parallel vacuum which is limited by the number of workers specified with PARALLEL option if any which is further limited by max_parallel_maintenance_workers. An index can participate in parallel vacuum if and only if the size of the index is more than min_parallel_index_scan_size. Please note that it is not guaranteed that the number of parallel workers specified in integer will be used during execution. It is possible for a vacuum to run with fewer workers than specified, or even with no workers at all. Only one worker can be used per index. So parallel workers are launched only when there are at least 2 indexes in the table. Workers for vacuum are launched before the start of each phase and exit at the end of the phase. These behaviors might change in a future release. This option can't be used with the FULL option.

boolean

Specifies whether the selected option should be turned on or off. You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.

integer

Specifies a non-negative integer value passed to the selected option.

table_name

The name (optionally schema-qualified) of a specific table or materialized view to vacuum. If the specified table is a partitioned table, all of its leaf partitions are vacuumed.

column_name

The name of a specific column to analyze. Defaults to all columns. If a column list is specified, ANALYZE must also be specified.

## OUTPUTS
When VERBOSE is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.

## NOTES
To vacuum a table, one must ordinarily be the table's owner or a superuser. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide VACUUM can only be performed by a superuser.) VACUUM will skip over any tables that the calling user does not have permission to vacuum.

VACUUM cannot be executed inside a transaction block.

For tables with GIN indexes, VACUUM (in any form) also completes any pending index insertions, by moving pending index entries to the appropriate places in the main GIN index structure. See Section 66.4.1 for details.

We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows. After adding or deleting a large number of rows, it might be a good idea to issue a VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries.

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

The PARALLEL option is used only for vacuum purposes. If this option is specified with the ANALYZE option, it does not affect ANALYZE.

VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. For parallel vacuum, each worker sleeps in proportion to the work done by that worker. See Section 19.4.4 for details.

PostgreSQL includes an “autovacuum” facility which can automate routine vacuum maintenance. For more information about automatic and manual vacuuming, see Section 24.1.

## EXAMPLES
To clean a single table onek, analyze it for the optimizer and print a detailed vacuum activity report:

```
VACUUM (VERBOSE, ANALYZE) onek;
```

## COMPATIBILITY
There is no VACUUM statement in the SQL standard.

## SEE ALSO
vacuumdb(1), Section 19.4.4, Section 24.1.6


VALUES

title: VALUES(7)

summary: compute a set of rows

text:
# VALUES(7)

## NAME
VALUES - compute a set of rows

## SYNOPSIS

```
VALUES ( expression [, ...] ) [, ...]

    [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]

    [ LIMIT { count | ALL } ]

    [ OFFSET start [ ROW | ROWS ] ]

    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
```

## DESCRIPTION
VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a “constant table” within a larger command, but it can be used on its own.

When more than one row is specified, all the rows must have the same number of elements. The data types of the resulting table's columns are determined by combining the explicit or inferred types of the expressions appearing in that column, using the same rules as for UNION (see Section 10.5).

Within larger commands, VALUES is syntactically allowed anywhere that SELECT is. Because it is treated like a SELECT by the grammar, it is possible to use the ORDER BY, LIMIT (or equivalently FETCH FIRST), and OFFSET clauses with a VALUES command.

## PARAMETERS
expression

A constant or expression to compute and insert at the indicated place in the resulting table (set of rows). In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column's default value should be inserted. DEFAULT cannot be used when VALUES appears in other contexts.

sort_expression

An expression or integer constant indicating how to sort the result rows. This expression can refer to the columns of the VALUES result as column1, column2, etc. For more details see ORDER BY Clause in the SELECT(7) documentation.

operator

A sorting operator. For details see ORDER BY Clause in the SELECT(7) documentation.

count

The maximum number of rows to return. For details see LIMIT Clause in the SELECT(7) documentation.

start

The number of rows to skip before starting to return rows. For details see LIMIT Clause in the SELECT(7) documentation.

## NOTES
VALUES lists with very large numbers of rows should be avoided, as you might encounter out-of-memory failures or poor performance. VALUES appearing within INSERT is a special case (because the desired column types are known from the INSERT's target table, and need not be inferred by scanning the VALUES list), so it can handle larger lists than are practical in other contexts.

## EXAMPLES
A bare VALUES command:

```
VALUES (1, 'one'), (2, 'two'), (3, 'three');
```

This will return a table of two columns and three rows. It's effectively equivalent to:

```
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
```

More usually, VALUES is used within a larger SQL command. The most common use is in INSERT:

```
INSERT INTO films (code, title, did, date_prod, kind)

    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
```

In the context of INSERT, entries of a VALUES list can be DEFAULT to indicate that the column default should be used here instead of specifying a value:

```
INSERT INTO films VALUES

    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),

    ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);
```

VALUES can also be used where a sub-SELECT might be written, for example in a FROM clause:

```
SELECT f.*

  FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)

  WHERE f.studio = t.studio AND f.kind = t.kind;
UPDATE employees SET salary = salary * v.increase

  FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)

  WHERE employees.depno = v.depno AND employees.sales >= v.target;
```

Note that an AS clause is required when VALUES is used in a FROM clause, just as is true for SELECT. It is not required that the AS clause specify names for all the columns, but it's good practice to do so. (The default column names for VALUES are column1, column2, etc in PostgreSQL, but these names might be different in other database systems.)

When VALUES is used in INSERT, the values are all automatically coerced to the data type of the corresponding destination column. When it's used in other contexts, it might be necessary to specify the correct data type. If the entries are all quoted literal constants, coercing the first is sufficient to determine the assumed type for all:

```
SELECT * FROM machines
WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));
```

Tip

For simple IN tests, it's better to rely on the list-of-scalars form of IN than to write a VALUES query as shown above. The list of scalars method requires less writing and is often more efficient.

## COMPATIBILITY
VALUES conforms to the SQL standard. LIMIT and OFFSET are PostgreSQL extensions; see also under SELECT(7).

## SEE ALSO
INSERT(7), SELECT(7)


ABORT

title: ABORT(7)

summary: abort the current transaction

text:
# ABORT(7)

## NAME
ABORT - abort the current transaction

## SYNOPSIS

```
ABORT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
```

## DESCRIPTION
ABORT rolls back the current transaction and causes all the updates made by the transaction to be discarded. This command is identical in behavior to the standard SQL command ROLLBACK, and is present only for historical reasons.

## PARAMETERS
WORK

TRANSACTION

Optional key words. They have no effect.

AND CHAIN

If AND CHAIN is specified, a new transaction is immediately started with the same transaction characteristics (see SET TRANSACTION) as the just finished one. Otherwise, no new transaction is started.

## NOTES
Use COMMIT to successfully terminate a transaction.

Issuing ABORT outside of a transaction block emits a warning and otherwise has no effect.

## EXAMPLES
To abort all changes:

```
ABORT;
```

## COMPATIBILITY
This command is a PostgreSQL extension present for historical reasons. ROLLBACK is the equivalent standard SQL command.

## SEE ALSO
BEGIN(7), COMMIT(7), ROLLBACK(7)


title: ALTER_AGGREGATE(7)

text:
# ALTER_AGGREGATE(7)

## NAME
ALTER_AGGREGATE - change the definition of an aggregate function

## SYNOPSIS

```
ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name
ALTER AGGREGATE name ( aggregate_signature )

                OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema
where aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
```

## DESCRIPTION
ALTER AGGREGATE changes the definition of an aggregate function.

You must own the aggregate function to use ALTER AGGREGATE. To change the schema of an aggregate function, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the aggregate function's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the aggregate function. However, a superuser can alter ownership of any aggregate function anyway.)

## PARAMETERS
name

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

argmode

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

argname

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

argtype

An input data type on which the aggregate function operates. To reference a zero-argument aggregate function, write * in place of the list of argument specifications. To reference an ordered-set aggregate function, write ORDER BY between the direct and aggregated argument specifications.

new_name

The new name of the aggregate function.

new_owner

The new owner of the aggregate function.

new_schema

The new schema for the aggregate function.

## NOTES
The recommended syntax for referencing an ordered-set aggregate is to write ORDER BY between the direct and aggregated argument specifications, in the same style as in CREATE AGGREGATE. However, it will also work to omit ORDER BY and just run the direct and aggregated argument specifications into a single list. In this abbreviated form, if VARIADIC "any" was used in both the direct and aggregated argument lists, write VARIADIC "any" only once.

## EXAMPLES
To rename the aggregate function myavg for type integer to my_average:

```
ALTER AGGREGATE myavg(integer) RENAME TO my_average;
```

To change the owner of the aggregate function myavg for type integer to joe:

```
ALTER AGGREGATE myavg(integer) OWNER TO joe;
```

To move the ordered-set aggregate mypercentile with direct argument of type float8 and aggregated argument of type integer into schema myschema:

```
ALTER AGGREGATE mypercentile(float8 ORDER BY integer) SET SCHEMA myschema;
```

This will work too:

```
ALTER AGGREGATE mypercentile(float8, integer) SET SCHEMA myschema;
```

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

## SEE ALSO
CREATE AGGREGATE (CREATE_AGGREGATE(7)), DROP AGGREGATE (DROP_AGGREGATE(7))


title: ALTER_COLLATION(7)

summary: change the definition of a collation

text:
# ALTER_COLLATION(7)

## NAME
ALTER_COLLATION - change the definition of a collation

## SYNOPSIS

```
ALTER COLLATION name REFRESH VERSION
ALTER COLLATION name RENAME TO new_name
ALTER COLLATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema
```

## DESCRIPTION
ALTER COLLATION changes the definition of a collation.

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

## PARAMETERS
name

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

new_name

The new name of the collation.

new_owner

The new owner of the collation.

new_schema

The new schema for the collation.

REFRESH VERSION

Update the collation's version. See Notes below.

## NOTES
When using collations provided by the ICU library, the ICU-specific version of the collator is recorded in the system catalog when the collation object is created. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch, for example:

```
WARNING:  collation "xx-x-icu" has version mismatch
DETAIL:  The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.
```

A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. Generally, this should be avoided, but it can happen in legitimate circumstances, such as when using pg_upgrade to upgrade to server binaries linked with a newer version of ICU. When this happens, all objects depending on the collation should be rebuilt, for example, using REINDEX. When that is done, the collation version can be refreshed using the command ALTER COLLATION ... REFRESH VERSION. This will update the system catalog to record the current collator version and will make the warning go away. Note that this does not actually check whether all affected objects have been rebuilt correctly.

When using collations provided by libc, version information is recorded on systems using the GNU C library (most Linux systems), FreeBSD and Windows.

Note

When using the GNU C library for collations, the C library's version is used as a proxy for the collation version. Many Linux distributions change collation definitions only when upgrading the C library, but this approach is imperfect as maintainers are free to back-port newer collation definitions to older C library releases.

When using Windows for collations, version information is only available for collations defined with BCP 47 language tags such as en-US.

Currently, there is no version tracking for the database default collation.

The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on them:

```
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",

       pg_describe_object(classid, objid, objsubid) AS "Object"

  FROM pg_depend d JOIN pg_collation c

       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid

  WHERE c.collversion <> pg_collation_actual_version(c.oid)

  ORDER BY 1, 2;
```

## EXAMPLES
To rename the collation de_DE to german:

```
ALTER COLLATION "de_DE" RENAME TO german;
```

To change the owner of the collation en_US to joe:

```
ALTER COLLATION "en_US" OWNER TO joe;
```

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

## SEE ALSO
CREATE COLLATION (CREATE_COLLATION(7)), DROP COLLATION (DROP_COLLATION(7))


title: ALTER_CONVERSION(7)

summary: change the definition of a conversion

text:
# ALTER_CONVERSION(7)

## NAME
ALTER_CONVERSION - change the definition of a conversion

## SYNOPSIS

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

## DESCRIPTION
ALTER CONVERSION changes the definition of a conversion.

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

## PARAMETERS
name

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

new_name

The new name of the conversion.

new_owner

The new owner of the conversion.

new_schema

The new schema for the conversion.

## EXAMPLES
To rename the conversion iso_8859_1_to_utf8 to latin1_to_unicode:

```
ALTER CONVERSION iso_8859_1_to_utf8 RENAME TO latin1_to_unicode;
```

To change the owner of the conversion iso_8859_1_to_utf8 to joe:

```
ALTER CONVERSION iso_8859_1_to_utf8 OWNER TO joe;
```

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

## SEE ALSO
CREATE CONVERSION (CREATE_CONVERSION(7)), DROP CONVERSION (DROP_CONVERSION(7))


title: ALTER_DATABASE(7)

summary: change a database

text:
# ALTER_DATABASE(7)

## NAME
ALTER_DATABASE - change a database

## SYNOPSIS

```
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:

    ALLOW_CONNECTIONS allowconn

    CONNECTION LIMIT connlimit

    IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
```

## DESCRIPTION
ALTER DATABASE changes the attributes of a database.

The first form changes certain per-database settings. (See below for details.) Only the database owner or a superuser can change these settings.

The second form changes the name of the database. Only the database owner or a superuser can rename a database; non-superuser owners must also have the CREATEDB privilege. The current database cannot be renamed. (Connect to a different database if you need to do that.)

The third form changes the owner of the database. To alter the owner, you must own the database and also be a direct or indirect member of the new owning role, and you must have the CREATEDB privilege. (Note that superusers have all these privileges automatically.)

The fourth form changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. The new default tablespace must be empty for this database, and no one can be connected to the database. Tables and indexes in non-default tablespaces are unaffected.

The remaining forms change the session default for a run-time configuration variable for a PostgreSQL database. Whenever a new session is subsequently started in that database, the specified value becomes the session default value. The database-specific default overrides whatever setting is present in postgresql.conf or has been received from the postgres command line. Only the database owner or a superuser can change the session defaults for a database. Certain variables cannot be set this way, or can only be set by a superuser.

## PARAMETERS
name

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

allowconn

If false then no one can connect to this database.

connlimit

How many concurrent connections can be made to this database. -1 means no limit.

istemplate

If true, then this database can be cloned by any user with CREATEDB privileges; if false, then only superusers or the owner of the database can clone it.

new_name

The new name of the database.

new_owner

The new owner of the database.

new_tablespace

The new default tablespace of the database. This form of the command cannot be executed inside a transaction block.

configuration_parameter

value

Set this database's session default for the specified configuration parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the database-specific setting is removed, so the system-wide default setting will be inherited in new sessions. Use RESET ALL to clear all database-specific settings. SET FROM CURRENT saves the session's current value of the parameter as the database-specific value. See SET(7) and Chapter 20 for more information about allowed parameter names and values.

## NOTES
It is also possible to tie a session default to a specific role rather than to a database; see ALTER ROLE (ALTER_ROLE(7)). Role-specific settings override database-specific ones if there is a conflict.

## EXAMPLES
To disable index scans by default in the database test:

```
ALTER DATABASE test SET enable_indexscan TO off;
```

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

## SEE ALSO
CREATE DATABASE (CREATE_DATABASE(7)), DROP DATABASE (DROP_DATABASE(7)), SET(7), CREATE TABLESPACE (CREATE_TABLESPACE(7))


title: ALTER_DEFAULT_PRIVILEGES(7)

summary: define default access privileges

text:
# ALTER_DEFAULT_PRIVILEGES(7)

## NAME
ALTER_DEFAULT_PRIVILEGES - define default access privileges

## SYNOPSIS

```
ALTER DEFAULT PRIVILEGES

    [ FOR { ROLE | USER } target_role [, ...] ]

    [ IN SCHEMA schema_name [, ...] ]

    abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

    [, ...] | ALL [ PRIVILEGES ] }

    ON TABLES

    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }

    [, ...] | ALL [ PRIVILEGES ] }

    ON SEQUENCES

    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }

    ON { FUNCTIONS | ROUTINES }

    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }

    ON TYPES

    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | CREATE }

    [, ...] | ALL [ PRIVILEGES ] }

    ON SCHEMAS

    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]

    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

    [, ...] | ALL [ PRIVILEGES ] }

    ON TABLES

    FROM { [ GROUP ] role_name | PUBLIC } [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { USAGE | SELECT | UPDATE }

    [, ...] | ALL [ PRIVILEGES ] }

    ON SEQUENCES

    FROM { [ GROUP ] role_name | PUBLIC } [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { EXECUTE | ALL [ PRIVILEGES ] }

    ON { FUNCTIONS | ROUTINES }

    FROM { [ GROUP ] role_name | PUBLIC } [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON TYPES

    FROM { [ GROUP ] role_name | PUBLIC } [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { USAGE | CREATE }

    [, ...] | ALL [ PRIVILEGES ] }

    ON SCHEMAS

    FROM { [ GROUP ] role_name | PUBLIC } [, ...]

    [ CASCADE | RESTRICT ]
```

## DESCRIPTION
ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered. For this command, functions include aggregates and procedures. The words FUNCTIONS and ROUTINES are equivalent in this command. (ROUTINES is preferred going forward as the standard term for functions and procedures taken together. In earlier PostgreSQL releases, only the word FUNCTIONS was allowed. It is not possible to set default privileges for functions and procedures separately.)

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. The privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas.

As explained in Section 5.7, the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.

Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type. This means you cannot revoke privileges per-schema if they are granted globally (either by default, or according to a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema). Per-schema REVOKE is only useful to reverse the effects of a previous per-schema GRANT.

### Parameters
target_role

The name of an existing role of which the current role is a member. Default access privileges are not inherited, so member roles must use SET ROLE to access these privileges, or ALTER DEFAULT PRIVILEGES must be run for each member role. If FOR ROLE is omitted, the current role is assumed.

schema_name

The name of an existing schema. If specified, the default privileges are altered for objects later created in that schema. If IN SCHEMA is omitted, the global default privileges are altered. IN SCHEMA is not allowed when setting privileges for schemas, since schemas can't be nested.

role_name

The name of an existing role to grant or revoke privileges for. This parameter, and all the other parameters in abbreviated_grant_or_revoke, act as described under GRANT(7) or REVOKE(7), except that one is setting permissions for a whole class of objects rather than specific named objects.

## NOTES
Use psql(1)'s \ddp command to obtain information about existing assignments of default privileges. The meaning of the privilege display is the same as explained for \dp in Section 5.7.

If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.

## EXAMPLES
Grant SELECT privilege to everyone for all tables (and views) you subsequently create in schema myschema, and allow role webuser to INSERT into them too:

```
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
```

Undo the above, so that subsequently-created tables won't have any more permissions than normal:

```
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
```

Remove the public EXECUTE permission that is normally granted on functions, for all functions subsequently created by role admin:

```
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
```

Note however that you cannot accomplish that effect with a command limited to a single schema. This command has no effect, unless it is undoing a matching GRANT:

```
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
```

That's because per-schema default privileges can only add privileges to the global setting, not remove privileges granted by it.

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

## SEE ALSO
GRANT(7), REVOKE(7)


title: ALTER_DOMAIN(7)

summary: change the definition of a domain

text:
# ALTER_DOMAIN(7)

## NAME
ALTER_DOMAIN - change the definition of a domain

## SYNOPSIS

```
ALTER DOMAIN name

    { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name

    { SET | DROP } NOT NULL
ALTER DOMAIN name

    ADD domain_constraint [ NOT VALID ]
ALTER DOMAIN name

    DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name

     RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER DOMAIN name

    VALIDATE CONSTRAINT constraint_name
ALTER DOMAIN name

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DOMAIN name

    RENAME TO new_name
ALTER DOMAIN name

    SET SCHEMA new_schema
where domain_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | CHECK (expression) }
```

## DESCRIPTION
ALTER DOMAIN changes the definition of an existing domain. There are several sub-forms:

SET/DROP DEFAULT

These forms set or remove the default value for a domain. Note that defaults only apply to subsequent INSERT commands; they do not affect rows already in a table using the domain.

SET/DROP NOT NULL

These forms change whether a domain is marked to allow NULL values or to reject NULL values. You can only SET NOT NULL when the columns using the domain contain no null values.

ADD domain_constraint [ NOT VALID ]

This form adds a new constraint to a domain. When a new constraint is added to a domain, all columns using that domain will be checked against the newly added constraint. These checks can be suppressed by adding the new constraint using the NOT VALID option; the constraint can later be made valid using ALTER DOMAIN ... VALIDATE CONSTRAINT. Newly inserted or updated rows are always checked against all constraints, even those marked NOT VALID. NOT VALID is only accepted for CHECK constraints.

DROP CONSTRAINT [ IF EXISTS ]

This form drops constraints on a domain. If IF EXISTS is specified and the constraint does not exist, no error is thrown. In this case a notice is issued instead.

RENAME CONSTRAINT

This form changes the name of a constraint on a domain.

VALIDATE CONSTRAINT

This form validates a constraint previously added as NOT VALID, that is, it verifies that all values in table columns of the domain type satisfy the specified constraint.

OWNER

This form changes the owner of the domain to the specified user.

RENAME

This form changes the name of the domain.

SET SCHEMA

This form changes the schema of the domain. Any constraints associated with the domain are moved into the new schema as well.

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

## PARAMETERS
name

The name (possibly schema-qualified) of an existing domain to alter.

domain_constraint

New domain constraint for the domain.

constraint_name

Name of an existing constraint to drop or rename.

NOT VALID

Do not verify existing stored data for constraint validity.

CASCADE

Automatically drop objects that depend on the constraint, and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the constraint if there are any dependent objects. This is the default behavior.

new_name

The new name for the domain.

new_constraint_name

The new name for the constraint.

new_owner

The user name of the new owner of the domain.

new_schema

The new schema for the domain.

## NOTES
Although ALTER DOMAIN ADD CONSTRAINT attempts to verify that existing stored data satisfies the new constraint, this check is not bulletproof, because the command cannot “see” table rows that are newly inserted or updated and not yet committed. If there is a hazard that concurrent operations might insert bad data, the way to proceed is to add the constraint using the NOT VALID option, commit that command, wait until all transactions started before that commit have finished, and then issue ALTER DOMAIN VALIDATE CONSTRAINT to search for data violating the constraint. This method is reliable because once the constraint is committed, all new transactions are guaranteed to enforce it against new values of the domain type.

Currently, ALTER DOMAIN ADD CONSTRAINT, ALTER DOMAIN VALIDATE CONSTRAINT, and ALTER DOMAIN SET NOT NULL will fail if the named domain or any derived domain is used within a container-type column (a composite, array, or range column) in any table in the database. They should eventually be improved to be able to verify the new constraint for such nested values.

## EXAMPLES
To add a NOT NULL constraint to a domain:

```
ALTER DOMAIN zipcode SET NOT NULL;
```

To remove a NOT NULL constraint from a domain:

```
ALTER DOMAIN zipcode DROP NOT NULL;
```

To add a check constraint to a domain:

```
ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);
```

To remove a check constraint from a domain:

```
ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;
```

To rename a check constraint on a domain:

```
ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;
```

To move the domain into a different schema:

```
ALTER DOMAIN zipcode SET SCHEMA customers;
```

## COMPATIBILITY
ALTER DOMAIN conforms to the SQL standard, except for the OWNER, RENAME, SET SCHEMA, and VALIDATE CONSTRAINT variants, which are PostgreSQL extensions. The NOT VALID clause of the ADD CONSTRAINT variant is also a PostgreSQL extension.

## SEE ALSO
CREATE DOMAIN (CREATE_DOMAIN(7)), DROP DOMAIN (DROP_DOMAIN(7))


title: ALTER_EVENT_TRIGGER(7)

text:
# ALTER_EVENT_TRIGGER(7)

## NAME
ALTER_EVENT_TRIGGER - change the definition of an event trigger

## SYNOPSIS

```
ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name
```

## DESCRIPTION
ALTER EVENT TRIGGER changes properties of an existing event trigger.

You must be superuser to alter an event trigger.

## PARAMETERS
name

The name of an existing trigger to alter.

new_owner

The user name of the new owner of the event trigger.

new_name

The new name of the event trigger.

DISABLE/ENABLE [ REPLICA | ALWAYS ]

These forms configure the firing of event triggers. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. See also session_replication_role.

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

## SEE ALSO
CREATE EVENT TRIGGER (CREATE_EVENT_TRIGGER(7)), DROP EVENT TRIGGER (DROP_EVENT_TRIGGER(7))


title: ALTER_EXTENSION(7)

summary: change the definition of an extension

text:
# ALTER_EXTENSION(7)

## NAME
ALTER_EXTENSION - change the definition of an extension

## SYNOPSIS

```
ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object
where member_object is:

  ACCESS METHOD object_name |

  AGGREGATE aggregate_name ( aggregate_signature ) |

  CAST (source_type AS target_type) |

  COLLATION object_name |

  CONVERSION object_name |

  DOMAIN object_name |

  EVENT TRIGGER object_name |

  FOREIGN DATA WRAPPER object_name |

  FOREIGN TABLE object_name |

  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  MATERIALIZED VIEW object_name |

  OPERATOR operator_name (left_type, right_type) |

  OPERATOR CLASS object_name USING index_method |

  OPERATOR FAMILY object_name USING index_method |

  [ PROCEDURAL ] LANGUAGE object_name |

  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  SCHEMA object_name |

  SEQUENCE object_name |

  SERVER object_name |

  TABLE object_name |

  TEXT SEARCH CONFIGURATION object_name |

  TEXT SEARCH DICTIONARY object_name |

  TEXT SEARCH PARSER object_name |

  TEXT SEARCH TEMPLATE object_name |

  TRANSFORM FOR type_name LANGUAGE lang_name |

  TYPE object_name |

  VIEW object_name
and aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
```

## DESCRIPTION
ALTER EXTENSION changes the definition of an installed extension. There are several subforms:

UPDATE

This form updates the extension to a newer version. The extension must supply a suitable update script (or series of scripts) that can modify the currently-installed version into the requested version.

SET SCHEMA

This form moves the extension's objects into another schema. The extension has to be relocatable for this command to succeed.

ADD member_object

This form adds an existing object to the extension. This is mainly useful in extension update scripts. The object will subsequently be treated as a member of the extension; notably, it can only be dropped by dropping the extension.

DROP member_object

This form removes a member object from the extension. This is mainly useful in extension update scripts. The object is not dropped, only disassociated from the extension.

See Section 38.17 for more information about these operations. You must own the extension to use ALTER EXTENSION. The ADD/DROP forms require ownership of the added/dropped object as well.

## PARAMETERS
name

The name of an installed extension.

new_version

The desired new version of the extension. This can be written as either an identifier or a string literal. If not specified, ALTER EXTENSION UPDATE attempts to update to whatever is shown as the default version in the extension's control file.

new_schema

The new schema for the extension.

object_name

aggregate_name

function_name

operator_name

procedure_name

routine_name

The name of an object to be added to or removed from the extension. Names of tables, aggregates, domains, foreign tables, functions, operators, operator classes, operator families, procedures, routines, sequences, text search objects, types, and views can be schema-qualified.

source_type

The name of the source data type of the cast.

target_type

The name of the target data type of the cast.

argmode

The mode of a function, procedure, or aggregate argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Note that ALTER EXTENSION does not actually pay any attention to OUT arguments, since only the input arguments are needed to determine the function's identity. So it is sufficient to list the IN, INOUT, and VARIADIC arguments.

argname

The name of a function, procedure, or aggregate argument. Note that ALTER EXTENSION does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity.

argtype

The data type of a function, procedure, or aggregate argument.

left_type

right_type

The data type(s) of the operator's arguments (optionally schema-qualified). Write NONE for the missing argument of a prefix operator.

PROCEDURAL

This is a noise word.

type_name

The name of the data type of the transform.

lang_name

The name of the language of the transform.

## EXAMPLES
To update the hstore extension to version 2.0:

```
ALTER EXTENSION hstore UPDATE TO '2.0';
```

To change the schema of the hstore extension to utils:

```
ALTER EXTENSION hstore SET SCHEMA utils;
```

To add an existing function to the hstore extension:

```
ALTER EXTENSION hstore ADD FUNCTION populate_record(anyelement, hstore);
```

## COMPATIBILITY
ALTER EXTENSION is a PostgreSQL extension.

## SEE ALSO
CREATE EXTENSION (CREATE_EXTENSION(7)), DROP EXTENSION (DROP_EXTENSION(7))


title: ALTER_FOREIGN_DATA_WRAPPER(7)

summary: change the definition of a foreign\-data wrapper

text:
# ALTER_FOREIGN_DATA_WRAPPER(7)

## NAME
ALTER_FOREIGN_DATA_WRAPPER - change the definition of a foreign-data wrapper

## SYNOPSIS

```
ALTER FOREIGN DATA WRAPPER name

    [ HANDLER handler_function | NO HANDLER ]

    [ VALIDATOR validator_function | NO VALIDATOR ]

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

## DESCRIPTION
ALTER FOREIGN DATA WRAPPER changes the definition of a foreign-data wrapper. The first form of the command changes the support functions or the generic options of the foreign-data wrapper (at least one clause is required). The second form changes the owner of the foreign-data wrapper.

Only superusers can alter foreign-data wrappers. Additionally, only superusers can own foreign-data wrappers.

## PARAMETERS
name

The name of an existing foreign-data wrapper.

HANDLER handler_function

Specifies a new handler function for the foreign-data wrapper.

NO HANDLER

This is used to specify that the foreign-data wrapper should no longer have a handler function. Note that foreign tables that use a foreign-data wrapper with no handler cannot be accessed.

VALIDATOR validator_function

Specifies a new validator function for the foreign-data wrapper. Note that it is possible that pre-existing options of the foreign-data wrapper, or of dependent servers, user mappings, or foreign tables, are invalid according to the new validator. PostgreSQL does not check for this. It is up to the user to make sure that these options are correct before using the modified foreign-data wrapper. However, any options specified in this ALTER FOREIGN DATA WRAPPER command will be checked using the new validator.

NO VALIDATOR

This is used to specify that the foreign-data wrapper should no longer have a validator function.

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

Change options for the foreign-data wrapper. 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 foreign data wrapper's validator function, if any.

new_owner

The user name of the new owner of the foreign-data wrapper.

new_name

The new name for the foreign-data wrapper.

## EXAMPLES
Change a foreign-data wrapper dbi, add option foo, drop bar:

```
ALTER FOREIGN DATA WRAPPER dbi OPTIONS (ADD foo '1', DROP bar);
```

Change the foreign-data wrapper dbi validator to bob.myvalidator:

```
ALTER FOREIGN DATA WRAPPER dbi VALIDATOR bob.myvalidator;
```

## COMPATIBILITY
ALTER FOREIGN DATA WRAPPER conforms to ISO/IEC 9075-9 (SQL/MED), except that the HANDLER, VALIDATOR, OWNER TO, and RENAME clauses are extensions.

## SEE ALSO
CREATE FOREIGN DATA WRAPPER (CREATE_FOREIGN_DATA_WRAPPER(7)), DROP FOREIGN DATA WRAPPER (DROP_FOREIGN_DATA_WRAPPER(7))


title: ALTER_FOREIGN_TABLE(7)

text:
# ALTER_FOREIGN_TABLE(7)

## NAME
ALTER_FOREIGN_TABLE - change the definition of a foreign table

## SYNOPSIS

```
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]

    action [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]

    RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] name

    RENAME TO new_name
ALTER FOREIGN TABLE [ IF EXISTS ] name

    SET SCHEMA new_schema
where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]

    ALTER [ COLUMN ] column_name SET DEFAULT expression

    ALTER [ COLUMN ] column_name DROP DEFAULT

    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

    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 }

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

    ADD table_constraint [ NOT VALID ]

    VALIDATE CONSTRAINT constraint_name

    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]

    DISABLE TRIGGER [ trigger_name | ALL | USER ]

    ENABLE TRIGGER [ trigger_name | ALL | USER ]

    ENABLE REPLICA TRIGGER trigger_name

    ENABLE ALWAYS TRIGGER trigger_name

    SET WITHOUT OIDS

    INHERIT parent_table

    NO INHERIT parent_table

    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

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

## DESCRIPTION
ALTER FOREIGN TABLE changes the definition of an existing foreign table. There are several subforms:

ADD [ COLUMN ] [ IF NOT EXISTS ]

This form adds a new column to the foreign table, using the same syntax as CREATE FOREIGN TABLE. If IF NOT EXISTS is specified and a column already exists with this name, no error is thrown. Unlike the case when adding a column to a regular table, nothing happens to the underlying storage: this action simply declares that some new column is now accessible through the foreign table.

DROP [ COLUMN ] [ IF EXISTS ]

This form drops a column from a foreign table. You will need to say CASCADE if anything outside the table depends on the column; for example, views. If IF EXISTS is specified and the column does not exist, no error is thrown. In this case a notice is issued instead.

SET DATA TYPE

This form changes the type of a column of a foreign table. Again, this has no effect on any underlying storage: this action simply changes the type that PostgreSQL believes the column to have.

SET/DROP DEFAULT

These forms set or remove the default value for a column. Default values only apply in subsequent INSERT or UPDATE commands; they do not cause rows already in the table to change.

SET/DROP NOT NULL

Mark a column as allowing, or not allowing, null values.

SET STATISTICS

This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. See the similar form of ALTER TABLE for more details.

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

RESET ( attribute_option [, ... ] )

This form sets or resets per-attribute options. See the similar form of ALTER TABLE for more details.

SET STORAGE

This form sets the storage mode for a column. See the similar form of ALTER TABLE for more details. Note that the storage mode has no effect unless the table's foreign-data wrapper chooses to pay attention to it.

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a foreign table, using the same syntax as CREATE FOREIGN TABLE. Currently only CHECK constraints are supported. Unlike the case when adding a constraint to a regular table, nothing is done to verify the constraint is correct; rather, this action simply declares that some new condition should be assumed to hold for all rows in the foreign table. (See the discussion in CREATE FOREIGN TABLE.) If the constraint is marked NOT VALID, then it isn't assumed to hold, but is only recorded for possible future use.

VALIDATE CONSTRAINT

This form marks as valid a constraint that was previously marked as NOT VALID. No action is taken to verify the constraint, but future queries will assume that it holds.

DROP CONSTRAINT [ IF EXISTS ]

This form drops the specified constraint on a foreign table. If IF EXISTS is specified and the constraint does not exist, no error is thrown. In this case a notice is issued instead.

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

These forms configure the firing of trigger(s) belonging to the foreign table. See the similar form of ALTER TABLE for more details.

SET WITHOUT OIDS

Backward compatibility syntax for removing the oid system column. As oid system columns cannot be added anymore, this never has an effect.

INHERIT parent_table

This form adds the target foreign table as a new child of the specified parent table. See the similar form of ALTER TABLE for more details.

NO INHERIT parent_table

This form removes the target foreign table from the list of children of the specified parent table.

OWNER

This form changes the owner of the foreign table to the specified user.

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

Change options for the foreign table or one of its columns. ADD, SET, and DROP specify the action to be performed. ADD is assumed if no operation is explicitly specified. Duplicate option names are not allowed (although it's OK for a table option and a column option to have the same name). Option names and values are also validated using the foreign data wrapper library.

RENAME

The RENAME forms change the name of a foreign table or the name of an individual column in a foreign table.

SET SCHEMA

This form moves the foreign table into another schema.

All the actions except RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns and/or alter the type of several columns in a single command.

If the command is written as ALTER FOREIGN TABLE IF EXISTS ... and the foreign table does not exist, no error is thrown. A notice is issued in this case.

You must own the table to use ALTER FOREIGN TABLE. To change the schema of a foreign table, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type, you must also have USAGE privilege on the data type.

## PARAMETERS
name

The name (possibly schema-qualified) of an existing foreign table to alter. If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

column_name

Name of a new or existing column.

new_column_name

New name for an existing column.

new_name

New name for the table.

data_type

Data type of the new column, or new data type for an existing column.

table_constraint

New table constraint for the foreign table.

constraint_name

Name of an existing constraint to drop.

CASCADE

Automatically drop objects that depend on the dropped column or constraint (for example, views referencing the column), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the column or constraint if there are any dependent objects. This is the default behavior.

trigger_name

Name of a single trigger to disable or enable.

ALL

Disable or enable all triggers belonging to the foreign table. (This requires superuser privilege if any of the triggers are internally generated triggers. The core system does not add such triggers to foreign tables, but add-on code could do so.)

USER

Disable or enable all triggers belonging to the foreign table except for internally generated triggers.

parent_table

A parent table to associate or de-associate with this foreign table.

new_owner

The user name of the new owner of the table.

new_schema

The name of the schema to which the table will be moved.

## NOTES
The key word COLUMN is noise and can be omitted.

Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or DROP COLUMN, a NOT NULL or CHECK constraint is added, or a column type is changed with SET DATA TYPE. It is the user's responsibility to ensure that the table definition matches the remote side.

Refer to CREATE FOREIGN TABLE for a further description of valid parameters.

## EXAMPLES
To mark a column as not-null:

```
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
```

To change options of a foreign table:

```
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3);
```

## COMPATIBILITY
The forms ADD, DROP, and SET DATA TYPE conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER FOREIGN TABLE command is an extension.

ALTER FOREIGN TABLE DROP COLUMN can be used to drop the only column of a foreign table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column foreign tables.

## SEE ALSO
CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), DROP FOREIGN TABLE (DROP_FOREIGN_TABLE(7))


title: ALTER_FUNCTION(7)

summary: change the definition of a function

text:
# ALTER_FUNCTION(7)

## NAME
ALTER_FUNCTION - change the definition of a function

## SYNOPSIS

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

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

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

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

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

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

    CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

    IMMUTABLE | STABLE | VOLATILE

    [ NOT ] LEAKPROOF

    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

    PARALLEL { UNSAFE | RESTRICTED | SAFE }

    COST execution_cost

    ROWS result_rows

    SUPPORT support_function

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

    SET configuration_parameter FROM CURRENT

    RESET configuration_parameter

    RESET ALL
```

## DESCRIPTION
ALTER FUNCTION changes the definition of a function.

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

## PARAMETERS
name

The name (optionally schema-qualified) of an existing function. 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. Note that ALTER FUNCTION does not actually pay any attention to OUT arguments, since only the input arguments are needed to determine the function's identity. So it is sufficient to list the IN, INOUT, and VARIADIC arguments.

argname

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

argtype

The data type(s) of the function's arguments (optionally schema-qualified), if any.

new_name

The new name of the function.

new_owner

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

new_schema

The new schema for the function.

DEPENDS ON EXTENSION extension_name

NO DEPENDS ON EXTENSION extension_name

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

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT changes the function so that it will be invoked when some or all of its arguments are null. RETURNS NULL ON NULL INPUT or STRICT changes the function so that it is not invoked if any of its arguments are null; instead, a null result is assumed automatically. See CREATE FUNCTION (CREATE_FUNCTION(7)) for more information.

IMMUTABLE

STABLE

VOLATILE

Change the volatility of the function to the specified setting. See CREATE FUNCTION (CREATE_FUNCTION(7)) for details.

[ EXTERNAL ] SECURITY INVOKER

[ EXTERNAL ] SECURITY DEFINER

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

PARALLEL

Change whether the function is deemed safe for parallelism. See CREATE FUNCTION (CREATE_FUNCTION(7)) for details.

LEAKPROOF

Change whether the function is considered leakproof or not. See CREATE FUNCTION (CREATE_FUNCTION(7)) for more information about this capability.

COST execution_cost

Change the estimated execution cost of the function. See CREATE FUNCTION (CREATE_FUNCTION(7)) for more information.

ROWS result_rows

Change the estimated number of rows returned by a set-returning function. See CREATE FUNCTION (CREATE_FUNCTION(7)) for more information.

SUPPORT support_function

Set or change the planner support function to use for this function. See Section 38.11 for details. You must be superuser to use this option. This option cannot be used to remove the support function altogether, since it must name a new support function. Use CREATE OR REPLACE FUNCTION if you need to do that.

configuration_parameter

value

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

RESTRICT

Ignored for conformance with the SQL standard.

## EXAMPLES
To rename the function sqrt for type integer to square_root:

```
ALTER FUNCTION sqrt(integer) RENAME TO square_root;
```

To change the owner of the function sqrt for type integer to joe:

```
ALTER FUNCTION sqrt(integer) OWNER TO joe;
```

To change the schema of the function sqrt for type integer to maths:

```
ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
```

To mark the function sqrt for type integer as being dependent on the extension mathlib:

```
ALTER FUNCTION sqrt(integer) DEPENDS ON EXTENSION mathlib;
```

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

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

To disable automatic setting of search_path for a function:

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

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

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

## SEE ALSO
CREATE FUNCTION (CREATE_FUNCTION(7)), DROP FUNCTION (DROP_FUNCTION(7)), ALTER PROCEDURE (ALTER_PROCEDURE(7)), ALTER ROUTINE (ALTER_ROUTINE(7))


title: ALTER_GROUP(7)

summary: change role name or membership

text:
# ALTER_GROUP(7)

## NAME
ALTER_GROUP - change role name or membership

## SYNOPSIS

```
ALTER GROUP role_specification ADD USER user_name [, ... ]
ALTER GROUP role_specification DROP USER user_name [, ... ]
where role_specification can be:

    role_name

  | CURRENT_ROLE

  | CURRENT_USER

  | SESSION_USER
ALTER GROUP group_name RENAME TO new_name
```

## DESCRIPTION
ALTER GROUP changes the attributes of a user group. This is an obsolete command, though still accepted for backwards compatibility, because groups (and users too) have been superseded by the more general concept of roles.

The first two variants add users to a group or remove them from a group. (Any role can play the part of either a “user” or a “group” for this purpose.) These variants are effectively equivalent to granting or revoking membership in the role named as the “group”; so the preferred way to do this is to use GRANT or REVOKE.

The third variant changes the name of the group. This is exactly equivalent to renaming the role with ALTER ROLE.

## PARAMETERS
group_name

The name of the group (role) to modify.

user_name

Users (roles) that are to be added to or removed from the group. The users must already exist; ALTER GROUP does not create or drop users.

new_name

The new name of the group.

## EXAMPLES
Add users to a group:

```
ALTER GROUP staff ADD USER karl, john;
```

Remove a user from a group:

```
ALTER GROUP workers DROP USER beth;
```

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

## SEE ALSO
GRANT(7), REVOKE(7), ALTER ROLE (ALTER_ROLE(7))


title: ALTER_INDEX(7)

summary: change the definition of an index

text:
# ALTER_INDEX(7)

## NAME
ALTER_INDEX - change the definition of an index

## SYNOPSIS

```
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name ATTACH PARTITION index_name
ALTER INDEX name [ NO ] DEPENDS ON EXTENSION extension_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number

    SET STATISTICS integer
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]

    SET TABLESPACE new_tablespace [ NOWAIT ]
```

## DESCRIPTION
ALTER INDEX changes the definition of an existing index. There are several subforms described below. Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple subcommands are listed, the lock held will be the strictest one required from any subcommand.

RENAME

The RENAME form changes the name of the index. If the index is associated with a table constraint (either UNIQUE, PRIMARY KEY, or EXCLUDE), the constraint is renamed as well. There is no effect on the stored data. Renaming an index acquires a SHARE UPDATE EXCLUSIVE lock.

SET TABLESPACE

This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all indexes to be moved and then move each one. This form also supports OWNED BY, which will only move indexes owned by the roles specified. If the NOWAIT option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs will not be moved by this command, use ALTER DATABASE or explicit ALTER INDEX invocations instead if desired. See also CREATE TABLESPACE.

ATTACH PARTITION index_name

Causes the named index (possibly schema-qualified) to become attached to the altered index. The named index must be on a partition of the table containing the index being altered, and have an equivalent definition. An attached index cannot be dropped by itself, and will automatically be dropped if its parent index is dropped. If the named index is already attached to the altered index, the command will attempt to validate the parent index if the parent is currently invalid.

DEPENDS ON EXTENSION extension_name

NO DEPENDS ON EXTENSION extension_name

This form marks the index as dependent on the extension, or no longer dependent on that extension if NO is specified. An index that's marked as dependent on an extension is automatically dropped when the extension is dropped.

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

This form changes one or more index-method-specific storage parameters for the index. See CREATE INDEX for details on the available parameters. Note that the index contents will not be modified immediately by this command; depending on the parameter you might need to rebuild the index with REINDEX to get the desired effects.

RESET ( storage_parameter [, ... ] )

This form resets one or more index-method-specific storage parameters to their defaults. As with SET, a REINDEX might be needed to update the index entirely.

ALTER [ COLUMN ] column_number SET STATISTICS integer

This form sets the per-column statistics-gathering target for subsequent ANALYZE operations, though can be used only on index columns that are defined as an expression. Since expressions lack a unique name, we refer to them using the ordinal number of the index column. The target can be set in the range 0 to 10000; alternatively, set it to -1 to revert to using the system default statistics target (default_statistics_target). For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.

## PARAMETERS
IF EXISTS

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

column_number

The ordinal number refers to the ordinal (left-to-right) position of the index column.

name

The name (possibly schema-qualified) of an existing index to alter.

new_name

The new name for the index.

tablespace_name

The tablespace to which the index will be moved.

extension_name

The name of the extension that the index is to depend on.

storage_parameter

The name of an index-method-specific storage parameter.

value

The new value for an index-method-specific storage parameter. This might be a number or a word depending on the parameter.

## NOTES
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 variant, but this is now ignored (with a warning). An index cannot have an owner different from its table's owner. Changing the table's owner automatically changes the index as well.

Changing any part of a system catalog index is not permitted.

## EXAMPLES
To rename an existing index:

```
ALTER INDEX distributors RENAME TO suppliers;
```

To move an index to a different tablespace:

```
ALTER INDEX distributors SET TABLESPACE fasttablespace;
```

To change an index's fill factor (assuming that the index method supports it):

```
ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;
```

Set the statistics-gathering target for an expression index:

```
CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
```

## COMPATIBILITY
ALTER INDEX is a PostgreSQL extension.

## SEE ALSO
CREATE INDEX (CREATE_INDEX(7)), REINDEX(7)


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 a direct or indirect member of 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 35


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 }

    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method

    CLUSTER ON index_name

    SET WITHOUT CLUSTER

    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 also be a direct or indirect member of 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 }

         } [, ... ] )
```

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

You must own the operator to use ALTER OPERATOR. To alter the owner, you must also be a direct or indirect member of 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.

## 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 && (_int4, _int4) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
```

## 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 also be a direct or indirect member of 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))