Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column. CREATE UNIQUE INDEX name ON table (column [, ...]); Currently, only B-tree indexes can be declared unique. When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows. PostgreSQL automa

The modification of data that is already in the database is referred to as updating. You can update individual rows, all the rows in a table, or a subset of all rows. Each column can be updated separately; the other columns are not affected. To update existing rows, use the UPDATE command. This requires three pieces of information: The name of the table and column to update The new value of the column Which row(s) to update Recall from Chapter 5 that SQL does not, in general, provide a

unaccent is a text search dictionary that removes accents (diacritic signs) from lexemes. It's a filtering dictionary, which means its output is always passed to the next dictionary (if any), unlike the normal behavior of dictionaries. This allows accent-insensitive processing for full text search. The current implementation of unaccent cannot be used as a normalizing dictionary for the thesaurus dictionary. F.44.1. Configuration An unaccent dictionary accepts the following options: RULES is

Values to be inserted into a table are converted to the destination column's data type according to the following steps. Value Storage Type Conversion Check for an exact match with the target. Otherwise, try to convert the expression to the target type. This is possible if an assignment cast between the two types is registered in the pg_cast catalog (see CREATE CAST). Alternatively, if the expression is an unknown-type literal, the contents of the literal string will be fed to the input con

The specific operator that is referenced by an operator expression is determined using the following procedure. Note that this procedure is indirectly affected by the precedence of the operators involved, since that will determine which sub-expressions are taken to be the inputs of which operators. See Section 4.1.6 for more information. Operator Type Resolution Select the operators to be considered from the pg_operator system catalog. If a non-schema-qualified operator name was used (the us

The specific function that is referenced by a function call is determined using the following procedure. Function Type Resolution Select the functions to be considered from the pg_proc system catalog. If a non-schema-qualified function name was used, the functions considered are those with the matching name and argument count that are visible in the current search path (see Section 5.8.3). If a qualified function name was given, only functions in the specified schema are considered. If the s

SQL UNION constructs must match up possibly dissimilar types to become a single result set. The resolution algorithm is applied separately to each output column of a union query. The INTERSECT and EXCEPT constructs resolve dissimilar types in the same way as UNION. The CASE, ARRAY, VALUES, GREATEST and LEAST constructs use the identical algorithm to match up their component expressions and select a result data type. Type Resolution for UNION, CASE, and Related Constructs If all inputs are of

SQL is a strongly typed language. That is, every data item has an associated data type which determines its behavior and allowed usage. PostgreSQL has an extensible type system that is more general and flexible than other SQL implementations. Hence, most type conversion behavior in PostgreSQL is governed by general rules rather than by ad hoc heuristics. This allows the use of mixed-type expressions even with user-defined types. The PostgreSQL scanner/parser divides lexical elements into five f

The tsm_system_time module provides the table sampling method SYSTEM_TIME, which can be used in the TABLESAMPLE clause of a SELECT command. This table sampling method accepts a single floating-point argument that is the maximum number of milliseconds to spend reading the table. This gives you direct control over how long the query takes, at the price that the size of the sample becomes hard to predict. The resulting sample will contain as many rows as could be read in the specified time, unless

The tsm_system_rows module provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command. This table sampling method accepts a single integer argument that is the maximum number of rows to read. The resulting sample will always contain exactly that many rows, unless the table does not contain enough rows, in which case the whole table is selected. Like the built-in SYSTEM sampling method, SYSTEM_ROWS performs block-level sampling, so that the sa