bool amclusterable;

/* does AM handle predicate locks? */

bool ampredlocks;

/* does AM support columns included with clauseINCLUDING? */

/* does AM support columns included with clauseINCLUDE? */

bool amcaninclude;

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

Oid amkeytype;

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

feature sets <structfield>amcanunique</> true.

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

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

<literal>INCLUDE</> clause are not used to enforce 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>)

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

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

</synopsis>

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

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

<literal>INCLUDING</literal> aren't used to enforce constraints (UNIQUE,

<literal>INCLUDE</literal> aren't used to enforce constraints (UNIQUE,

PRIMARY KEY, etc).

</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> ) ]

[INCLUDE ( <replaceable class="parameter">column_name</replaceable> ) ]

[ 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> ]

</varlistentry>

<varlistentry>

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

<term><literal>INCLUDE</literal></term>

<listitem>

<para>

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

An optional <literal>INCLUDE</> 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

<literal>INCLUDE</> 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

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

in some cases allows <productname>&productname;</> 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.

<literal>INCLUDE</> 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 be included in the

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

<literal>INCLUDE</> 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.

Expressions as included columns are not supported since they cannot be used

in index-only scan.

</para>

<note>

<para>

First versions of this feture used keyword INCLUDING, which is still supported,

but deprecated now.

</para>

</note>

</listitem>

</varlistentry>

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

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

</programlisting>

</para>

[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]

{ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |

UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDING (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |

PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDING (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |

UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDE (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |

PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDE (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |

EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |

FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }

<varlistentry>

<term><literal>UNIQUE</> (column constraint)</term>

<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )

<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>

<optional>INCLUDE ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>

<listitem>

<para>

The <literal>UNIQUE</literal> constraint specifies that a

<para>

Adding a unique constraint will automatically create a unique btree

index on the column or group of columns used in the constraint.

Optional clause <literal>INCLUDING</literal> allows to add into the index

Optional clause <literal>INCLUDE</literal> allows to add into the index

a portion of columns on which the uniqueness is not enforced upon.

Note, that althogh constraint is not enforced upon 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>INCLUDING</literal> in

See paragraph about <literal>INCLUDE</literal> in

<xref linkend="SQL-CREATEINDEX"> for more information.

</para>

<varlistentry>

<term><literal>PRIMARY KEY</> (column constraint)</term>

<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )

<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>

<optional>INCLUDE ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>

<listitem>

<para>

The <literal>PRIMARY KEY</> constraint specifies that a column or

<para>

Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree

index on the column or group of columns used in the constraint.

Optionalclause <literal>INCLUDING</literal> allows to add into the index

An optionalclause <literal>INCLUDE</literal> allows to add into the index

a portion of columns on which the constraint is not enforced upon.

Note, that althogh constraint is not enforced upon 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>INCLUDING</literal> in

See paragraph about <literal>INCLUDE</literal> in

<xref linkend="SQL-CREATEINDEX"> for more information.

</para>

</listitem>

}

index_truncate_tuple(Relationidxrel,IndexTupleolditup)

to behave sanely across two datatypes. The extensions to three or more

datatypes within a family are not strictly required by the btree index

mechanism itself, but the planner relies on them for optimization purposes.

Included attributes in B-tree indexes

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

Since 10.0 there is an optional INCLUDE clause, that allows to add

a portion of non-key attributes to index. They exist to allow more queries

to benefit from index-only scans. We never use included attributes in

ScanKeys, neither for search nor for inserts. That allows us to include

into B-tree any datatypes, even those which don't have suitable opclass.

Included columns only stored in regular items on leaf pages. All inner

keys and high keys are truncated and contain only key attributes.

That helps to reduce the size of index.

numberOfKeyAttributes=list_length(stmt->indexParams);

stmt->indexParams=list_concat(stmt->indexParams,

stmt->indexIncludingParams);

PathKey*cpathkey;

if(i >=index->nkeycolumns)

HANDLER HAVING HEADER_P HOLD HOUR_P

IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P

IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE

INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P

INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER

INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION

$$ = (Node *)n;

}

{

Constraint *n = makeNode(Constraint);

n->contype = CONSTR_EXCLUSION;

n->location =@1;

n->access_method=$2;

n->exclusions=$4;

n->options=$6;

n->including=$6;

n->options=$7;

n->indexname=NULL;

n->indexspace=$7;

n->where_clause=$8;

processCASbits($9, @9,"EXCLUDE",

n->indexspace=$8;

n->where_clause=$9;

processCASbits($10, @10,"EXCLUDE",

&n->deferrable, &n->initdeferred,NULL,

NULL, yyscanner);

$$ = (Node *)n;

}

;

opt_c_including:INCLUDINGoptcincluding{$$ =$2; }

opt_c_including:include_keywordoptcincluding{$$ =$2; }

|/* EMPTY*/{$$ = NIL; }

;

optcincluding :'('columnList')'{$$ =$2; }

;

include_keyword:

INCLUDE{}

|INCLUDING/* Deprecated. Only in early versions of PGPRO.*/{}

;

key_match:MATCHFULL

{

$$ = FKCONSTR_MATCH_FULL;

optincluding :'('index_including_params')'{$$ =$2; }

;

opt_including:INCLUDINGoptincluding{$$ =$2; }

opt_including:include_keywordoptincluding{$$ =$2; }

|/* EMPTY*/{$$ = NIL; }

;

| IMMUTABLE

| IMPLICIT_P

| IMPORT_P

| INCLUDE

| INCLUDING

| INCREMENT

| INDEX

index->indexParams=lappend(index->indexParams,iparam);

}

foreach(lc,constraint->including)

{

char*key=strVal(lfirst(lc));