The view pg_indexes provides access to useful information about each index in the database. Table 50-65. pg_indexes Columns Name Type References Description schemaname name pg_namespace.nspname Name of schema containing table and index tablename name pg_class.relname Name of table the index is for indexname name pg_class.relname Name of index tablespace name pg_tablespace.spcname Name of tablespace containing index (null if default for database) indexdef text Index definition (a reconstruct

All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard's conventions for "SQLSTATE" codes. Applications that need to know which error condition has occurred should usually test the error code, rather than looking at the textual error message. The error codes are less likely to change across PostgreSQL releases, and also are not subject to change due to localization of error messages. Note that some, but not all, of the error codes prod

In order to create a database, the PostgreSQL server must be up and running (see Section 18.3). Databases are created with the SQL command CREATE DATABASE: CREATE DATABASE name; where name follows the usual rules for SQL identifiers. The current role automatically becomes the owner of the new database. It is the privilege of the owner of a database to remove it later (which also removes all the objects in it, even if they have a different owner). The creation of databases is a restricted oper

Suppose we have a table similar to this: CREATE TABLE test1 ( id integer, content varchar ); and the application issues many queries of the form: SELECT content FROM test1 WHERE id = constant; With no advance preparation, the system would have to scan the entire test1 table, row by row, to find all matching entries. If there are many rows in test1 and only a few rows (perhaps zero or one) that would be returned by such a query, this is clearly an inefficient method. But if the syste

NameALTER AGGREGATE -- change the definition of an aggregate function Synopsis ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name ALTER AGGREGATE name ( aggregate_signature ) OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema where aggregate_signature is: * | [ argmode ] [ argname ] argtype [ , ... ] | [ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [

The pg_prepared_statements view displays all the prepared statements that are available in the current session. See PREPARE for more information about prepared statements. pg_prepared_statements contains one row for each prepared statement. Rows are added to the view when a new prepared statement is created and removed when a prepared statement is released (for example, via the DEALLOCATE command). Table 50-69. pg_prepared_statements Columns Name Type Description name text The identifier of t

NameALTER MATERIALIZED VIEW -- change the definition of a materialized view Synopsis ALTER MATERIALIZED VIEW [ IF EXISTS ] name action [, ... ] ALTER MATERIALIZED VIEW name DEPENDS ON EXTENSION extension_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name RENAME TO new_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name SET SCHEMA new_schema ALTER MATERIALIZED VIEW ALL IN TABLESPACE nam

NameALTER CONVERSION -- change the definition of a conversion Synopsis ALTER CONVERSION name RENAME TO new_name ALTER CONVERSION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER CONVERSION name SET SCHEMA new_schema Description ALTER CONVERSION changes the definition of a conversion. You must own the conversion to use ALTER CONVERSION. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the

NameDROP 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 Prev Next DROP FUNCTION Up DROP INDEX

For enum types (described in Section 8.7), there are several functions that allow cleaner programming without hard-coding particular values of an enum type. These are listed in Table 9-32. The examples assume an enum type created as: CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); Table 9-32. Enum Support Functions Function Description Example Example Result enum_first(anyenum) Returns the first value of the input enum type enum_first(null::rainbow) red e