title: DROP_FUNCTION(7)

summary: remove a function

text:
# DROP_FUNCTION(7)

## NAME
DROP_FUNCTION - remove a function

## SYNOPSIS

```
DROP FUNCTION [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]

    [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP FUNCTION removes the definition of an existing function. To execute this command the user must be the owner of the function. The argument types to the function must be specified, since several different functions can exist with the same name and different argument lists.

## PARAMETERS
IF EXISTS

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

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 DROP 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 DROP 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.

CASCADE

Automatically drop objects that depend on the function (such as operators or triggers), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the function if any objects depend on it. This is the default.

## EXAMPLES
This command removes the square root function:

```
DROP FUNCTION sqrt(integer);
```

Drop multiple functions in one command:

```
DROP FUNCTION sqrt(integer), sqrt(bigint);
```

If the function name is unique in its schema, it can be referred to without an argument list:

```
DROP FUNCTION update_employee_salaries;
```

Note that this is different from

```
DROP FUNCTION update_employee_salaries();
```

which refers to a function with zero arguments, whereas the first variant can refer to a function with any number of arguments, including zero, as long as the name is unique.

## COMPATIBILITY
This command conforms to the SQL standard, with these PostgreSQL extensions:

•The standard only allows one function to be dropped per command.

•The IF EXISTS option

•The ability to specify argument modes and names

## SEE ALSO
CREATE FUNCTION (CREATE_FUNCTION(7)), ALTER FUNCTION (ALTER_FUNCTION(7)), DROP PROCEDURE (DROP_PROCEDURE(7)), DROP ROUTINE (DROP_ROUTINE(7))


title: DROP_GROUP(7)

summary: remove a database role

text:
# DROP_GROUP(7)

## NAME
DROP_GROUP - remove a database role

## SYNOPSIS

```
DROP GROUP [ IF EXISTS ] name [, ...]
```

## DESCRIPTION
DROP GROUP is now an alias for DROP ROLE.

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

## SEE ALSO
DROP ROLE (DROP_ROLE(7))


title: DROP_INDEX(7)

summary: remove an index

text:
# DROP_INDEX(7)

## NAME
DROP_INDEX - remove an index

## SYNOPSIS

```
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP INDEX drops an existing index from the database system. To execute this command you must be the owner of the index.

## PARAMETERS
CONCURRENTLY

Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal DROP INDEX acquires an ACCESS EXCLUSIVE lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed. There are several caveats to be aware of when using this option. Only one index name can be specified, and the CASCADE option is not supported. (Thus, an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way.) Also, regular DROP INDEX commands can be performed within a transaction block, but DROP INDEX CONCURRENTLY cannot. Lastly, indexes on partitioned tables cannot be dropped using this option.

For temporary tables, DROP INDEX is always non-concurrent, as no other session can access them, and non-concurrent index drop is cheaper.

IF EXISTS

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

name

The name (optionally schema-qualified) of an index to remove.

CASCADE

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

RESTRICT

Refuse to drop the index if any objects depend on it. This is the default.

## EXAMPLES
This command will remove the index title_idx:

```
DROP INDEX title_idx;
```

## COMPATIBILITY
DROP INDEX is a PostgreSQL language extension. There are no provisions for indexes in the SQL standard.

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


title: DROP_LANGUAGE(7)

summary: remove a procedural language

text:
# DROP_LANGUAGE(7)

## NAME
DROP_LANGUAGE - remove a procedural language

## SYNOPSIS

```
DROP [ PROCEDURAL ] LANGUAGE [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP LANGUAGE removes the definition of a previously registered procedural language. You must be a superuser or the owner of the language to use DROP LANGUAGE.

Note

As of PostgreSQL 9.1, most procedural languages have been made into “extensions”, and should therefore be removed with DROP EXTENSION not DROP LANGUAGE.

## PARAMETERS
IF EXISTS

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

name

The name of an existing procedural language.

CASCADE

Automatically drop objects that depend on the language (such as functions in the language), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the language if any objects depend on it. This is the default.

## EXAMPLES
This command removes the procedural language plsample:

```
DROP LANGUAGE plsample;
```

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

## SEE ALSO
ALTER LANGUAGE (ALTER_LANGUAGE(7)), CREATE LANGUAGE (CREATE_LANGUAGE(7))


title: DROP_MATERIALIZED_VIEW(7)

summary: remove a materialized view

text:
# DROP_MATERIALIZED_VIEW(7)

## NAME
DROP_MATERIALIZED_VIEW - remove a materialized view

## SYNOPSIS

```
DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP MATERIALIZED VIEW drops an existing materialized view. To execute this command you must be the owner of the materialized view.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of the materialized view to remove.

CASCADE

Automatically drop objects that depend on the materialized view (such as other materialized views, or regular views), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the materialized view if any objects depend on it. This is the default.

## EXAMPLES
This command will remove the materialized view called order_summary:

```
DROP MATERIALIZED VIEW order_summary;
```

## COMPATIBILITY
DROP MATERIALIZED VIEW is a PostgreSQL extension.

## SEE ALSO
CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), ALTER MATERIALIZED VIEW (ALTER_MATERIALIZED_VIEW(7)), REFRESH MATERIALIZED VIEW (REFRESH_MATERIALIZED_VIEW(7))


title: DROP_OPERATOR(7)

summary: remove an operator

text:
# DROP_OPERATOR(7)

## NAME
DROP_OPERATOR - remove an operator

## SYNOPSIS

