DELETE

title: DELETE(7)

summary: delete rows of a table

text:
# DELETE(7)

## NAME
DELETE - delete rows of a table

## SYNOPSIS

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

    [ USING from_item [, ...] ]

    [ WHERE condition | WHERE CURRENT OF cursor_name ]

    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]

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

## DESCRIPTION
DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.

Tip

TRUNCATE provides a faster mechanism to remove all rows from a table.

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

The optional RETURNING clause causes DELETE to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in USING, can be computed. The syntax of the RETURNING list is identical to that of the output list of SELECT.

You must have the DELETE privilege on the table to delete from it, as well as the SELECT privilege for any table in the USING clause or whose values are read in the condition.

## PARAMETERS
with_query

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

table_name

The name (optionally schema-qualified) of the table to delete rows from. If ONLY is specified before the table name, matching rows are deleted from the named table only. If ONLY is not specified, matching rows are also deleted from 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 DELETE FROM foo AS f, the remainder of the DELETE statement must refer to this table as f not foo.

from_item

A table expression allowing columns from other tables to appear in the WHERE condition. 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 wish to set up 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 deleted.

cursor_name

The name of the cursor to use in a WHERE CURRENT OF condition. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the DELETE'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_alias

An optional substitute name for OLD or NEW rows in the RETURNING list. By default, old values from the target table can be returned by writing OLD.column_name or OLD.*, and new values can be returned by writing NEW.column_name or NEW.*. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.

output_expression

An expression to be computed and returned by the DELETE command after each row is deleted. The expression can use any column names of the table named by table_name or table(s) listed in USING. Write * to return all columns. A column name or * may be qualified using OLD or NEW, or the corresponding output_alias for OLD or NEW, to cause old or new values to be returned. An unqualified column name, or *, or a column name or * qualified using the target table name or alias will return old values.

For a simple DELETE, all new values will be NULL. However, if an ON DELETE rule causes an INSERT or UPDATE to be executed instead, the new values may be non-NULL.

output_name

A name to use for a returned column.

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

```
DELETE count
```

The count is the number of rows deleted. Note that the number may be less than the number of rows that matched the condition when deletes were suppressed by a BEFORE DELETE trigger. If count is 0, no rows were deleted by the query (this is not considered an error).

If the DELETE 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) deleted by the command.

## NOTES
PostgreSQL lets you reference columns of other tables in the WHERE condition by specifying the other tables in the USING clause. For example, to delete all films produced by a given producer, one can do:

```
DELETE FROM films USING producers

  WHERE producer_id = producers.id AND producers.name = 'foo';
```

What is essentially happening here is a join between films and producers, with all successfully joined films rows being marked for deletion. This syntax is not standard. A more standard way to do it is:

```
DELETE FROM films

  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
```

In some cases the join style is easier to write or faster to execute than the sub-select style.

## EXAMPLES
Delete all films but musicals:

```
DELETE FROM films WHERE kind <> 'Musical';
```

Clear the table films:

```
DELETE FROM films;
```

Delete completed tasks, returning full details of the deleted rows:

```
DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
```

Delete the row of tasks on which the cursor c_tasks is currently positioned:

```
DELETE FROM tasks WHERE CURRENT OF c_tasks;
```

While there is no LIMIT clause for DELETE, it is possible to get a similar effect using the same method described in the documentation of UPDATE:

```
WITH delete_batch AS (

  SELECT l.ctid FROM user_logs AS l

    WHERE l.status = 'archived'

    ORDER BY l.creation_date

    FOR UPDATE

    LIMIT 10000
)
DELETE FROM user_logs AS dl

  USING delete_batch AS del

  WHERE dl.ctid = del.ctid;
```

This use of ctid is only safe because the query is repeatedly run, avoiding the problem of changed ctids.

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

## SEE ALSO
TRUNCATE(7)


DISCARD

title: DISCARD(7)

summary: discard session state

text:
# DISCARD(7)

## NAME
DISCARD - discard session state

## SYNOPSIS

```
DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
```

## DESCRIPTION
DISCARD releases internal resources associated with a database session. This command is useful for partially or fully resetting the session's state. There are several subcommands to release different types of resources; the DISCARD ALL variant subsumes all the others, and also resets additional state.

