<entry>Does an index of this type manage fine-grained predicate locks?</entry>

</row>

<row>

<entry><structfield>amcanincluding</structfield></entry>

<entry><type>bool</type></entry>

<entry></entry>

<entry>Does the access method support included columns?</entry>

</row>

<row>

<entry><structfield>amkeytype</structfield></entry>

<entry><type>oid</type></entry>

<literal>pg_class.relnatts</literal>)</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 in contrast with "included" columns.</entry>

</row>

<row>

<entry><structfield>indisunique</structfield></entry>

<entry><type>bool</type></entry>

<para>

<productname>&productname;</productname> enforces SQL uniqueness constraints

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

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

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

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

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

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>

<para>

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.

<synopsis>

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

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

<optional>INCLUDING (<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.

indexed columns are equal in multiple rows. Columns included with clause

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

</para>

<para>

<synopsis>

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]

( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

[ INCLUDING ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]

[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]

[ WHERE <replaceable class="parameter">predicate</replaceable> ]

</listitem>

</varlistentry>

<varlistentry>

<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.

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

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">name</replaceable></term>

<listitem>

<title>Examples</title>

<para>

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

To createan 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 create an 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>

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

</programlisting>

</para>

<para>

To create an index on the expression <literal>lower(title)</>,

allowing efficient case-insensitive searches:

memcpy(result,source,size);

returnresult;

}

index_reform_tuple(Relationidxrel,IndexTupleolditup,intnatts,intnkeyatts)

{

TupleDescitupdesc=RelationGetDescr(idxrel);

Datumvalues[INDEX_MAX_KEYS];

boolisnull[INDEX_MAX_KEYS];

IndexTuplenewitup;

Assert(natts <=INDEX_MAX_KEYS);

Assert(nkeyatts>0);

Assert(nkeyatts <=natts);

index_deform_tuple(olditup,itupdesc,values,isnull);

itupdesc->natts=nkeyatts;

newitup=index_form_tuple(itupdesc,values,isnull);

newitup->t_tid=olditup->t_tid;

itupdesc->natts=natts;

returnnewitup;

}

IndexUniqueCheckcheckUnique,RelationheapRel)

{

boolis_unique= false;

intnatts=rel->rd_rel->relnatts;

intnkeyatts=rel->rd_rel->relnatts;

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);

itup_scankey=_bt_mkscankey(rel,itup);

top:

stack=_bt_search(rel,natts,itup_scankey, false,&buf,BT_WRITE);

stack=_bt_search(rel,nkeyatts,itup_scankey, false,&buf,BT_WRITE);

offset=InvalidOffsetNumber;

buf=_bt_moveright(rel,buf,natts,itup_scankey, false,

buf=_bt_moveright(rel,buf,nkeyatts,itup_scankey, false,

true,stack,BT_WRITE);

TransactionIdxwait;

uint32speculativeToken;

offset=_bt_binsrch(rel,buf,natts,itup_scankey, false);

offset=_bt_binsrch(rel,buf,nkeyatts,itup_scankey, false);

xwait=_bt_check_unique(rel,itup,heapRel,buf,offset,itup_scankey,

checkUnique,&is_unique,&speculativeToken);

CheckForSerializableConflictIn(rel,NULL,buf);

_bt_findinsertloc(rel,&buf,&offset,natts,itup_scankey,itup,

_bt_findinsertloc(rel,&buf,&offset,nkeyatts,itup_scankey,itup,

stack,heapRel);

_bt_insertonpg(rel,buf,InvalidBuffer,stack,itup,offset, false);

}

uint32*speculativeToken)

{

TupleDescitupdesc=RelationGetDescr(rel);

intnatts=rel->rd_rel->relnatts;

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;

if (!_bt_isequal(itupdesc,page,offset,natts,itup_scankey))

if (!_bt_isequal(itupdesc,page,offset,nkeyatts,itup_scankey))

break;/* we're past all the equal tuples */

if (P_RIGHTMOST(opaque))

break;

if (!_bt_isequal(itupdesc,page,P_HIKEY,

natts,itup_scankey))

nkeyatts,itup_scankey))

break;

for (;;)

elog(ERROR,"cannot insert to incompletely split page %u",

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);

itemsz=IndexTupleDSize(*itup);

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

right_item_sz=ItemIdGetLength(itemid);

item= (IndexTuple)PageGetItem(lpage,itemid);

right_item=CopyIndexTuple(item);

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

ItemPointerSet(&(right_item->t_tid),rbkno,P_HIKEY);

itup_scankey=_bt_mkscankey(rel,targetkey);

stack=_bt_search(rel,rel->rd_rel->relnatts,itup_scankey,

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

false,&lbuf,BT_READ);

_bt_relbuf(rel,lbuf);

state->btps_minkey=CopyIndexTuple(itup);

}

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);

itupsz=IndexTupleDSize(*itup);

itupsz=MAXALIGN(itupsz);

}

}

{

ScanKeyskey;

TupleDescitupdesc;

intnatts;

int16*indoption;

intnkeyatts=rel->rd_rel->relnatts;

int16*indoption=rel->rd_indoption;

inti;

itupdesc=RelationGetDescr(rel);

natts=RelationGetNumberOfAttributes(rel);

indoption=rel->rd_indoption;

skey= (ScanKey)palloc(natts*sizeof(ScanKeyData));

Assert(rel->rd_index!=NULL);

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

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

for (i=0;i<natts;i++)

nkeyatts=rel->rd_index->indnkeyatts;

skey= (ScanKey)palloc(nkeyatts*sizeof(ScanKeyData));

for (i=0;i<nkeyatts;i++)

{

FmgrInfo*procinfo;

Datumarg;

stmt->accessMethod =$8;

stmt->tableSpace =NULL;

stmt->indexParams =$10;

stmt->indexIncludingParams = NIL;

stmt->options = NIL;

stmt->whereClause =NULL;

stmt->excludeOpNames = NIL;

stmt->accessMethod =$9;

stmt->tableSpace =NULL;

stmt->indexParams =$11;

stmt->indexIncludingParams = NIL;

stmt->options = NIL;

stmt->whereClause =NULL;

stmt->excludeOpNames = NIL;