<entry><type>int2</type></entry>

<entry></entry>

<entry>The total number of columns in the index (duplicates

<literal>pg_class.relnatts</literal>). This number includes both key and included attributes.</entry>

<literal>pg_class.relnatts</literal>); this number includes both key and included attributes</entry>

</row>

<row>

<entry><structfield>indnkeyatts</structfield></entry>

<entry><type>int2</type></entry>

<entry></entry>

<entry>The number of key columns in the index. "Key columns" are ordinary

index columns (as opposed to "included" columns).</entry>

<entry>The number of <firstterm>key columns</firstterm> in the index,

not counting any <firstterm>included columns</firstterm>, which are

merely stored and do not participate in the index semantics</entry>

</row>

<row>

This is an array of <structfield>indnatts</structfield> values that

indicate which table columns this index indexes. For example a value

of <literal>1 3</literal> would mean that the first and the third table

columns make up the index key. A zero in this array indicates that the

columns make up the index entries. Key columns come before non-key

(included) columns. A zero in this array indicates that the

corresponding index attribute is an expression over the table columns,

rather than a simple column reference.

</entry>

<entry><type>oidvector</type></entry>

<entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry>

<entry>

For each column in the index key, this contains the OID of the

collation to use for the index, or zero if the column is not

of a collatable data type.

For each column in the index key

(<structfield>indnkeyatts</structfield> values), this contains the OID

of the collation to use for the index, or zero if the column is not of

a collatable data type.

</entry>

</row>

<entry><type>oidvector</type></entry>

<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>

<entry>

For each column in the index key, this contains the OID of

the operator class to use. See

For each column in the index key

(<structfield>indnkeyatts</structfield> values), this contains the OID

of the operator class to use. See

<link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link> for details.

</entry>

</row>

<entry><type>int2vector</type></entry>

<entry></entry>

<entry>

This is an array of <structfield>indnatts</structfield> values that

This is an array of <structfield>indnkeyatts</structfield> values that

store per-column flag bits. The meaning of the bits is defined by

the index's access method.

</entry>

bool ampredlocks;

/* does AM support parallel scan? */

bool amcanparallel;

/* type of data stored in index, or InvalidOid if variable */

Oid amkeytype;

/* does AM support columns included with clause INCLUDE? */

bool amcaninclude;

/* type of data stored in index, or InvalidOid if variable */

Oid amkeytype;

/* interface functions */

ambuild_function ambuild;

using <firstterm>unique indexes</firstterm>, which are indexes that disallow

multiple entries with identical keys. An access method that supports this

feature sets <structfield>amcanunique</structfield> true.

(At present, only b-tree supports it.) Columns listed in the

<literal>INCLUDE</literal> clause are not used to enforce uniqueness.

(At present, only b-tree supports it.) Columns listed in the

<literal>INCLUDE</literal> clause are not considered when enforcing

uniqueness.

</para>

<para>

or the uniqueness of the combined values of more than one column.

<synopsis>

CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>)

[ INCLUDE (<replaceable>column</replaceable> <optional>, ...</optional>)];

<optional> INCLUDE (<replaceable>column</replaceable> <optional>, ...</optional>)</optional>;

</synopsis>

Currently, only B-tree indexes can be declared unique.

</para>

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. Columns listed in the

<literal>INCLUDE</literal> clausearen'tused to enforce constraints

(UNIQUE, PRIMARY KEY, etc).

indexed columns are equal. Columns listed in

the<literal>INCLUDE</literal> clause, if any,aren'tconsidered when

determining whether index entries are equal.

</para>

<para>

<listitem>

<para>

The optional <literal>INCLUDE</literal> clause specifies a

list of columns which will be included as a non-key part in the index.

Columns listed in this clause cannot also be present as index key columns.

The <literal>INCLUDE</literal> columns exist solely to

allow more queries to benefit from <firstterm>index-only scans</firstterm>

by including the values of the specified columns in the index. These values

would otherwise have to be obtained by reading the table's heap.

list of columns which will be included in the index

as <firstterm>non-key</firstterm> columns. A non-key column cannot

be used in an index scan search qualification, and it is disregarded

for purposes of any uniqueness or exclusion constraint enforced by

the index. However, an index-only scan can return the contents of

non-key columns without having to visit the index's table, since

they are available directly from the index entry. Thus, addition of

non-key columns allows index-only scans to be used for queries that

otherwise could not use them.

</para>

<para>

for keycolumns.Columns listed in the <literal>INCLUDE</literal>

thesame way.

index, especially widecolumns.If an index tuple exceeds the

and bloatthesize of the index, thus potentially slowing searches.

</para>

<para>

Columns listed in the <literal>INCLUDE</literal> clause don't need