## PARAMETERS
PLANS

Releases all cached query plans, forcing re-planning to occur the next time the associated prepared statement is used.

SEQUENCES

Discards all cached sequence-related state, including currval()/lastval() information and any preallocated sequence values that have not yet been returned by nextval(). (See CREATE SEQUENCE (CREATE_SEQUENCE(7)) for a description of preallocated sequence values.)

TEMPORARY or TEMP

Drops all temporary tables created in the current session.

ALL

Releases all temporary resources associated with the current session and resets the session to its initial state. Currently, this has the same effect as executing the following sequence of statements:

```
CLOSE ALL;
SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;
DISCARD SEQUENCES;
```

## NOTES
DISCARD ALL cannot be executed inside a transaction block.

## COMPATIBILITY
DISCARD is a PostgreSQL extension.

branch: sisyphus


DO

title: DO(7)

summary: execute an anonymous code block

text:
# DO(7)

## NAME
DO - execute an anonymous code block

## SYNOPSIS

```
DO [ LANGUAGE lang_name ] code
```

## DESCRIPTION
DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

The optional LANGUAGE clause can be written either before or after the code block.

## PARAMETERS
code

The procedural language code to be executed. This must be specified as a string literal, just as in CREATE FUNCTION. Use of a dollar-quoted literal is recommended.

lang_name

The name of the procedural language the code is written in. If omitted, the default is plpgsql.

## NOTES
The procedural language to be used must already have been installed into the current database by means of CREATE EXTENSION. plpgsql is installed by default, but other languages are not.

The user must have USAGE privilege for the procedural language, or must be a superuser if the language is untrusted. This is the same privilege requirement as for creating a function in the language.

If DO is executed in a transaction block, then the procedure code cannot execute transaction control statements. Transaction control statements are only allowed if DO is executed in its own transaction.

## EXAMPLES
Grant all privileges on all views in schema public to role webuser:

```
DO $$DECLARE r record;
BEGIN

    FOR r IN SELECT table_schema, table_name FROM information_schema.tables

             WHERE table_type = 'VIEW' AND table_schema = 'public'

    LOOP

        EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';

    END LOOP;
END$$;
```

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

## SEE ALSO
CREATE LANGUAGE (CREATE_LANGUAGE(7))


title: DROP_ACCESS_METHOD(7)

summary: remove an access method

text:
# DROP_ACCESS_METHOD(7)

## NAME
DROP_ACCESS_METHOD - remove an access method

## SYNOPSIS