```
DROP OPERATOR [ IF EXISTS ] name ( { left_type | NONE } , right_type ) [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP OPERATOR drops an existing operator from the database system. To execute this command you must be the owner of the operator.

## PARAMETERS
IF EXISTS

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

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.

CASCADE

Automatically drop objects that depend on the operator (such as views using it), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the operator if any objects depend on it. This is the default.

## EXAMPLES
Remove the power operator a^b for type integer:

```
DROP OPERATOR ^ (integer, integer);
```

Remove the bitwise-complement prefix operator ~b for type bit:

```
DROP OPERATOR ~ (none, bit);
```

Remove multiple operators in one command:

```
DROP OPERATOR ~ (none, bit), ^ (integer, integer);
```

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

## SEE ALSO
CREATE OPERATOR (CREATE_OPERATOR(7)), ALTER OPERATOR (ALTER_OPERATOR(7))


title: DROP_OPERATOR_CLASS(7)

summary: remove an operator class

text:
# DROP_OPERATOR_CLASS(7)

## NAME
DROP_OPERATOR_CLASS - remove an operator class

## SYNOPSIS

```
DROP OPERATOR CLASS [ IF EXISTS ] name USING index_method [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP OPERATOR CLASS drops an existing operator class. To execute this command you must be the owner of the operator class.

DROP OPERATOR CLASS does not drop any of the operators or functions referenced by the class. If there are any indexes depending on the operator class, you will need to specify CASCADE for the drop to complete.

## PARAMETERS
IF EXISTS

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

name

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

index_method

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

CASCADE

Automatically drop objects that depend on the operator class (such as indexes), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the operator class if any objects depend on it. This is the default.

## NOTES
DROP OPERATOR CLASS will not drop the operator family containing the class, even if there is nothing else left in the family (in particular, in the case where the family was implicitly created by CREATE OPERATOR CLASS). An empty operator family is harmless, but for the sake of tidiness you might wish to remove the family with DROP OPERATOR FAMILY; or perhaps better, use DROP OPERATOR FAMILY in the first place.

## EXAMPLES
Remove the B-tree operator class widget_ops:

```
DROP OPERATOR CLASS widget_ops USING btree;
```

This command will not succeed if there are any existing indexes that use the operator class. Add CASCADE to drop such indexes along with the operator class.

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

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


title: DROP_OPERATOR_FAMILY(7)

summary: remove an operator family

text:
# DROP_OPERATOR_FAMILY(7)

## NAME
DROP_OPERATOR_FAMILY - remove an operator family

## SYNOPSIS

```
DROP OPERATOR FAMILY [ IF EXISTS ] name USING index_method [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP OPERATOR FAMILY drops an existing operator family. To execute this command you must be the owner of the operator family.

DROP OPERATOR FAMILY includes dropping any operator classes contained in the family, but it does not drop any of the operators or functions referenced by the family. If there are any indexes depending on operator classes within the family, you will need to specify CASCADE for the drop to complete.

## PARAMETERS
IF EXISTS

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

name

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

index_method

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

CASCADE

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

RESTRICT

Refuse to drop the operator family if any objects depend on it. This is the default.

## EXAMPLES
Remove the B-tree operator family float_ops:

```
DROP OPERATOR FAMILY float_ops USING btree;
```

This command will not succeed if there are any existing indexes that use operator classes within the family. Add CASCADE to drop such indexes along with the operator family.

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

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


title: DROP_OWNED(7)

text:
# DROP_OWNED(7)

## NAME
DROP_OWNED - remove database objects owned by a database role

## SYNOPSIS

```
DROP OWNED BY { name | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP OWNED drops all the objects within the current database that are owned by one of the specified roles. Any privileges granted to the given roles on objects in the current database or on shared objects (databases, tablespaces, configuration parameters) will also be revoked.

## PARAMETERS
name

The name of a role whose objects will be dropped, and whose privileges will be revoked.

CASCADE

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

RESTRICT

Refuse to drop the objects owned by a role if any other database objects depend on one of the affected objects. This is the default.

## NOTES
DROP OWNED is often used to prepare for the removal of one or more roles. Because DROP OWNED only affects the objects in the current database, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

Using the CASCADE option might make the command recurse to objects owned by other users.

The REASSIGN OWNED command is an alternative that reassigns the ownership of all the database objects owned by one or more roles. However, REASSIGN OWNED does not deal with privileges for other objects.

Databases and tablespaces owned by the role(s) will not be removed.

See Section 22.4 for more discussion.

## COMPATIBILITY
The DROP OWNED command is a PostgreSQL extension.

## SEE ALSO
REASSIGN OWNED (REASSIGN_OWNED(7)), DROP ROLE (DROP_ROLE(7))


title: DROP_POLICY(7)

summary: remove a row\-level security policy from a table

text:
# DROP_POLICY(7)

## NAME
DROP_POLICY - remove a row-level security policy from a table

## SYNOPSIS

```
DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP POLICY removes the specified policy from the table. Note that if the last policy is removed for a table and the table still has row-level security enabled via ALTER TABLE, then the default-deny policy will be used. ALTER TABLE ... DISABLE ROW LEVEL SECURITY can be used to disable row-level security for a table, whether policies for the table exist or not.

## PARAMETERS
IF EXISTS

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

name

The name of the policy to drop.

table_name

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

CASCADE

RESTRICT

These key words do not have any effect, since there are no dependencies on policies.

## EXAMPLES
To drop the policy called p1 on the table named my_table:

```
DROP POLICY p1 ON my_table;
```

## COMPATIBILITY
DROP POLICY is a PostgreSQL extension.

## SEE ALSO
CREATE POLICY (CREATE_POLICY(7)), ALTER POLICY (ALTER_POLICY(7))


title: DROP_PROCEDURE(7)

summary: remove a procedure

text:
# DROP_PROCEDURE(7)

## NAME
DROP_PROCEDURE - remove a procedure

## SYNOPSIS

```
DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]

    [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP PROCEDURE removes the definition of one or more existing procedures. To execute this command the user must be the owner of the procedure(s). The argument types to the procedure(s) usually must be specified, since several different procedures can exist with the same name and different argument lists.

## PARAMETERS
IF EXISTS

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

name

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

argmode

The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN (but see below).

argname

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

argtype

The data type(s) of the procedure's arguments (optionally schema-qualified), if any. See below for details.

CASCADE

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

RESTRICT

Refuse to drop the procedure if any objects depend on it. This is the default.

## NOTES
If there is only one procedure of the given name, the argument list can be omitted. Omit the parentheses too in this case.

In PostgreSQL, it's sufficient to list the input (including INOUT) arguments, because no two routines of the same name are allowed to share the same input-argument list. Moreover, the DROP command will not actually check that you wrote the types of OUT arguments correctly; so any arguments that are explicitly marked OUT are just noise. But writing them is recommendable for consistency with the corresponding CREATE command.

For compatibility with the SQL standard, it is also allowed to write all the argument data types (including those of OUT arguments) without any argmode markers. When this is done, the types of the procedure's OUT argument(s) will be verified against the command. This provision creates an ambiguity, in that when the argument list contains no argmode markers, it's unclear which rule is intended. The DROP command will attempt the lookup both ways, and will throw an error if two different procedures are found. To avoid the risk of such ambiguity, it's recommendable to write IN markers explicitly rather than letting them be defaulted, thus forcing the traditional PostgreSQL interpretation to be used.

The lookup rules just explained are also used by other commands that act on existing procedures, such as ALTER PROCEDURE and COMMENT ON PROCEDURE.

## EXAMPLES
If there is only one procedure do_db_maintenance, this command is sufficient to drop it:

```
DROP PROCEDURE do_db_maintenance;
```

Given this procedure definition:

```
CREATE PROCEDURE do_db_maintenance(IN target_schema text, OUT results text) ...
```

any one of these commands would work to drop it:

```
DROP PROCEDURE do_db_maintenance(IN target_schema text, OUT results text);
DROP PROCEDURE do_db_maintenance(IN text, OUT text);
DROP PROCEDURE do_db_maintenance(IN text);
DROP PROCEDURE do_db_maintenance(text);
DROP PROCEDURE do_db_maintenance(text, text);  -- potentially ambiguous
```

However, the last example would be ambiguous if there is also, say,

```
CREATE PROCEDURE do_db_maintenance(IN target_schema text, IN options text) ...
```

## COMPATIBILITY
This command conforms to the SQL standard, with these PostgreSQL extensions:

•The standard only allows one procedure to be dropped per command.

•The IF EXISTS option is an extension.

•The ability to specify argument modes and names is an extension, and the lookup rules differ when modes are given.

## SEE ALSO
CREATE PROCEDURE (CREATE_PROCEDURE(7)), ALTER PROCEDURE (ALTER_PROCEDURE(7)), DROP FUNCTION (DROP_FUNCTION(7)), DROP ROUTINE (DROP_ROUTINE(7))


title: DROP_PUBLICATION(7)

summary: remove a publication

text:
# DROP_PUBLICATION(7)

## NAME
DROP_PUBLICATION - remove a publication

## SYNOPSIS

```
DROP PUBLICATION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP PUBLICATION removes an existing publication from the database.

A publication can only be dropped by its owner or a superuser.

## PARAMETERS
IF EXISTS

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

name

The name of an existing publication.

CASCADE

RESTRICT

These key words do not have any effect, since there are no dependencies on publications.

## EXAMPLES
Drop a publication:

```
DROP PUBLICATION mypublication;
```

## COMPATIBILITY
DROP PUBLICATION is a PostgreSQL extension.

## SEE ALSO
CREATE PUBLICATION (CREATE_PUBLICATION(7)), ALTER PUBLICATION (ALTER_PUBLICATION(7))


title: DROP_ROLE(7)

summary: remove a database role

text:
# DROP_ROLE(7)

## NAME
DROP_ROLE - remove a database role

## SYNOPSIS

```
DROP ROLE [ IF EXISTS ] name [, ...]
```

## DESCRIPTION
DROP ROLE removes the specified role(s). To drop a superuser role, you must be a superuser yourself; to drop non-superuser roles, you must have CREATEROLE privilege.

A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so. Before dropping the role, you must drop all the objects it owns (or reassign their ownership) and revoke any privileges the role has been granted on other objects. The REASSIGN OWNED and DROP OWNED commands can be useful for this purpose; see Section 22.4 for more discussion.

However, it is not necessary to remove role memberships involving the role; DROP ROLE automatically revokes any memberships of the target role in other roles, and of other roles in the target role. The other roles are not dropped nor otherwise affected.

## PARAMETERS
IF EXISTS

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

name

The name of the role to remove.

## NOTES
PostgreSQL includes a program dropuser(1) that has the same functionality as this command (in fact, it calls this command) but can be run from the command shell.

## EXAMPLES
To drop a role:

```
DROP ROLE jonathan;
```

## COMPATIBILITY
The SQL standard defines DROP ROLE, but it allows only one role to be dropped at a time, and it specifies different privilege requirements than PostgreSQL uses.

## SEE ALSO
CREATE ROLE (CREATE_ROLE(7)), ALTER ROLE (ALTER_ROLE(7)), SET ROLE (SET_ROLE(7))


title: DROP_ROUTINE(7)

summary: remove a routine

text:
# DROP_ROUTINE(7)

## NAME
DROP_ROUTINE - remove a routine

## SYNOPSIS

```
DROP ROUTINE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]

    [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP ROUTINE removes the definition of one or more existing routines. The term “routine” includes aggregate functions, normal functions, and procedures. See under DROP AGGREGATE (DROP_AGGREGATE(7)), DROP FUNCTION (DROP_FUNCTION(7)), and DROP PROCEDURE (DROP_PROCEDURE(7)) for the description of the parameters, more examples, and further details.

## NOTES
The lookup rules used by DROP ROUTINE are fundamentally the same as for DROP PROCEDURE; in particular, DROP ROUTINE shares that command's behavior of considering an argument list that has no argmode markers to be possibly using the SQL standard's definition that OUT arguments are included in the list. (DROP AGGREGATE and DROP FUNCTION do not do that.)

In some cases where the same name is shared by routines of different kinds, it is possible for DROP ROUTINE to fail with an ambiguity error when a more specific command (DROP FUNCTION, etc.) would work. Specifying the argument type list more carefully will also resolve such problems.

These lookup rules are also used by other commands that act on existing routines, such as ALTER ROUTINE and COMMENT ON ROUTINE.

## EXAMPLES
To drop the routine foo for type integer:

```
DROP ROUTINE foo(integer);
```

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

## COMPATIBILITY
This command conforms to the SQL standard, with these PostgreSQL extensions:

•The standard only allows one routine to be dropped per command.

•The IF EXISTS option is an extension.

•The ability to specify argument modes and names is an extension, and the lookup rules differ when modes are given.

•User-definable aggregate functions are an extension.

## SEE ALSO
DROP AGGREGATE (DROP_AGGREGATE(7)), DROP FUNCTION (DROP_FUNCTION(7)), DROP PROCEDURE (DROP_PROCEDURE(7)), ALTER ROUTINE (ALTER_ROUTINE(7))

Note that there is no CREATE ROUTINE command.


title: DROP_RULE(7)

summary: remove a rewrite rule

text:
# DROP_RULE(7)

## NAME
DROP_RULE - remove a rewrite rule

## SYNOPSIS

```
DROP RULE [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP RULE drops a rewrite rule.

## PARAMETERS
IF EXISTS

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

name

The name of the rule to drop.

table_name

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

CASCADE

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

RESTRICT

Refuse to drop the rule if any objects depend on it. This is the default.

## EXAMPLES
To drop the rewrite rule newrule:

```
DROP RULE newrule ON mytable;
```

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

## SEE ALSO
CREATE RULE (CREATE_RULE(7)), ALTER RULE (ALTER_RULE(7))


title: DROP_SCHEMA(7)

summary: remove a schema

text:
# DROP_SCHEMA(7)

## NAME
DROP_SCHEMA - remove a schema

## SYNOPSIS

```
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP SCHEMA removes schemas from the database.

A schema can only be dropped by its owner or a superuser. Note that the owner can drop the schema (and thereby all contained objects) even if they do not own some of the objects within the schema.

## PARAMETERS
IF EXISTS

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

name

The name of a schema.

CASCADE

Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the schema if it contains any objects. This is the default.

## NOTES
Using the CASCADE option might make the command remove objects in other schemas besides the one(s) named.

## EXAMPLES
To remove schema mystuff from the database, along with everything it contains:

```
DROP SCHEMA mystuff CASCADE;
```

## COMPATIBILITY
DROP SCHEMA is fully conforming with the SQL standard, except that the standard only allows one schema to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
ALTER SCHEMA (ALTER_SCHEMA(7)), CREATE SCHEMA (CREATE_SCHEMA(7))


title: DROP_SEQUENCE(7)

summary: remove a sequence

text:
# DROP_SEQUENCE(7)

## NAME
DROP_SEQUENCE - remove a sequence

## SYNOPSIS

```
DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP SEQUENCE removes sequence number generators. A sequence can only be dropped by its owner or a superuser.

## PARAMETERS
IF EXISTS

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

name

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

CASCADE

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

RESTRICT

Refuse to drop the sequence if any objects depend on it. This is the default.

## EXAMPLES
To remove the sequence serial:

```
DROP SEQUENCE serial;
```

## COMPATIBILITY
DROP SEQUENCE conforms to the SQL standard, except that the standard only allows one sequence to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
CREATE SEQUENCE (CREATE_SEQUENCE(7)), ALTER SEQUENCE (ALTER_SEQUENCE(7))


title: DROP_SERVER(7)

summary: remove a foreign server descriptor

text:
# DROP_SERVER(7)

## NAME
DROP_SERVER - remove a foreign server descriptor

## SYNOPSIS

```
DROP SERVER [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP SERVER removes an existing foreign server descriptor. To execute this command, the current user must be the owner of the server.

## PARAMETERS
IF EXISTS

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

name

The name of an existing server.

CASCADE

Automatically drop objects that depend on the server (such as user mappings), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the server if any objects depend on it. This is the default.

## EXAMPLES
Drop a server foo if it exists:

```
DROP SERVER IF EXISTS foo;
```

## COMPATIBILITY
DROP SERVER conforms to ISO/IEC 9075-9 (SQL/MED). The IF EXISTS clause is a PostgreSQL extension.

## SEE ALSO
CREATE SERVER (CREATE_SERVER(7)), ALTER SERVER (ALTER_SERVER(7))


title: DROP_STATISTICS(7)

summary: remove extended statistics

text:
# DROP_STATISTICS(7)

## NAME
DROP_STATISTICS - remove extended statistics

## SYNOPSIS

```
DROP STATISTICS [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP STATISTICS removes statistics object(s) from the database. Only the statistics object's owner, the schema owner, or a superuser can drop a statistics object.

## PARAMETERS
IF EXISTS

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

name

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

CASCADE

RESTRICT

These key words do not have any effect, since there are no dependencies on statistics.

## EXAMPLES
To destroy two statistics objects in different schemas, without failing if they don't exist:

```
DROP STATISTICS IF EXISTS

    accounting.users_uid_creation,

    public.grants_user_role;
```

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

## SEE ALSO
ALTER STATISTICS (ALTER_STATISTICS(7)), CREATE STATISTICS (CREATE_STATISTICS(7))


title: DROP_SUBSCRIPTION(7)

summary: remove a subscription

text:
# DROP_SUBSCRIPTION(7)

## NAME
DROP_SUBSCRIPTION - remove a subscription

## SYNOPSIS

```
DROP SUBSCRIPTION [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP SUBSCRIPTION removes a subscription from the database cluster.

A subscription can only be dropped by a superuser.

DROP SUBSCRIPTION cannot be executed inside a transaction block if the subscription is associated with a replication slot. (You can use ALTER SUBSCRIPTION to unset the slot.)

## PARAMETERS
IF EXISTS

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

name

The name of a subscription to be dropped.

CASCADE

RESTRICT

These key words do not have any effect, since there are no dependencies on subscriptions.

## NOTES
When dropping a subscription that is associated with a replication slot on the remote host (the normal state), DROP SUBSCRIPTION will connect to the remote host and try to drop the replication slot (and any remaining table synchronization slots) as part of its operation. This is necessary so that the resources allocated for the subscription on the remote host are released. If this fails, either because the remote host is not reachable or because the remote replication slot cannot be dropped or does not exist or never existed, the DROP SUBSCRIPTION command will fail. To proceed in this situation, first disable the subscription by executing ALTER SUBSCRIPTION ... DISABLE, and then disassociate it from the replication slot by executing ALTER SUBSCRIPTION ... SET (slot_name = NONE). After that, DROP SUBSCRIPTION will no longer attempt any actions on a remote host. Note that if the remote replication slot still exists, it (and any related table synchronization slots) should then be dropped manually; otherwise it/they will continue to reserve WAL and might eventually cause the disk to fill up. See also Section 31.2.1.

If a subscription is associated with a replication slot, then DROP SUBSCRIPTION cannot be executed inside a transaction block.

## EXAMPLES
Drop a subscription:

```
DROP SUBSCRIPTION mysub;
```

## COMPATIBILITY
DROP SUBSCRIPTION is a PostgreSQL extension.

## SEE ALSO
CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)), ALTER SUBSCRIPTION (ALTER_SUBSCRIPTION(7))


title: DROP_TABLE(7)

summary: remove a table

text:
# DROP_TABLE(7)

## NAME
DROP_TABLE - remove a table

## SYNOPSIS

```
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TABLE removes tables from the database. Only the table owner, the schema owner, and superuser can drop a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE.

DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of the table to drop.

CASCADE

Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the table if any objects depend on it. This is the default.

## EXAMPLES
To destroy two tables, films and distributors:

```
DROP TABLE films, distributors;
```

## COMPATIBILITY
This command conforms to the SQL standard, except that the standard only allows one table to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
ALTER TABLE (ALTER_TABLE(7)), CREATE TABLE (CREATE_TABLE(7))


title: DROP_TABLESPACE(7)

summary: remove a tablespace

text:
# DROP_TABLESPACE(7)

## NAME
DROP_TABLESPACE - remove a tablespace

## SYNOPSIS

```
DROP TABLESPACE [ IF EXISTS ] name
```

## DESCRIPTION
DROP TABLESPACE removes a tablespace from the system.

A tablespace can only be dropped by its owner or a superuser. The tablespace must be empty of all database objects before it can be dropped. It is possible that objects in other databases might still reside in the tablespace even if no objects in the current database are using the tablespace. Also, if the tablespace is listed in the temp_tablespaces setting of any active session, the DROP might fail due to temporary files residing in the tablespace.

## PARAMETERS
IF EXISTS

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

name

The name of a tablespace.

## NOTES
DROP TABLESPACE cannot be executed inside a transaction block.

## EXAMPLES
To remove tablespace mystuff from the system:

```
DROP TABLESPACE mystuff;
```

## COMPATIBILITY
DROP TABLESPACE is a PostgreSQL extension.

## SEE ALSO
CREATE TABLESPACE (CREATE_TABLESPACE(7)), ALTER TABLESPACE (ALTER_TABLESPACE(7))


title: DROP_TEXT_SEARCH_CONFIGURATION(7)

summary: remove a text search configuration

text:
# DROP_TEXT_SEARCH_CONFIGURATION(7)

## NAME
DROP_TEXT_SEARCH_CONFIGURATION - remove a text search configuration

## SYNOPSIS

```
DROP TEXT SEARCH CONFIGURATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TEXT SEARCH CONFIGURATION drops an existing text search configuration. To execute this command you must be the owner of the configuration.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of an existing text search configuration.

CASCADE

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

RESTRICT

Refuse to drop the text search configuration if any objects depend on it. This is the default.

## EXAMPLES
Remove the text search configuration my_english:

```
DROP TEXT SEARCH CONFIGURATION my_english;
```

This command will not succeed if there are any existing indexes that reference the configuration in to_tsvector calls. Add CASCADE to drop such indexes along with the text search configuration.

## COMPATIBILITY
There is no DROP TEXT SEARCH CONFIGURATION statement in the SQL standard.

## SEE ALSO
ALTER TEXT SEARCH CONFIGURATION (ALTER_TEXT_SEARCH_CONFIGURATION(7)), CREATE TEXT SEARCH CONFIGURATION (CREATE_TEXT_SEARCH_CONFIGURATION(7))


title: DROP_TEXT_SEARCH_DICTIONARY(7)

summary: remove a text search dictionary

text:
# DROP_TEXT_SEARCH_DICTIONARY(7)

## NAME
DROP_TEXT_SEARCH_DICTIONARY - remove a text search dictionary

## SYNOPSIS

```
DROP TEXT SEARCH DICTIONARY [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TEXT SEARCH DICTIONARY drops an existing text search dictionary. To execute this command you must be the owner of the dictionary.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of an existing text search dictionary.

CASCADE

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

RESTRICT

Refuse to drop the text search dictionary if any objects depend on it. This is the default.

## EXAMPLES
Remove the text search dictionary english:

```
DROP TEXT SEARCH DICTIONARY english;
```

This command will not succeed if there are any existing text search configurations that use the dictionary. Add CASCADE to drop such configurations along with the dictionary.

## COMPATIBILITY
There is no DROP TEXT SEARCH DICTIONARY statement in the SQL standard.

## SEE ALSO
ALTER TEXT SEARCH DICTIONARY (ALTER_TEXT_SEARCH_DICTIONARY(7)), CREATE TEXT SEARCH DICTIONARY (CREATE_TEXT_SEARCH_DICTIONARY(7))


title: DROP_TEXT_SEARCH_PARSER(7)

summary: remove a text search parser

text:
# DROP_TEXT_SEARCH_PARSER(7)

## NAME
DROP_TEXT_SEARCH_PARSER - remove a text search parser

## SYNOPSIS

```
DROP TEXT SEARCH PARSER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TEXT SEARCH PARSER drops an existing text search parser. You must be a superuser to use this command.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of an existing text search parser.

CASCADE

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

RESTRICT

Refuse to drop the text search parser if any objects depend on it. This is the default.

## EXAMPLES
Remove the text search parser my_parser:

```
DROP TEXT SEARCH PARSER my_parser;
```

This command will not succeed if there are any existing text search configurations that use the parser. Add CASCADE to drop such configurations along with the parser.

## COMPATIBILITY
There is no DROP TEXT SEARCH PARSER statement in the SQL standard.

## SEE ALSO
ALTER TEXT SEARCH PARSER (ALTER_TEXT_SEARCH_PARSER(7)), CREATE TEXT SEARCH PARSER (CREATE_TEXT_SEARCH_PARSER(7))


title: DROP_TEXT_SEARCH_TEMPLATE(7)

summary: remove a text search template

text:
# DROP_TEXT_SEARCH_TEMPLATE(7)

## NAME
DROP_TEXT_SEARCH_TEMPLATE - remove a text search template

## SYNOPSIS

```
DROP TEXT SEARCH TEMPLATE [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TEXT SEARCH TEMPLATE drops an existing text search template. You must be a superuser to use this command.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of an existing text search template.

CASCADE

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

RESTRICT

Refuse to drop the text search template if any objects depend on it. This is the default.

## EXAMPLES
Remove the text search template thesaurus:

```
DROP TEXT SEARCH TEMPLATE thesaurus;
```

This command will not succeed if there are any existing text search dictionaries that use the template. Add CASCADE to drop such dictionaries along with the template.

## COMPATIBILITY
There is no DROP TEXT SEARCH TEMPLATE statement in the SQL standard.

## SEE ALSO
ALTER TEXT SEARCH TEMPLATE (ALTER_TEXT_SEARCH_TEMPLATE(7)), CREATE TEXT SEARCH TEMPLATE (CREATE_TEXT_SEARCH_TEMPLATE(7))


title: DROP_TRANSFORM(7)

summary: remove a transform

text:
# DROP_TRANSFORM(7)

## NAME
DROP_TRANSFORM - remove a transform

## SYNOPSIS

```
DROP TRANSFORM [ IF EXISTS ] FOR type_name LANGUAGE lang_name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TRANSFORM removes a previously defined transform.

To be able to drop a transform, you must own the type and the language. These are the same privileges that are required to create a transform.

## PARAMETERS
IF EXISTS

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

type_name

The name of the data type of the transform.

lang_name

The name of the language of the transform.

CASCADE

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

RESTRICT

Refuse to drop the transform if any objects depend on it. This is the default.

## EXAMPLES
To drop the transform for type hstore and language plpython3u:

```
DROP TRANSFORM FOR hstore LANGUAGE plpython3u;
```

## COMPATIBILITY
This form of DROP TRANSFORM is a PostgreSQL extension. See CREATE TRANSFORM (CREATE_TRANSFORM(7)) for details.

## SEE ALSO
CREATE TRANSFORM (CREATE_TRANSFORM(7))


title: DROP_TRIGGER(7)

summary: remove a trigger

text:
# DROP_TRIGGER(7)

## NAME
DROP_TRIGGER - remove a trigger

## SYNOPSIS

```
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TRIGGER removes an existing trigger definition. To execute this command, the current user must be the owner of the table for which the trigger is defined.

## PARAMETERS
IF EXISTS

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

name

The name of the trigger to remove.

table_name

The name (optionally schema-qualified) of the table for which the trigger is defined.

CASCADE

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

RESTRICT

Refuse to drop the trigger if any objects depend on it. This is the default.

## EXAMPLES
Destroy the trigger if_dist_exists on the table films:

```
DROP TRIGGER if_dist_exists ON films;
```

## COMPATIBILITY
The DROP TRIGGER statement in PostgreSQL is incompatible with the SQL standard. In the SQL standard, trigger names are not local to tables, so the command is simply DROP TRIGGER name.

## SEE ALSO
CREATE TRIGGER (CREATE_TRIGGER(7))


title: DROP_TYPE(7)

summary: remove a data type

text:
# DROP_TYPE(7)

## NAME
DROP_TYPE - remove a data type

## SYNOPSIS

```
DROP TYPE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP TYPE removes a user-defined data type. Only the owner of a type can remove it.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of the data type to remove.

CASCADE

Automatically drop objects that depend on the type (such as table columns, functions, and operators), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the type if any objects depend on it. This is the default.

## EXAMPLES
To remove the data type box:

```
DROP TYPE box;
```

## COMPATIBILITY
This command is similar to the corresponding command in the SQL standard, apart from the IF EXISTS option, which is a PostgreSQL extension. But note that much of the CREATE TYPE command and the data type extension mechanisms in PostgreSQL differ from the SQL standard.

## SEE ALSO
ALTER TYPE (ALTER_TYPE(7)), CREATE TYPE (CREATE_TYPE(7))


title: DROP_USER(7)

summary: remove a database role

text:
# DROP_USER(7)

## NAME
DROP_USER - remove a database role

## SYNOPSIS

```
DROP USER [ IF EXISTS ] name [, ...]
```

## DESCRIPTION
DROP USER is simply an alternate spelling of DROP ROLE.

## COMPATIBILITY
The DROP USER statement is a PostgreSQL extension. The SQL standard leaves the definition of users to the implementation.

## SEE ALSO
DROP ROLE (DROP_ROLE(7))


title: DROP_USER_MAPPING(7)

text:
# DROP_USER_MAPPING(7)

## NAME
DROP_USER_MAPPING - remove a user mapping for a foreign server

## SYNOPSIS

```
DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_ROLE | CURRENT_USER | PUBLIC } SERVER server_name
```

## DESCRIPTION
DROP USER MAPPING removes an existing user mapping from foreign server.

The owner of a foreign server can drop user mappings for that server for any user. Also, a user can drop a user mapping for their own user name if USAGE privilege on the server has been granted to the user.

## PARAMETERS
IF EXISTS

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

user_name

User name of the mapping. CURRENT_ROLE, CURRENT_USER, and USER match the name of the current user. PUBLIC is used to match all present and future user names in the system.

server_name

Server name of the user mapping.

## EXAMPLES
Drop a user mapping bob, server foo if it exists:

```
DROP USER MAPPING IF EXISTS FOR bob SERVER foo;
```

## COMPATIBILITY
DROP USER MAPPING conforms to ISO/IEC 9075-9 (SQL/MED). The IF EXISTS clause is a PostgreSQL extension.

## SEE ALSO
CREATE USER MAPPING (CREATE_USER_MAPPING(7)), ALTER USER MAPPING (ALTER_USER_MAPPING(7))


title: DROP_VIEW(7)

summary: remove a view

text:
# DROP_VIEW(7)

## NAME
DROP_VIEW - remove a view

## SYNOPSIS

```
DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP VIEW drops an existing view. To execute this command you must be the owner of the view.

## PARAMETERS
IF EXISTS

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

name

The name (optionally schema-qualified) of the view to remove.

CASCADE

Automatically drop objects that depend on the view (such as other views), and in turn all objects that depend on those objects (see Section 5.14).

RESTRICT

Refuse to drop the view if any objects depend on it. This is the default.

## EXAMPLES
This command will remove the view called kinds:

```
DROP VIEW kinds;
```

## COMPATIBILITY
This command conforms to the SQL standard, except that the standard only allows one view to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
ALTER VIEW (ALTER_VIEW(7)), CREATE VIEW (CREATE_VIEW(7))


END

title: END(7)

summary: commit the current transaction

text:
# END(7)

## NAME
END - commit the current transaction

## SYNOPSIS

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

## DESCRIPTION
END commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs. This command is a PostgreSQL extension that is equivalent to COMMIT.

## 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 (SET_TRANSACTION(7))) as the just finished one. Otherwise, no new transaction is started.

## NOTES
Use ROLLBACK to abort a transaction.

Issuing END when not inside a transaction does no harm, but it will provoke a warning message.

## EXAMPLES
To commit the current transaction and make all changes permanent:

```
END;
```

## COMPATIBILITY
END is a PostgreSQL extension that provides functionality equivalent to COMMIT, which is specified in the SQL standard.

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


EXECUTE

title: EXECUTE(7)

summary: execute a prepared statement

text:
# EXECUTE(7)

## NAME
EXECUTE - execute a prepared statement

## SYNOPSIS

```
EXECUTE name [ ( parameter [, ...] ) ]
```

## DESCRIPTION
EXECUTE is used to execute a previously prepared statement. Since prepared statements only exist for the duration of a session, the prepared statement must have been created by a PREPARE statement executed earlier in the current session.

If the PREPARE statement that created the statement specified some parameters, a compatible set of parameters must be passed to the EXECUTE statement, or else an error is raised. Note that (unlike functions) prepared statements are not overloaded based on the type or number of their parameters; the name of a prepared statement must be unique within a database session.

For more information on the creation and usage of prepared statements, see PREPARE(7).

## PARAMETERS
name

The name of the prepared statement to execute.

parameter

The actual value of a parameter to the prepared statement. This must be an expression yielding a value that is compatible with the data type of this parameter, as was determined when the prepared statement was created.

## OUTPUTS
The command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE.

## EXAMPLES
Examples are given in Examples in the PREPARE(7) documentation.

## COMPATIBILITY
The SQL standard includes an EXECUTE statement, but it is only for use in embedded SQL. This version of the EXECUTE statement also uses a somewhat different syntax.

## SEE ALSO
DEALLOCATE(7), PREPARE(7)


EXPLAIN

title: EXPLAIN(7)

summary: show the execution plan of a statement

text:
# EXPLAIN(7)

## NAME
EXPLAIN - show the execution plan of a statement

## SYNOPSIS

```
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:

    ANALYZE [ boolean ]

    VERBOSE [ boolean ]

    COSTS [ boolean ]

    SETTINGS [ boolean ]

    BUFFERS [ boolean ]

    WAL [ boolean ]

    TIMING [ boolean ]

    SUMMARY [ boolean ]

    FORMAT { TEXT | XML | JSON | YAML }
```

## DESCRIPTION
This command displays the execution plan that the PostgreSQL planner generates for the supplied statement. The execution plan shows how the table(s) referenced by the statement will be scanned — by plain sequential scan, index scan, etc. — and if multiple tables are referenced, what join algorithms will be used to bring together the required rows from each input table.

The most critical part of the display is the estimated statement execution cost, which is the planner's guess at how long it will take to run the statement (measured in cost units that are arbitrary, but conventionally mean disk page fetches). Actually two numbers are shown: the start-up cost before the first row can be returned, and the total cost to return all the rows. For most queries the total cost is what matters, but in contexts such as a subquery in EXISTS, the planner will choose the smallest start-up cost instead of the smallest total cost (since the executor will stop after getting one row, anyway). Also, if you limit the number of rows to return with a LIMIT clause, the planner makes an appropriate interpolation between the endpoint costs to estimate which plan is really the cheapest.

The ANALYZE option causes the statement to be actually executed, not only planned. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node (in milliseconds) and the total number of rows it actually returned. This is useful for seeing whether the planner's estimates are close to reality.

Important

Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:

```
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
```

Only the ANALYZE and VERBOSE options can be specified, and only in that order, without surrounding the option list in parentheses. Prior to PostgreSQL 9.0, the unparenthesized syntax was the only one supported. It is expected that all new options will be supported only in the parenthesized syntax.

## PARAMETERS
ANALYZE

Carry out the command and show actual run times and other statistics. This parameter defaults to FALSE.

VERBOSE

Display additional information regarding the plan. Specifically, include the output column list for each node in the plan tree, schema-qualify table and function names, always label variables in expressions with their range table alias, and always print the name of each trigger for which statistics are displayed. The query identifier will also be displayed if one has been computed, see compute_query_id for more details. This parameter defaults to FALSE.

COSTS

Include information on the estimated startup and total cost of each plan node, as well as the estimated number of rows and the estimated width of each row. This parameter defaults to TRUE.

SETTINGS

Include information on configuration parameters. Specifically, include options affecting query planning with value different from the built-in default value. This parameter defaults to FALSE.

BUFFERS

Include information on buffer usage. Specifically, include the number of shared blocks hit, read, dirtied, and written, the number of local blocks hit, read, dirtied, and written, the number of temp blocks read and written, and the time spent reading and writing data file blocks and temporary file blocks (in milliseconds) if track_io_timing is enabled. A hit means that a read was avoided because the block was found already in cache when needed. Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temporary blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases. The number of blocks dirtied indicates the number of previously unmodified blocks that were changed by this query; while the number of blocks written indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed. It defaults to FALSE.

WAL

Include information on WAL record generation. Specifically, include the number of records, number of full page images (fpi) and the amount of WAL generated in bytes. In text format, only non-zero values are printed. This parameter may only be used when ANALYZE is also enabled. It defaults to FALSE.

TIMING

Include actual startup time and time spent in each node in the output. The overhead of repeatedly reading the system clock can slow down the query significantly on some systems, so it may be useful to set this parameter to FALSE when only actual row counts, and not exact times, are needed. Run time of the entire statement is always measured, even when node-level timing is turned off with this option. This parameter may only be used when ANALYZE is also enabled. It defaults to TRUE.

SUMMARY

Include summary information (e.g., totaled timing information) after the query plan. Summary information is included by default when ANALYZE is used but otherwise is not included by default, but can be enabled using this option. Planning time in EXPLAIN EXECUTE includes the time required to fetch the plan from the cache and the time required for re-planning, if necessary.

FORMAT

Specify the output format, which can be TEXT, XML, JSON, or YAML. Non-text output contains the same information as the text output format, but is easier for programs to parse. This parameter defaults to TEXT.

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.

statement

Any SELECT, INSERT, UPDATE, DELETE, MERGE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS statement, whose execution plan you wish to see.

## OUTPUTS
The command's result is a textual description of the plan selected for the statement, optionally annotated with execution statistics. Section 14.1 describes the information provided.

## NOTES
In order to allow the PostgreSQL query planner to make reasonably informed decisions when optimizing queries, the pg_statistic data should be up-to-date for all tables used in the query. Normally the autovacuum daemon will take care of that automatically. But if a table has recently had substantial changes in its contents, you might need to do a manual ANALYZE rather than wait for autovacuum to catch up with the changes.

In order to measure the run-time cost of each node in the execution plan, the current implementation of EXPLAIN ANALYZE adds profiling overhead to query execution. As a result, running EXPLAIN ANALYZE on a query can sometimes take significantly longer than executing the query normally. The amount of overhead depends on the nature of the query, as well as the platform being used. The worst case occurs for plan nodes that in themselves require very little time per execution, and on machines that have relatively slow operating system calls for obtaining the time of day.

## EXAMPLES
To show the plan for a simple query on a table with a single integer column and 10000 rows:

```
EXPLAIN SELECT * FROM foo;

                       QUERY PLAN
---------------------------------------------------------

 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)
```

Here is the same query, with JSON output formatting:

```
EXPLAIN (FORMAT JSON) SELECT * FROM foo;

           QUERY PLAN
--------------------------------

 [                             +

   {                           +

     "Plan": {                 +

       "Node Type": "Seq Scan",+

       "Relation Name": "foo", +

       "Alias": "foo",         +

       "Startup Cost": 0.00,   +

       "Total Cost": 155.00,   +

       "Plan Rows": 10000,     +

       "Plan Width": 4         +

     }                         +

   }                           +

 ]
(1 row)
```

If there is an index and we use a query with an indexable WHERE condition, EXPLAIN might show a different plan:

```
EXPLAIN SELECT * FROM foo WHERE i = 4;

                         QUERY PLAN
--------------------------------------------------------------

 Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)

   Index Cond: (i = 4)
