-- create index

CREATE INDEX testidx ON test USING gist (a);

-- query

SELECT * FROM test WHERE a< 10;

SELECT * FROM test WHERE a&lt; 10;

</programlisting>

</sect2>

</para>

<programlisting>

a && b Overlaps

a &amp;&amp; b Overlaps

</programlisting>

<para>

The cubements a and b overlap.

</para>

<programlisting>

a @> b Contains

a @&gt; b Contains

</programlisting>

<para>

The cubement a contains the cubement b.

</para>

<programlisting>

a<@ b Contained in

a&lt;@ b Contained in

</programlisting>

<para>

The cubement a is contained in b.

</para>

<para>

(Before PostgreSQL 8.2, the containment operators @> and<@ were

(Before PostgreSQL 8.2, the containment operators @&gt; and&lt;@ were

respectively called @ and ~. These names are still available, but are

deprecated and will eventually be retired. Notice that the old names

are reversed from the convention formerly followed by the core geometric

</para>

<programlisting>

[a, b]< [c, d] Less than

[a, b]> [c, d] Greater than

[a, b]&lt; [c, d] Less than

[a, b]&gt; [c, d] Greater than

</programlisting>

<para>

is useful for creating bounding boxes around a point for searching for

nearby points. All defined dimensions are changed by the radius. If n

is greater than the number of defined dimensions and the cube is being

increased (r>= 0) then 0 is used as the base for the extra coordinates.

increased (r&gt;= 0) then 0 is used as the base for the extra coordinates.

LL coordinates are decreased by r and UR coordinates are increased by r.

If a LL coordinate is increased to larger than the corresponding UR

coordinate (this can only happen when r< 0) than both coordinates are

coordinate (this can only happen when r&lt; 0) than both coordinates are

set to their average. To make it harder for people to break things there

is an effective maximum on the dimension of cubes of 100. This is set

in cubedata.h if you need something bigger.

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.220 2007/11/27 16:46:36 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.221 2007/12/03 23:49:50 tgl Exp $ -->

<chapter id="datatype">

<title id="datatype-title">Data Types</title>

function is convenient for performing such normalization:

<programlisting>

SELECT to_tsquery('Fat:ab & Cats');

SELECT to_tsquery('Fat:ab &amp; Cats');

to_tsquery

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

'fat':AB & 'cat'

'fat':AB &amp; 'cat'

</programlisting>

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.79 2007/12/0304:59:55 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.80 2007/12/0323:49:50 tgl Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

<para>

To set up a partitioned table, do the following:

<orderedlist spacing=compact>

<orderedlist spacing="compact">

<listitem>

<para>

Create the <quote>master</quote> table, from which all of the

</para>

<para>

<orderedlist spacing=compact>

<orderedlist spacing="compact">

<listitem>

<para>

The master table is the <structname>measurement</> table, declared

<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.16 2007/02/01 00:28:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.17 2007/12/03 23:49:50 tgl Exp $ -->

<chapter id="dml">

<title>Data Manipulation</title>

<para>

To perform an update, you need three pieces of information:

<orderedlist spacing=compact>

<orderedlist spacing="compact">

<listitem>

<para>The name of the table and column to update,</para>

</listitem>

</entry>

</row>

<row>

<entry><literal><@></literal> operator</entry>

<entry><literal>&lt;@&gt;</literal> operator</entry>

<entry>gives the distance in statute miles between

two points on the Earth's surface. Coordinates are in degrees. Points are

taken as (longitude, latitude) and not vice versa as longitude is closer

<!-- $PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.84 2007/11/07 12:24:23 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.85 2007/12/03 23:49:50 tgl Exp $ -->

<chapter id="ecpg">

<title><application>ECPG</application> - Embedded <acronym>SQL</acronym> in C</title>

ECPG = ecpg

%.c: %.pgc

$(ECPG) $<

$(ECPG) $&lt;

</programlisting>

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.414 2007/11/28 15:42:31 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.415 2007/12/03 23:49:50 tgl Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

<entry><literal><function>to_tsquery</function>(<optional> <replaceable class="PARAMETER">config</> <type>regconfig</> , </optional> <replaceable class="PARAMETER">query</> <type>text</type>)</literal></entry>

<entry><type>tsquery</type></entry>

<entry>normalize words and convert to <type>tsquery</></entry>

<entry><literal>to_tsquery('english', 'The & Fat & Rats')</literal></entry>

<entry><literal>'fat' & 'rat'</literal></entry>

<entry><literal>to_tsquery('english', 'The &amp; Fat &amp; Rats')</literal></entry>

<entry><literal>'fat' &amp; 'rat'</literal></entry>

</row>

<row>

<entry><literal><function>plainto_tsquery</function>(<optional> <replaceable class="PARAMETER">config</> <type>regconfig</> , </optional> <replaceable class="PARAMETER">query</> <type>text</type>)</literal></entry>

<entry><type>tsquery</type></entry>

<entry>produce <type>tsquery</> ignoring punctuation</entry>

<entry><literal>plainto_tsquery('english', 'The Fat Rats')</literal></entry>

<entry><literal>'fat' & 'rat'</literal></entry>

<entry><literal>'fat' &amp; 'rat'</literal></entry>

</row>

<row>

<entry><literal><function>numnode</function>(<type>tsquery</>)</literal></entry>

<entry><literal><function>querytree</function>(<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>)</literal></entry>

<entry><type>text</type></entry>

<entry>get indexable part of a <type>tsquery</></entry>

<entry><literal>querytree('foo & ! bar'::tsquery)</literal></entry>

<entry><literal>querytree('foo &amp; ! bar'::tsquery)</literal></entry>

<entry><literal>'foo'</literal></entry>

</row>

<row>

SELECT * FROM s WHERE soundex(nm) = soundex('john');

SELECT a.nm, b.nm FROM s a, s b WHERE soundex(a.nm) = soundex(b.nm) AND a.oid<> b.oid;

SELECT a.nm, b.nm FROM s a, s b WHERE soundex(a.nm) = soundex(b.nm) AND a.oid&lt;&gt; b.oid;

CREATE FUNCTION text_sx_eq(text, text) RETURNS boolean AS

'select soundex($1) = soundex($2)'

LANGUAGE SQL;

CREATE FUNCTION text_sx_lt(text, text) RETURNS boolean AS

'select soundex($1)< soundex($2)'

'select soundex($1)&lt; soundex($2)'

LANGUAGE SQL;

CREATE FUNCTION text_sx_gt(text, text) RETURNS boolean AS

'select soundex($1)> soundex($2)'

'select soundex($1)&gt; soundex($2)'

LANGUAGE SQL;

CREATE FUNCTION text_sx_le(text, text) RETURNS boolean AS

'select soundex($1)<= soundex($2)'

'select soundex($1)&lt;= soundex($2)'

LANGUAGE SQL;

CREATE FUNCTION text_sx_ge(text, text) RETURNS boolean AS

'select soundex($1)>= soundex($2)'

'select soundex($1)&gt;= soundex($2)'

LANGUAGE SQL;

CREATE FUNCTION text_sx_ne(text, text) RETURNS boolean AS

'select soundex($1)<> soundex($2)'

'select soundex($1)&lt;&gt; soundex($2)'

LANGUAGE SQL;

DROP OPERATOR #= (text, text);

SELECT * FROM s WHERE s.nm #= 'john';

SELECT * FROM s WHERE difference(s.nm, 'john')> 2;

SELECT * FROM s WHERE difference(s.nm, 'john')&gt; 2;

</programlisting>

</sect2>

</thead>

<tbody>

<row>

<entry><literal>int[] && int[]</literal></entry>

<entry><literal>int[] &amp;&amp; int[]</literal></entry>

<entry>overlap - returns TRUE if arrays have at least one common element</entry>

</row>

<row>

<entry><literal>int[] @> int[]</literal></entry>

<entry><literal>int[] @&gt; int[]</literal></entry>

<entry>contains - returns TRUE if left array contains right array</entry>

</row>

<row>

<entry><literal>int[]<@ int[]</literal></entry>

<entry><literal>int[]&lt;@ int[]</literal></entry>

<entry>contained - returns TRUE if left array is contained in right array</entry>

</row>

<row>

</row>

<row>

<entry><literal>int[] & int[]</literal></entry>

<entry><literal>int[] &amp; int[]</literal></entry>

<entry>returns intersection of arrays</entry>

</row>

</tgroup>

</table>

<para>

(Before PostgreSQL 8.2, the containment operators @> and<@ were

(Before PostgreSQL 8.2, the containment operators @&gt; and&lt;@ were

respectively called @ and ~. These names are still available, but are

deprecated and will eventually be retired. Notice that the old names

are reversed from the convention formerly followed by the core geometric

CREATE INDEX message_rdtree_idx ON message USING GIST ( sections gist__int_ops);

-- select some messages with section in 1 OR 2 - OVERLAP operator

SELECT message.mid FROM message WHERE message.sections && '{1,2}';

SELECT message.mid FROM message WHERE message.sections &amp;&amp; '{1,2}';

-- select messages contains in sections 1 AND 2 - CONTAINS operator

SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

SELECT message.mid FROM message WHERE message.sections @&gt; '{1,2}';

-- the same, CONTAINED operator

SELECT message.mid FROM message WHERE '{1,2}'<@ message.sections;

SELECT message.mid FROM message WHERE '{1,2}'&lt;@ message.sections;

</programlisting>

</sect2>

<programlisting>

cd ./bench

1. createdb TEST

2. psql TEST< ../_int.sql

2. psql TEST&lt; ../_int.sql

3. ./create_test.pl | psql TEST

4. ./bench.pl - perl script to benchmark queries, supports OR, AND queries

with/without RD-Tree. Run script without arguments to