```
DROP ACCESS METHOD [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP ACCESS METHOD removes an existing access method. Only superusers can drop access methods.

## PARAMETERS
IF EXISTS

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

name

The name of an existing access method.

CASCADE

Automatically drop objects that depend on the access method (such as operator classes, operator families, and indexes), and in turn all objects that depend on those objects (see Section 5.15).

RESTRICT

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

## EXAMPLES
Drop the access method heptree:

```
DROP ACCESS METHOD heptree;
```

## COMPATIBILITY
DROP ACCESS METHOD is a PostgreSQL extension.

## SEE ALSO
CREATE ACCESS METHOD (CREATE_ACCESS_METHOD(7))


title: DROP_AGGREGATE(7)

summary: remove an aggregate function

text:
# DROP_AGGREGATE(7)

## NAME
DROP_AGGREGATE - remove an aggregate function

## SYNOPSIS

```
DROP AGGREGATE [ IF EXISTS ] name ( aggregate_signature ) [, ...] [ CASCADE | RESTRICT ]
where aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
```

## DESCRIPTION
DROP AGGREGATE removes an existing aggregate function. To execute this command the current user must be the owner of the aggregate function.

## PARAMETERS
IF EXISTS

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

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

CASCADE

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

RESTRICT

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

## NOTES
Alternative syntaxes for referencing ordered-set aggregates are described under ALTER AGGREGATE (ALTER_AGGREGATE(7)).

## EXAMPLES
To remove the aggregate function myavg for type integer:

```
DROP AGGREGATE myavg(integer);
```

To remove the hypothetical-set aggregate function myrank, which takes an arbitrary list of ordering columns and a matching list of direct arguments:

```
DROP AGGREGATE myrank(VARIADIC "any" ORDER BY VARIADIC "any");
```

To remove multiple aggregate functions in one command:

```
DROP AGGREGATE myavg(integer), myavg(bigint);
```

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

## SEE ALSO
ALTER AGGREGATE (ALTER_AGGREGATE(7)), CREATE AGGREGATE (CREATE_AGGREGATE(7))


title: DROP_CAST(7)

summary: remove a cast

text:
# DROP_CAST(7)

## NAME
DROP_CAST - remove a cast

## SYNOPSIS

```
DROP CAST [ IF EXISTS ] (source_type AS target_type) [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP CAST removes a previously defined cast.

To be able to drop a cast, you must own the source or the target data type. These are the same privileges that are required to create a cast.

## PARAMETERS
IF EXISTS

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

source_type

The name of the source data type of the cast.

target_type

The name of the target data type of the cast.

CASCADE

RESTRICT

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

## EXAMPLES
To drop the cast from type text to type int:

```
DROP CAST (text AS int);
```

## COMPATIBILITY
The DROP CAST command conforms to the SQL standard.

## SEE ALSO
CREATE CAST (CREATE_CAST(7))


title: DROP_COLLATION(7)

summary: remove a collation

text:
# DROP_COLLATION(7)

## NAME
DROP_COLLATION - remove a collation

## SYNOPSIS

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

## DESCRIPTION
DROP COLLATION removes a previously defined collation. To be able to drop a collation, you must own the collation.

## PARAMETERS
IF EXISTS

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

name

The name of the collation. The collation name can be schema-qualified.

CASCADE

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

RESTRICT

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

## EXAMPLES
To drop the collation named german:

```
DROP COLLATION german;
```

## COMPATIBILITY
The DROP COLLATION command conforms to the SQL standard, apart from the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
ALTER COLLATION (ALTER_COLLATION(7)), CREATE COLLATION (CREATE_COLLATION(7))


title: DROP_CONVERSION(7)

summary: remove a conversion

text:
# DROP_CONVERSION(7)

## NAME
DROP_CONVERSION - remove a conversion

## SYNOPSIS

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

## DESCRIPTION
DROP CONVERSION removes a previously defined conversion. To be able to drop a conversion, you must own the conversion.

## PARAMETERS
IF EXISTS

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

name

The name of the conversion. The conversion name can be schema-qualified.

CASCADE

RESTRICT

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

## EXAMPLES
To drop the conversion named myname:

```
DROP CONVERSION myname;
```

## COMPATIBILITY
There is no DROP CONVERSION statement in the SQL standard, but a DROP TRANSLATION statement that goes along with the CREATE TRANSLATION statement that is similar to the CREATE CONVERSION statement in PostgreSQL.

## SEE ALSO
ALTER CONVERSION (ALTER_CONVERSION(7)), CREATE CONVERSION (CREATE_CONVERSION(7))


title: DROP_DATABASE(7)

summary: remove a database

text:
# DROP_DATABASE(7)

## NAME
DROP_DATABASE - remove a database

## SYNOPSIS

```
DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]
where option can be:

    FORCE
```

## DESCRIPTION
DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. It cannot be executed while you are connected to the target database. (Connect to postgres or any other database to issue this command.) Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.

DROP DATABASE cannot be undone. Use it with care!

## PARAMETERS
IF EXISTS

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

name

The name of the database to remove.

FORCE

Attempt to terminate all existing connections to the target database. It doesn't terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database. This terminates background worker connections and connections that the current user has permission to terminate with pg_terminate_backend, described in Section 9.28.2. If connections would remain, this command will fail.

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

This command cannot be executed while connected to the target database. Thus, it might be more convenient to use the program dropdb(1) instead, which is a wrapper around this command.

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

## SEE ALSO
CREATE DATABASE (CREATE_DATABASE(7))


title: DROP_DOMAIN(7)

summary: remove a domain

text:
# DROP_DOMAIN(7)

## NAME
DROP_DOMAIN - remove a domain

## SYNOPSIS

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

## DESCRIPTION
DROP DOMAIN removes a domain. Only the owner of a domain can remove it.

## PARAMETERS
IF EXISTS

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

name

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

CASCADE

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

RESTRICT

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

## EXAMPLES
To remove the domain box:

```
DROP DOMAIN box;
```

## COMPATIBILITY
This command conforms to the SQL standard, except for the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
CREATE DOMAIN (CREATE_DOMAIN(7)), ALTER DOMAIN (ALTER_DOMAIN(7))


title: DROP_EVENT_TRIGGER(7)

summary: remove an event trigger

text:
# DROP_EVENT_TRIGGER(7)

## NAME
DROP_EVENT_TRIGGER - remove an event trigger

## SYNOPSIS

```
DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP EVENT TRIGGER removes an existing event trigger. To execute this command, the current user must be the owner of the event trigger.

## PARAMETERS
IF EXISTS

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

name

The name of the event trigger to remove.

CASCADE

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

RESTRICT

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

## EXAMPLES
Destroy the trigger snitch:

```
DROP EVENT TRIGGER snitch;
```

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

## SEE ALSO
CREATE EVENT TRIGGER (CREATE_EVENT_TRIGGER(7)), ALTER EVENT TRIGGER (ALTER_EVENT_TRIGGER(7))


title: DROP_EXTENSION(7)

summary: remove an extension

text:
# DROP_EXTENSION(7)

## NAME
DROP_EXTENSION - remove an extension

## SYNOPSIS

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

## DESCRIPTION
DROP EXTENSION removes extensions from the database. Dropping an extension causes its member objects, and other explicitly dependent routines (see ALTER ROUTINE (ALTER_ROUTINE(7)), the DEPENDS ON EXTENSION extension_name action), to be dropped as well.

You must own the extension to use DROP EXTENSION.

## PARAMETERS
IF EXISTS

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

name

The name of an installed extension.

CASCADE

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

RESTRICT

This option prevents the specified extensions from being dropped if other objects, besides these extensions, their members, and their explicitly dependent routines, depend on them. This is the default.

## EXAMPLES
To remove the extension hstore from the current database:

```
DROP EXTENSION hstore;
```

This command will fail if any of hstore's objects are in use in the database, for example if any tables have columns of the hstore type. Add the CASCADE option to forcibly remove those dependent objects as well.

## COMPATIBILITY
DROP EXTENSION is a PostgreSQL extension.

## SEE ALSO
CREATE EXTENSION (CREATE_EXTENSION(7)), ALTER EXTENSION (ALTER_EXTENSION(7))


title: DROP_FOREIGN_DATA_WRAPPER(7)

summary: remove a foreign\-data wrapper

text:
# DROP_FOREIGN_DATA_WRAPPER(7)

## NAME
DROP_FOREIGN_DATA_WRAPPER - remove a foreign-data wrapper

## SYNOPSIS

```
DROP FOREIGN DATA WRAPPER [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```

## DESCRIPTION
DROP FOREIGN DATA WRAPPER removes an existing foreign-data wrapper. To execute this command, the current user must be the owner of the foreign-data wrapper.

## PARAMETERS
IF EXISTS

Do not throw an error if the foreign-data wrapper does not exist. A notice is issued in this case.

name

The name of an existing foreign-data wrapper.

CASCADE

Automatically drop objects that depend on the foreign-data wrapper (such as foreign tables and servers), and in turn all objects that depend on those objects (see Section 5.15).

RESTRICT

Refuse to drop the foreign-data wrapper if any objects depend on it. This is the default.

## EXAMPLES
Drop the foreign-data wrapper dbi:

```
DROP FOREIGN DATA WRAPPER dbi;
```

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

## SEE ALSO
CREATE FOREIGN DATA WRAPPER (CREATE_FOREIGN_DATA_WRAPPER(7)), ALTER FOREIGN DATA WRAPPER (ALTER_FOREIGN_DATA_WRAPPER(7))


title: DROP_FOREIGN_TABLE(7)

summary: remove a foreign table

text:
# DROP_FOREIGN_TABLE(7)

## NAME
DROP_FOREIGN_TABLE - remove a foreign table

## SYNOPSIS

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

## DESCRIPTION
DROP FOREIGN TABLE removes a foreign table. Only the owner of a foreign table can remove it.

## PARAMETERS
IF EXISTS

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

name

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

CASCADE

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

RESTRICT

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

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

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

## COMPATIBILITY
This command conforms to ISO/IEC 9075-9 (SQL/MED), except that the standard only allows one foreign table to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

## SEE ALSO
ALTER FOREIGN TABLE (ALTER_FOREIGN_TABLE(7)), CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7))


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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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 21.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.15).

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 and have been granted ADMIN OPTION on the role.

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 21.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.15).

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.15).

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.15).

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.15).

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.

To execute this command the user must be the owner of the subscription.

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 29.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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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.15).

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
where option can be one of:

    ANALYZE [ boolean ]

    VERBOSE [ boolean ]

    COSTS [ boolean ]

    SETTINGS [ boolean ]

    GENERIC_PLAN [ boolean ]

    BUFFERS [ boolean ]

    SERIALIZE [ { NONE | TEXT | BINARY } ]

    WAL [ boolean ]

    TIMING [ boolean ]

    SUMMARY [ boolean ]

    MEMORY [ 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;
```

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

GENERIC_PLAN

Allow the statement to contain parameter placeholders like $1, and generate a generic plan that does not depend on the values of those parameters. See PREPARE for details about generic plans and the types of statement that support parameters. This parameter cannot be used together with ANALYZE. It 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, local 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. Buffers information is automatically included when ANALYZE is used.

SERIALIZE

Include information on the cost of serializing the query's output data, that is converting it to text or binary format to send to the client. This can be a significant part of the time required for regular execution of the query, if the datatype output functions are expensive or if TOASTed values must be fetched from out-of-line storage. EXPLAIN's default behavior, SERIALIZE NONE, does not perform these conversions. If SERIALIZE TEXT or SERIALIZE BINARY is specified, the appropriate conversions are performed, and the time spent doing so is measured (unless TIMING OFF is specified). If the BUFFERS option is also specified, then any buffer accesses involved in the conversions are counted too. In no case, however, will EXPLAIN actually send the resulting data to the client; hence network transmission costs cannot be investigated this way. Serialization may only be enabled when ANALYZE is also enabled. If SERIALIZE is written without an argument, TEXT is assumed.

WAL

Include information on WAL record generation. Specifically, include the number of records, number of full page images (fpi), the amount of WAL generated in bytes and the number of times the WAL buffers became full. 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.

MEMORY

Include information on memory consumption by the query planning phase. Specifically, include the precise amount of storage used by planner in-memory structures, as well as total memory considering allocation overhead. This parameter defaults to FALSE.

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=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)

   Group Key: foo

   Batches: 1  Memory Usage: 24kB

   Buffers: shared hit=4

   ->  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)

         Index Cond: ((id > 100) AND (id < 200))

         Index Searches: 1

         Buffers: shared hit=4

 Planning Time: 0.244 ms

 Execution Time: 0.073 ms
(10 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.

Notice that the previous example showed a “custom” plan for the specific parameter values given in EXECUTE. We might also wish to see the generic plan for a parameterized query, which can be done with GENERIC_PLAN:

```
EXPLAIN (GENERIC_PLAN)

  SELECT sum(bar) FROM test

    WHERE id > $1 AND id < $2

    GROUP BY foo;

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

 HashAggregate  (cost=26.79..26.89 rows=10 width=12)

   Group Key: foo

   ->  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)

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

In this case the parser correctly inferred that $1 and $2 should have the same data type as id, so the lack of parameter type information from PREPARE was not a problem. In other cases it might be necessary to explicitly specify types for the parameter symbols, which can be done by casting them, for example:

```
EXPLAIN (GENERIC_PLAN)

  SELECT sum(bar) FROM test

    WHERE id > $1::integer AND id < $2::integer

    GROUP BY foo;
```

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

The following syntax was used before PostgreSQL version 9.0 and is still supported:

```
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
```

Note that in this syntax, the options must be specified in exactly the order shown.

## 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 41.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)