When you create a table and you realize that you made a mistake, or the requirements of the application change, you can drop the table and create it again. But this is not a convenient option if the table is already filled with data, or if the table is referenced by other database objects (for instance a foreign key constraint). Therefore PostgreSQL provides a family of commands to make modifications to existing tables. Note that this is conceptually distinct from altering the data contained in

Namereindexdb -- reindex a PostgreSQL database Synopsis reindexdb [connection-option...] [option...] [ --schema | -S schema ] ... [ --table | -t table ] ... [ --index | -i index ] ... [dbname] reindexdb [connection-option...] [option...] --all | -a reindexdb [connection-option...] [option...] --system | -s [dbname] Description reindexdb is a utility for rebuilding indexes in a PostgreSQL database. reindexdb is a wrapper around the SQL command REINDEX. There is no effective difference be

NameREFRESH MATERIALIZED VIEW -- replace the contents of a materialized view Synopsis REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ] Description REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded. If WITH DATA is specified (or defaults) the backing query is executed to provide the new data, and the materialized view is left in a scannable state. If WITH NO DATA is specified no new data is generate

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text. F.8.1. Rationale The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example SELECT * FROM tab WHERE lower(col) = LOWER(?); This works reasonably well, but has a number of drawbacks: It makes your SQL statements verbose, and you

NameCREATE AGGREGATE -- define a new aggregate function Synopsis CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , SSPACE = state_data_size ] [ , FINALFUNC = ffunc ] [ , FINALFUNC_EXTRA ] [ , COMBINEFUNC = combinefunc ] [ , SERIALFUNC = serialfunc ] [ , DESERIALFUNC = deserialfunc ] [ , INITCOND = initial_condition ] [ , MSFUNC = msfunc ] [ , MINVFUNC = minvfunc ] [ , MSTYP

Namepg_recvlogical -- control PostgreSQL logical decoding streams Synopsis pg_recvlogical [option...] Description pg_recvlogical controls logical decoding replication slots and streams data from such replication slots. It creates a replication-mode connection, so it is subject to the same constraints as pg_receivexlog, plus those for logical replication (see Chapter 47). Options At least one of the following options must be specified to select an action: --create-slot Create a new

Namecreatelang -- install a PostgreSQL procedural language Synopsis createlang [connection-option...] langname [dbname] createlang [connection-option...] --list | -l [dbname] Description createlang is a utility for adding a procedural language to a PostgreSQL database. createlang is just a wrapper around the CREATE EXTENSION SQL command. Caution: createlang is deprecated and may be removed in a future PostgreSQL release. Direct use of the CREATE EXTENSION command is recommended instead

PostgreSQL allows functions that have named parameters to be called using either positional or named notation. Named notation is especially useful for functions that have a large number of parameters, since it makes the associations between parameters and actual arguments more explicit and reliable. In positional notation, a function call is written with its argument values in the same order as they are defined in the function declaration. In named notation, the arguments are matched to the fun

The task of the planner/optimizer is to create an optimal execution plan. A given SQL query (and hence, a query tree) can be actually executed in a wide variety of different ways, each of which will produce the same set of results. If it is computationally feasible, the query optimizer will examine each of these possible execution plans, ultimately selecting the execution plan that is expected to run the fastest. Note: In some situations, examining each possible way in which a query can be exe

PostgreSQL enforces SQL uniqueness constraints using unique indexes, which are indexes that disallow multiple entries with identical keys. An access method that supports this feature sets amcanunique true. (At present, only b-tree supports it.) Because of MVCC, it is always necessary to allow duplicate entries to exist physically in an index: the entries might refer to successive versions of a single logical row. The behavior we actually want to enforce is that no MVCC snapshot could include tw