NameALTER INDEX -- change the definition of an index Synopsis ALTER INDEX [ IF EXISTS ] name RENAME TO new_name ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name ALTER INDEX name DEPENDS ON EXTENSION extension_name ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ] Descript

NameTRUNCATE -- empty a table or set of tables Synopsis TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] Description TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on

All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the table's heap in PostgreSQL terminology). This means that in an ordinary index scan, each row retrieval requires fetching data from both the index and the heap. Furthermore, while the index entries that match a given indexable WHERE condition are usually close together in the index, the table rows they reference might be anywhere in the heap. The heap

WAL is automatically enabled; no action is required from the administrator except ensuring that the disk-space requirements for the WAL logs are met, and that any necessary tuning is done (see Section 30.4). WAL logs are stored in the directory pg_xlog under the data directory, as a set of segment files, normally each 16 MB in size (but the size can be changed by altering the --with-wal-segsize configure option when building the server). Each segment is divided into pages, normally 8 kB each (t

NameALTER DATABASE -- change a database Synopsis ALTER DATABASE name [ [ WITH ] option [ ... ] ] where option can be: ALLOW_CONNECTIONS allowconn CONNECTION LIMIT connlimit IS_TEMPLATE istemplate ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER DATABASE name SET TABLESPACE new_tablespace ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT } ALTER DATABASE name SET configuration_

NameLISTEN -- listen for a notification Synopsis LISTEN channel Description LISTEN registers the current session as a listener on the notification channel named channel. If the current session is already registered as a listener for this notification channel, nothing is done. Whenever the command NOTIFY channel is invoked, either by this session or another one connected to the same database, all the sessions currently listening on that notification channel are notified, and each will

NameCREATE VIEW -- define a new view Synopsis CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ] [ WITH ( view_option_name [= view_option_value] [, ... ] ) ] AS query [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] Description CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. CREATE OR REPLACE VIEW is similar, but if a view of the sam

Namepg_config -- retrieve information about the installed version of PostgreSQL Synopsis pg_config [option...] Description The pg_config utility prints configuration parameters of the currently installed version of PostgreSQL. It is intended, for example, to be used by software packages that want to interface to PostgreSQL to facilitate finding the required header files and libraries. Options To use pg_config, supply one or more of the following options: --bindir Print the location

Index access methods must handle concurrent updates of the index by multiple processes. The core PostgreSQL system obtains AccessShareLock on the index during an index scan, and RowExclusiveLock when updating the index (including plain VACUUM). Since these lock types do not conflict, the access method is responsible for handling any fine-grained locking it might need. An exclusive lock on the index as a whole will be taken only during index creation, destruction, or REINDEX. Building an index t

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time (see Chapter 16). With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both normal and SSL connections on the same TCP port, and will ne