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

NameVALUES -- compute a set of rows Synopsis VALUES ( expression [, ...] ) [, ...] [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] Description VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a "constant table" within a larger command, but it can be used on its own. W

Each table has a primary heap disk file where most of the data is stored. If the table has any columns with potentially-wide values, there also might be a TOAST file associated with the table, which is used to store values too wide to fit comfortably in the main table (see Section 65.2). There will be one valid index on the TOAST table, if present. There also might be indexes associated with the base table. Each table and index is stored in a separate disk file — possibly more than one file, if

PostgreSQL provides various lock modes to control concurrent access to data in tables. These modes can be used for application-controlled locking in situations where MVCC does not give the desired behavior. Also, most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes. (For example, TRUNCATE cannot safely be executed concurrently with other operations on the same table,

The catalog pg_amop stores information about operators associated with access method operator families. There is one row for each operator that is a member of an operator family. A family member can be either a search operator or an ordering operator. An operator can appear in more than one family, but cannot appear in more than one search position nor more than one ordering position within a family. (It is allowed, though unlikely, for an operator to be used for both search and ordering purpos

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

A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type. 8.16.1. Declaration of Composite Types Here are two simple examples of defining composite types: CREATE TYPE complex AS ( r double precision, i double precision ); CREATE

NameCLUSTER -- cluster a table according to an index Synopsis CLUSTER [VERBOSE] table_name [ USING index_name ] CLUSTER [VERBOSE] Description CLUSTER instructs PostgreSQL to cluster the table specified by table_name based on the index specified by index_name. The index must already have been defined on table_name. When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes

This is a list of acronyms commonly used in the PostgreSQL documentation and in discussions about PostgreSQL. ANSI American National Standards Institute API Application Programming Interface ASCII American Standard Code for Information Interchange BKI Backend Interface CA Certificate Authority CIDR Classless Inter-Domain Routing CPAN Comprehensive Perl Archive Network CRL Certificate Revocation List CSV Comma Separated Values CTE Common Table Expression CVE Common Vulnerab

The functions and function-like expressions described in this section operate on values of type xml. Check Section 8.13 for information about the xml type. The function-like expressions xmlparse and xmlserialize for converting to and from type xml are not repeated here. Use of most of these functions requires the installation to have been built with configure --with-libxml. 9.14.1. Producing XML Content A set of functions and function-like expressions are available for producing XML content fr