appropriate operator classes; the clause cancontain non-key index

appropriate operator classes; the clause caninclude

columns whose data types don't have operator classes defined for

a given access method.

</para>

Currently, only the B-tree index access method supports this feature.

In B-tree indexes, the values of columns listed in the

<literal>INCLUDE</literal> clause are included in leaf tuples which

are linked to the heap tuples, but are not included into pivot tuples

used for tree navigation. Therefore, moving columns from the list of

key columns to the <literal>INCLUDE</literal> clause can slightly

reduce index size and improve the tree branching factor.

</para>

<para>

Indexes with columns listed in the <literal>INCLUDE</literal> clause

are also called <quote>covering indexes</quote>.

correspond to heap tuples, but are not included in upper-level

index entries used for tree navigation.

</para>

</listitem>

</varlistentry>

columns is updated and so the recheck is not worth the additional cost

of executing the function.

</para>

<para>

Functional indexes are used frequently for the case where the function

returns a subset of the argument. Examples of this would be accessing

<para>

To create a unique B-tree index on the column <literal>title</literal>

and included columns <literal>director</literal> and <literal>rating</literal>

in the table <literal>films</literal>:

with included columns <literal>director</literal>

and <literal>rating</literal>in the table <literal>films</literal>:

<programlisting>

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

</programlisting>

one or more columns on which the uniqueness is not enforced.

Note that although the constraint is not enforced on the included columns,

it still depends on them. Consequently, some operations on these columns

(e.g. <literal>DROP COLUMN</literal>) can cause cascade constraint and

index deletion. See paragraph about <literal>INCLUDE</literal> in

<xref linkend="sql-createindex"/> for more information.

(e.g. <literal>DROP COLUMN</literal>) can cause cascaded constraint and

index deletion.

</para>

</listitem>

</varlistentry>

of columns to be specified which will be included in the non-key portion

of the index. Although uniqueness is not enforced on the included columns,

the constraint still depends on them. Consequently, some operations on the

included columns (e.g. <literal>DROP COLUMN</literal>) can cause cascade

constraint and index deletion. See paragraph about <literal>INCLUDE</literal>

in <xref linkend="sql-createindex"/> for more information.

included columns (e.g. <literal>DROP COLUMN</literal>) can cause cascaded

constraint and index deletion.

</para>

</listitem>

</varlistentry>

Snapshotsnapshot;

inti;

if (list_intersection(stmt->indexParams,stmt->indexIncludingParams)!=NIL)

ereport(ERROR,

(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),

errmsg("included columns must not intersect with key columns")));

(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

errmsg("access method \"%s\" does not support unique indexes",

accessMethodName)));

if (list_length(stmt->indexIncludingParams)>0&& !amRoutine->amcaninclude)

if (stmt->indexIncludingParams!=NIL&& !amRoutine->amcaninclude)

ereport(ERROR,

(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

errmsg("access method \"%s\" does not support included columns",

-- Regular index with included columns

CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box);

INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;

CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c3,c4);

-- must fail because of intersection of key and included columns

CREATE INDEX tbl_include_reg_idx ON tbl_include_reg using btree (c1, c2) INCLUDE (c1,c3);

ERROR: included columns must not intersect with key columns

CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4);

-- duplicate column is pretty pointless, but we allow it anyway

CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3);

SELECT pg_get_indexdef(i.indexrelid)

FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid

WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname;

pg_get_indexdef

--------------------------------------------------------------------------------------------------

pg_get_indexdef

---------------------------------------------------------------------------------------------------------------

CREATE INDEX tbl_include_reg_c1_c2_c11_c3_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c1, c3)

CREATE INDEX tbl_include_reg_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c3, c4)

(1 row)

(2 rows)

-- Unique index and unique constraint

CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box);

CREATETABLEtbl_include_reg (c1int, c2int, c3int, c4box);

INSERT INTO tbl_include_regSELECT x,2*x,3*x,box('4,4,4,4')FROM generate_series(1,10)AS x;

CREATEINDEXtbl_include_reg_idxON tbl_include_regusing btree(c1, c2) INCLUDE (c3,c4);

CREATEINDEXtbl_include_reg_idxON tbl_include_regusing btree(c1, c2) INCLUDE (c1,c3);

CREATEINDEXtbl_include_reg_idxON tbl_include_reg (c1, c2) INCLUDE (c3,c4);

CREATEINDEXON tbl_include_reg (c1, c2) INCLUDE (c1,c3);

SELECT pg_get_indexdef(i.indexrelid)

FROM pg_index iJOIN pg_class cONi.indexrelid=c.oid

WHEREi.indrelid='tbl_include_reg'::regclassORDER BYc.relname;