(2 rows)
```

Here is the same query, but in YAML format:

```
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';

          QUERY PLAN
-------------------------------

 - Plan:                      +

     Node Type: "Index Scan"  +

     Scan Direction: "Forward"+

     Index Name: "fi"         +

     Relation Name: "foo"     +

     Alias: "foo"             +

     Startup Cost: 0.00       +

     Total Cost: 5.98         +

     Plan Rows: 1             +

     Plan Width: 4            +

     Index Cond: "(i = 4)"
(1 row)
```

XML format is left as an exercise for the reader.

Here is the same plan with cost estimates suppressed:

```
EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;

        QUERY PLAN
----------------------------

 Index Scan using fi on foo

   Index Cond: (i = 4)
(2 rows)
```

Here is an example of a query plan for a query using an aggregate function:

```
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

                             QUERY PLAN
---------------------------------------------------------------------

 Aggregate  (cost=23.93..23.93 rows=1 width=4)

   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)

         Index Cond: (i < 10)
(3 rows)
```

Here is an example of using EXPLAIN EXECUTE to display the execution plan for a prepared query:

```
PREPARE query(int, int) AS SELECT sum(bar) FROM test

    WHERE id > $1 AND id < $2

    GROUP BY foo;
EXPLAIN ANALYZE EXECUTE query(100, 200);

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------

 HashAggregate  (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)

   Group Key: foo

   ->  Index Scan using test_pkey on test  (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)

         Index Cond: ((id > $1) AND (id < $2))

 Planning time: 0.197 ms

 Execution time: 0.225 ms
