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

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

Columns included with clause INCLUDING aren't used to enforce uniqueness.

An access method that supports this feature sets <structname>pg_am</>.<structfield>amcanincluding</> true.

(At present, only b-tree supports them.)

</para>

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 included with clause

INCLUDING aren't used to enforce constraints (UNIQUE, PRIMARY KEY, etc).

<literal>INCLUDING</literal> aren't used to enforce constraints (UNIQUE, PRIMARY KEY, etc).

</para>

<para>

<term><literal>INCLUDING</literal></term>

<listitem>

<para>

This clause specifies additional columns to be appended to the set of index columns.

Included columns don't support any constraints <literal>(UNIQUE, PRMARY KEY, EXCLUSION CONSTRAINT)</>.

These columns can improve the performance of some queries through using advantages of index-only scan

(Or so called <firstterm>covering</firstterm> indexes. Covering index is the index that

covers all columns required in the query and prevents a table access).

Besides that, included attributes are not stored in index inner pages.

It allows to decrease index size and furthermore it provides a way to extend included

columns to store atttributes without suitable opclass (not implemented yet).

This clause could be applied to both unique and nonunique indexes.

It's possible to have non-unique covering index, which behaves as a regular

multi-column index with a bit smaller index-size.

An optional <literal>INCLUDING</> clause allows a list of columns to be

specified which will be included in the index, in the non-key portion of

the index. Columns which are part of this clause cannot also exist in the

key columns portion of the index, and vice versa. The

<literal>INCLUDING</> columns exist solely to allow more queries to benefit

from <firstterm>index-only scans</> by including certain columns in the

index, the value of which would otherwise have to be obtained by reading

the table's heap. Having these columns in the <literal>INCLUDING</> clause

in some cases allows <productname>PostgreSQL</> to skip the heap read

completely. This also allows <literal>UNIQUE</> indexes to be defined on

one set of columns, which can include another set of column in the

<literal>INCLUDING</> clause, on which the uniqueness is not enforced upon.

It's the same with other constraints (PRIMARY KEY and EXCLUDE). This can

also can be used for non-unique indexes as any columns which are not required

for the searching or ordering of records can defined in the

<literal>INCLUDING</> clause, which can slightly reduce the size of the index,

due to storing included attributes only in leaf index pages.

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

</para>

</listitem>

<title>Examples</title>

<para>

To createan unique B-tree index on the column <literal>title</literal> in

To createa unique B-tree index on the column <literal>title</literal> in

the table <literal>films</literal>:

<programlisting>

CREATE UNIQUE INDEX title_idx ON films (title);

</programlisting>

</para>

<para>

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

To createa 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>:

<programlisting>

}

index_reform_tuple(Relationidxrel,IndexTupleolditup,intnatts,intnkeyatts)

_bt_doinsert(Relationrel,IndexTupleitup,

IndexUniqueCheckcheckUnique,RelationheapRel)

{

boolis_unique= false;

intnkeyatts=rel->rd_rel->relnatts;

boolis_unique;

intnkeyatts;

ScanKeyitup_scankey;

BTStackstack;

Bufferbuf;

OffsetNumberoffset;

Assert (rel->rd_index!=NULL);

Assert(rel->rd_index->indnatts!=0);

Assert(rel->rd_index->indnkeyatts!=0);

nkeyatts=IndexRelationGetNumberOfKeyAttributes(rel);

is_unique= false;

itup_scankey=_bt_mkscankey(rel,itup);

{

TupleDescitupdesc=RelationGetDescr(rel);

intnkeyatts=rel->rd_index->indnkeyatts;

Assert (rel->rd_index!=NULL);

Assert(rel->rd_index->indnatts!=0);

Assert(rel->rd_index->indnkeyatts!=0);

SnapshotDataSnapshotDirty;

OffsetNumbermaxoff;

Pagepage;

BTPageOpaqueopaque;

Buffernbuf=InvalidBuffer;

boolfound= false;

Assert(rel->rd_index->indnatts!=0);

Assert(rel->rd_index->indnkeyatts!=0);

*is_unique= true;

BufferGetBlockNumber(buf));

if (rel->rd_index->indnatts!=rel->rd_index->indnkeyatts)

if (!P_ISLEAF(lpageop))

itup=index_reform_tuple(rel,itup,rel->rd_index->indnatts,rel->rd_index->indnkeyatts);

if (rel->rd_index->indnatts!=rel->rd_index->indnkeyatts

&& !P_ISLEAF(lpageop))

{

itup=index_reform_tuple(rel,itup,

rel->rd_index->indnatts,rel->rd_index->indnkeyatts);

}

itemsz=IndexTupleDSize(*itup);

itemsz=MAXALIGN(itemsz);/* be safe, PageAddItem will do this but we

itup_scankey=_bt_mkscankey(rel,targetkey);

stack=_bt_search(rel,IndexRelationGetNumberOfKeyAttributes(rel),itup_scankey,

false,&lbuf,BT_READ);

stack=_bt_search(rel,

IndexRelationGetNumberOfKeyAttributes(rel),

itup_scankey, false,&lbuf,BT_READ);

_bt_relbuf(rel,lbuf);

}

if (wstate->index->rd_index->indnatts!=wstate->index->rd_index->indnkeyatts)

if (wstate->index->rd_index->indnatts

!=wstate->index->rd_index->indnkeyatts)

{

BTPageOpaquepageop= (BTPageOpaque)PageGetSpecialPointer(npage);

if (!P_ISLEAF(pageop))

{

itup=index_reform_tuple(wstate->index,itup,wstate->index->rd_index->indnatts,wstate->index->rd_index->indnkeyatts);

itup=index_reform_tuple(wstate->index,

itup,wstate->index->rd_index->indnatts,

wstate->index->rd_index->indnkeyatts);

itupsz=IndexTupleDSize(*itup);

itupsz=MAXALIGN(itupsz);

}

{

ScanKeyskey;

TupleDescitupdesc;

intnkeyatts=rel->rd_rel->relnatts;

intnkeyatts;

int16*indoption=rel->rd_indoption;

inti;

itupdesc=RelationGetDescr(rel);

Assert(rel->rd_index!=NULL);

Assert(rel->rd_index->indnkeyatts!=0);

Assert(rel->rd_index->indnkeyatts <=rel->rd_index->indnatts);

Relationirel;

inti;

Datumd;

relationId=IndexGetRelation(oldId, false);

errmsg("must specify at least one key column")));

stmt->indexParams=list_concat(stmt->indexParams,stmt->indexIncludingParams);

numberOfAttributes=list_length(stmt->indexParams);

boolnulls_first;

PathKey*cpathkey;

if(i >=index->nkeycolumns)

break;

indexkey=indextle->expr;