(6 rows)
```

Of course, the specific numbers shown here depend on the actual contents of the tables involved. Also note that the numbers, and even the selected query strategy, might vary between PostgreSQL releases due to planner improvements. In addition, the ANALYZE command uses random sampling to estimate data statistics; therefore, it is possible for cost estimates to change after a fresh run of ANALYZE, even if the actual distribution of data in the table has not changed.

## COMPATIBILITY
There is no EXPLAIN statement defined in the SQL standard.

## SEE ALSO
ANALYZE(7)


FETCH

title: FETCH(7)

text:
# FETCH(7)

## NAME
FETCH - retrieve rows from a query using a cursor

## SYNOPSIS

```
FETCH [ direction ] [ FROM | IN ] cursor_name
where direction can be one of:

    NEXT

    PRIOR

    FIRST

    LAST

    ABSOLUTE count

    RELATIVE count

    count

    ALL

    FORWARD

    FORWARD count

    FORWARD ALL

    BACKWARD

    BACKWARD count

    BACKWARD ALL
```

## DESCRIPTION
FETCH retrieves rows using a previously-created cursor.

A cursor has an associated position, which is used by FETCH. The cursor position can be before the first row of the query result, on any particular row of the result, or after the last row of the result. When created, a cursor is positioned before the first row. After fetching some rows, the cursor is positioned on the row most recently retrieved. If FETCH runs off the end of the available rows then the cursor is left positioned after the last row, or before the first row if fetching backward. FETCH ALL or FETCH BACKWARD ALL will always leave the cursor positioned after the last row or before the first row.

The forms NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE fetch a single row after moving the cursor appropriately. If there is no such row, an empty result is returned, and the cursor is left positioned before the first row or after the last row as appropriate.

The forms using FORWARD and BACKWARD retrieve the indicated number of rows moving in the forward or backward direction, leaving the cursor positioned on the last-returned row (or after/before all rows, if the count exceeds the number of rows available).

RELATIVE 0, FORWARD 0, and BACKWARD 0 all request fetching the current row without moving the cursor, that is, re-fetching the most recently fetched row. This will succeed unless the cursor is positioned before the first row or after the last row; in which case, no row is returned.

Note

This page describes usage of cursors at the SQL command level. If you are trying to use cursors inside a PL/pgSQL function, the rules are different — see Section 43.7.3.

## PARAMETERS
direction

direction defines the fetch direction and number of rows to fetch. It can be one of the following: NEXT

Fetch the next row. This is the default if direction is omitted.

PRIOR

Fetch the prior row.

FIRST

Fetch the first row of the query (same as ABSOLUTE 1).

LAST

Fetch the last row of the query (same as ABSOLUTE -1).

ABSOLUTE count

Fetch the count'th row of the query, or the abs(count)'th row from the end if count is negative. Position before first row or after last row if count is out of range; in particular, ABSOLUTE 0 positions before the first row.

RELATIVE count

Fetch the count'th succeeding row, or the abs(count)'th prior row if count is negative. RELATIVE 0 re-fetches the current row, if any.

count

Fetch the next count rows (same as FORWARD count).

ALL

Fetch all remaining rows (same as FORWARD ALL).

FORWARD

Fetch the next row (same as NEXT).

FORWARD count

Fetch the next count rows. FORWARD 0 re-fetches the current row.

FORWARD ALL

Fetch all remaining rows.

BACKWARD

Fetch the prior row (same as PRIOR).

BACKWARD count

Fetch the prior count rows (scanning backwards). BACKWARD 0 re-fetches the current row.

BACKWARD ALL

Fetch all prior rows (scanning backwards).

count

count is a possibly-signed integer constant, determining the location or number of rows to fetch. For FORWARD and BACKWARD cases, specifying a negative count is equivalent to changing the sense of FORWARD and BACKWARD.

cursor_name

An open cursor's name.

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

```
FETCH count
```

The count is the number of rows fetched (possibly zero). Note that in psql, the command tag will not actually be displayed, since psql displays the fetched rows instead.

## NOTES
The cursor should be declared with the SCROLL option if one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count. For simple queries PostgreSQL will allow backwards fetch from cursors not declared with SCROLL, but this behavior is best not relied on. If the cursor is declared with NO SCROLL, no backward fetches are allowed.

ABSOLUTE fetches are not any faster than navigating to the desired row with a relative move: the underlying implementation must traverse all the intermediate rows anyway. Negative absolute fetches are even worse: the query must be read to the end to find the last row, and then traversed backward from there. However, rewinding to the start of the query (as with FETCH ABSOLUTE 0) is fast.

DECLARE is used to define a cursor. Use MOVE to change cursor position without retrieving data.

## EXAMPLES
The following example traverses a table using a cursor:

```
BEGIN WORK;
-- Set up a cursor:
DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
-- Fetch the first 5 rows in the cursor liahona:
FETCH FORWARD 5 FROM liahona;

 code  |          title          | did | date_prod  |   kind   |  len
-------+-------------------------+-----+------------+----------+-------

 BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44

 BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43

 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25

 P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08

 P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28
-- Fetch the previous row:
FETCH PRIOR FROM liahona;

 code  |  title  | did | date_prod  |  kind  |  len
-------+---------+-----+------------+--------+-------

 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT WORK;
```

## COMPATIBILITY
The SQL standard defines FETCH for use in embedded SQL only. The variant of FETCH described here returns the data as if it were a SELECT result rather than placing it in host variables. Other than this point, FETCH is fully upward-compatible with the SQL standard.

The FETCH forms involving FORWARD and BACKWARD, as well as the forms FETCH count and FETCH ALL, in which FORWARD is implicit, are PostgreSQL extensions.

The SQL standard allows only FROM preceding the cursor name; the option to use IN, or to leave them out altogether, is an extension.

## SEE ALSO
CLOSE(7), DECLARE(7), MOVE(7)


GRANT

title: GRANT(7)

summary: define access privileges

text:
# GRANT(7)

## NAME
GRANT - define access privileges

## SYNOPSIS

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

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

    ON { [ TABLE ] table_name [, ...]

         | ALL TABLES IN SCHEMA schema_name [, ...] }

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )

    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }

    ON [ TABLE ] table_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { { USAGE | SELECT | UPDATE }

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

    ON { SEQUENCE sequence_name [, ...]

         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }

    ON DATABASE database_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }

    ON DOMAIN domain_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }

    ON FOREIGN DATA WRAPPER fdw_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }

    ON FOREIGN SERVER server_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }

    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]

         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }

    ON LANGUAGE lang_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }

    ON LARGE OBJECT loid [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }

    ON PARAMETER configuration_parameter [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }

    ON SCHEMA schema_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { CREATE | ALL [ PRIVILEGES ] }

    ON TABLESPACE tablespace_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }

    ON TYPE type_name [, ...]

    TO role_specification [, ...] [ WITH GRANT OPTION ]

    [ GRANTED BY role_specification ]
GRANT role_name [, ...] TO role_specification [, ...]

    [ WITH ADMIN OPTION ]

    [ GRANTED BY role_specification ]
where role_specification can be:

    [ GROUP ] role_name

  | PUBLIC

  | CURRENT_ROLE

  | CURRENT_USER

  | SESSION_USER
```

## DESCRIPTION
The GRANT command has two basic variants: one that grants privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, large object, configuration parameter, schema, tablespace, or type), and one that grants membership in a role. These variants are similar in many ways, but they are different enough to be described separately.

### GRANT on Database Objects
This variant of the GRANT command gives specific privileges on a database object to one or more roles. These privileges are added to those already granted, if any.

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

If WITH GRANT OPTION is specified, the recipient of the privilege can in turn grant it to others. Without a grant option, the recipient cannot do that. Grant options cannot be granted to PUBLIC.

If GRANTED BY is specified, the specified grantor must be the current user. This clause is currently present in this form only for SQL compatibility.

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of their own privileges for safety.)

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.

The possible privileges are:

SELECT

INSERT

UPDATE

DELETE

TRUNCATE

REFERENCES

TRIGGER

CREATE

CONNECT

TEMPORARY

EXECUTE

USAGE

SET

ALTER SYSTEM

Specific types of privileges, as defined in Section 5.7.

TEMP

Alternative spelling for TEMPORARY.

ALL PRIVILEGES

Grant all of the privileges available for the object's type. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.

The FUNCTION syntax works for plain functions, aggregate functions, and window functions, but not for procedures; use PROCEDURE for those. Alternatively, use ROUTINE to refer to a function, aggregate function, window function, or procedure regardless of its precise type.

There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, functions, and procedures. ALL TABLES also affects views and foreign tables, just like the specific-object GRANT command. ALL FUNCTIONS also affects aggregate and window functions, but not procedures, again just like the specific-object GRANT command. Use ALL ROUTINES to include procedures.

### GRANT on Roles
This variant of the GRANT command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its members.

If WITH ADMIN OPTION is specified, the member can in turn grant membership in the role to others, and revoke membership in the role as well. Without the admin option, ordinary users cannot do that. A role is not considered to hold WITH ADMIN OPTION on itself. Database superusers can grant or revoke membership in any role to anyone. Roles having CREATEROLE privilege can grant or revoke membership in any role that is not a superuser.

If GRANTED BY is specified, the grant is recorded as having been done by the specified role. Only database superusers may use this option, except when it names the same role executing the command.

Unlike the case with privileges, membership in a role cannot be granted to PUBLIC. Note also that this form of the command does not allow the noise word GROUP in role_specification.

## NOTES
The REVOKE command is used to revoke access privileges.

Since PostgreSQL 8.1, the concepts of users and groups have been unified into a single kind of entity called a role. It is therefore no longer necessary to use the keyword GROUP to identify whether a grantee is a user or a group. GROUP is still allowed in the command, but it is a noise word.

A user may perform SELECT, INSERT, etc. on a column if they hold that privilege for either the specific column or its whole table. Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.

When a non-owner of an object attempts to GRANT privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as some privilege is available, the command will proceed, but it will grant only those privileges for which the user has grant options. The GRANT ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)

It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary.

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner. (For role membership, the membership appears to have been granted by the containing role itself.)

GRANT and REVOKE can also be done by a role that is not the owner of the affected object, but is a member of the role that owns the object, or is a member of a role that holds privileges WITH GRANT OPTION on the object. In this case the privileges will be recorded as having been granted by the role that actually owns the object or holds the privileges WITH GRANT OPTION. For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can grant privileges on t1 to u2, but those privileges will appear to have been granted directly by g1. Any other member of role g1 could revoke them later.

If the role executing GRANT holds the required privileges indirectly via more than one role membership path, it is unspecified which containing role will be recorded as having done the grant. In such cases it is best practice to use SET ROLE to become the specific role you want to do the GRANT as.

Granting permission on a table does not automatically extend permissions to any sequences used by the table, including sequences tied to SERIAL columns. Permissions on sequences must be set separately.

See Section 5.7 for more information about specific privilege types, as well as how to inspect objects' privileges.

## EXAMPLES
Grant insert privilege to all users on table films:

```
GRANT INSERT ON films TO PUBLIC;
```

Grant all available privileges to user manuel on view kinds:

```
GRANT ALL PRIVILEGES ON kinds TO manuel;
```

Note that while the above will indeed grant all privileges if executed by a superuser or the owner of kinds, when executed by someone else it will only grant those permissions for which the someone else has grant options.

Grant membership in role admins to user joe:

```
GRANT admins TO joe;
```

## COMPATIBILITY
According to the SQL standard, the PRIVILEGES key word in ALL PRIVILEGES is required. The SQL standard does not support setting the privileges on more than one object per command.

PostgreSQL allows an object owner to revoke their own ordinary privileges: for example, a table owner can make the table read-only to themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE privileges. This is not possible according to the SQL standard. The reason is that PostgreSQL treats the owner's privileges as having been granted by the owner to themselves; therefore they can revoke them too. In the SQL standard, the owner's privileges are granted by an assumed entity “_SYSTEM”. Not being “_SYSTEM”, the owner cannot revoke these rights.

According to the SQL standard, grant options can be granted to PUBLIC; PostgreSQL only supports granting grant options to roles.

The SQL standard allows the GRANTED BY option to specify only CURRENT_USER or CURRENT_ROLE. The other variants are PostgreSQL extensions.

The SQL standard provides for a USAGE privilege on other kinds of objects: character sets, collations, translations.

In the SQL standard, sequences only have a USAGE privilege, which controls the use of the NEXT VALUE FOR expression, which is equivalent to the function nextval in PostgreSQL. The sequence privileges SELECT and UPDATE are PostgreSQL extensions. The application of the sequence USAGE privilege to the currval function is also a PostgreSQL extension (as is the function itself).

Privileges on databases, tablespaces, schemas, languages, and configuration parameters are PostgreSQL extensions.

## SEE ALSO
REVOKE(7), ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))


title: IMPORT_FOREIGN_SCHEMA(7)

text:
# IMPORT_FOREIGN_SCHEMA(7)

## NAME
IMPORT_FOREIGN_SCHEMA - import table definitions from a foreign server

## SYNOPSIS

```
IMPORT FOREIGN SCHEMA remote_schema

    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]

    FROM SERVER server_name

    INTO local_schema

    [ OPTIONS ( option 'value' [, ... ] ) ]
```

## DESCRIPTION
IMPORT FOREIGN SCHEMA creates foreign tables that represent tables existing on a foreign server. The new foreign tables will be owned by the user issuing the command and are created with the correct column definitions and options to match the remote tables.

By default, all tables and views existing in a particular schema on the foreign server are imported. Optionally, the list of tables can be limited to a specified subset, or specific tables can be excluded. The new foreign tables are all created in the target schema, which must already exist.

To use IMPORT FOREIGN SCHEMA, the user must have USAGE privilege on the foreign server, as well as CREATE privilege on the target schema.

## PARAMETERS
remote_schema

The remote schema to import from. The specific meaning of a remote schema depends on the foreign data wrapper in use.

LIMIT TO ( table_name [, ...] )

Import only foreign tables matching one of the given table names. Other tables existing in the foreign schema will be ignored.

EXCEPT ( table_name [, ...] )

Exclude specified foreign tables from the import. All tables existing in the foreign schema will be imported except the ones listed here.

server_name

The foreign server to import from.

local_schema

The schema in which the imported foreign tables will be created.

OPTIONS ( option 'value' [, ...] )

Options to be used during the import. The allowed option names and values are specific to each foreign data wrapper.

## EXAMPLES
Import table definitions from a remote schema foreign_films on server film_server, creating the foreign tables in local schema films:

```
IMPORT FOREIGN SCHEMA foreign_films

    FROM SERVER film_server INTO films;
```

As above, but import only the two tables actors and directors (if they exist):

```
IMPORT FOREIGN SCHEMA foreign_films LIMIT TO (actors, directors)

    FROM SERVER film_server INTO films;
```

## COMPATIBILITY
The IMPORT FOREIGN SCHEMA command conforms to the SQL standard, except that the OPTIONS clause is a PostgreSQL extension.

## SEE ALSO
CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)), CREATE SERVER (CREATE_SERVER(7))