<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.14 2001/11/1903:58:23 tgl Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.15 2001/11/1909:05:00 tgl Exp $ -->

<chapter id="arrays">

<title>Arrays</title>

);

</programlisting>

As shown, an array data type is named by appending square brackets

(<literal>[]</>) to the data type name of the array elements.

(<literal>[]</>) to the data type name of the array elements.

The above query will create a table named

<structname>sal_emp</structname> with a <type>text</type> string

(<structfield>name</structfield>), a one-dimensional array of type

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.70 2001/11/1903:58:22 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.71 2001/11/1909:05:00 tgl Exp $

-->

<chapter id="datatype">

<para>

<xref linkend="datatype-table"> shows all general-purpose data types

available to users. Most of the alternative names listed in the

included in the standard distribution. Most of the alternative names

listed in the

<quote>Aliases</quote> column are the names used internally by

<productname>Postgres</productname> for historical reasons. In

addition, some internally used or deprecated types are available,

but they are not documented here. Many of the built-in types have

obvious external formats. However, several types are either unique

to <productname>Postgres</productname>, such as open and closed

paths, or have several possibilities for formats, such as the date

and time types.

but they are not listed here.

</para>

<para>

<row>

<entry><type>timestamp [ with time zone ]</type></entry>

<entry>timestamptz</entry>

<entry>date and time</entry>

<entry>date and time, including time zone</entry>

</row>

</tbody>

</tgroup>

</note>

<para>

Each data type has an external representation determined by its input

and output functions. Many of the built-in types have

obvious external formats. However, several types are either unique

to <productname>Postgres</productname>, such as open and closed

paths, or have several possibilities for formats, such as the date

and time types.

Most of the input and output functions corresponding to the

base types (e.g., integers and floating point numbers) do some

error-checking.

Some of the input and output functions are not invertible. That is,

the result of an output function may lose precision when compared to

the original input.

</para>

<para>

Some of the operators and functions (e.g.,

addition and multiplication) do not perform run-time error-checking in the

interests of improving execution speed.

On some systems, for example, the numeric operators for some data types may

silently underflow or overflow.

</para>

<para>

Some of the input and output functions are not invertible. That is,

the result of an output function may lose precision when compared to

the original input.

</para>

<sect1 id="datatype-numeric">

<title>Numeric Types</title>

platform where this is actually the case.

</para>

<para>

SQL only specifies the integer types <type>integer</type> (or

<type>int</type>) and <type>smallint</type>. The type

<type>bigint</type>, and the type names <type>int2</type>,

<type>int4</type>, and <type>int8</type> are extensions, which

are shared with various other RDBMS products.

</para>

<note>

<para>

If you have a column of type <type>smallint</type> or

... WHERE smallint_column = 42

</programlisting>

will not use an index, because the system assigns type

<type>integer</type> to the 42, and PostgreSQL currently cannot

use an index when two different data types are involved. A

<type>integer</type> to theconstant42, and PostgreSQL currently

cannotuse an index when two different data types are involved. A

workaround is to single-quote the constant, thus:

<programlisting>

... WHERE smallint_column = '42'

</programlisting>

This will cause the system to delaythetype resolution and will

This will cause the system to delay type resolution and will

assign the right type to the constant.

</para>

</note>

<para>

SQL only specifies the integer types <type>integer</type> (or

<type>int</type>) and <type>smallint</type>. The type

<type>bigint</type>, and the type names <type>int2</type>,

<type>int4</type>, and <type>int8</type> are extensions, which

are shared with various other RDBMS products.

</para>

</sect2>

<sect2 id="datatype-numeric-decimal">

<para>

Normally, the <type>real</type> type has a range of at least

-1E+37 to +1E+37 with a precision of at least 6. The

-1E+37 to +1E+37 with a precision of at least 6 decimal digits. The

<type>double precision</type> type normally has a range of around

-1E+308 to +1E+308 with a precision of at least 15. Values that

-1E+308 to +1E+308 with a precision of at least 15 digits. Values that

are too large or too small will cause an error. Rounding may

take place if the precision of an input number is too high.

Numbers too close to zero that are not representable as distinct

or abbreviations or plurals of these units;

<literal>Direction</literal> can be <literal>ago</literal> or

empty. The at sign (<literal>@</>) is optional noise. The amounts

of differentquantities are implicitly added up with appropriate

of differentunits are implicitly added up with appropriate

sign accounting.

</para>

<para>

Quantities of days, hours, minutes, and seconds can be specified without

explicit unit markings: for example, <literal>'1 12:59:10'</> is read

explicit unit markings. For example, <literal>'1 12:59:10'</> is read

the same as <literal>'1 day 12 hours 59 min 10 sec'</>.

</para>

</sect3>

</tbody>

</tgroup>

</table>

<literal>'now'</literal> is resolved when the value is inserted, <literal>'current'</literal>

is resolved every time the value is retrieved. So you probably want to use <literal>'now'</literal>

in most applications. (Of course you <emphasis>really</emphasis> want to use

<literal>CURRENT_TIMESTAMP</literal>, which is equivalent to <literal>'now'</literal>.)

</para>

</sect3>

</para>

<para>

The output of the <type>date</type> and <type>time</type>styles is of course

The output of the <type>date</type> and <type>time</type>types is of course

only the date or time part in accordance with the above examples.

</para>

<para>

Although the <type>date</type> type

does not have an associated time zone, the

<type>time</type> type can or does.

<type>time</type> type can.

Time zones in the real world can have no meaning unless

associated with a date as well as a time

since the offset may vary through the year with daylight savings

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.81 2001/11/1903:58:23 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.82 2001/11/1909:05:01 tgl Exp $

Postgres documentation

-->

and some explicitly marked functions, are not specified by the <acronym>SQL</acronym>

standard. Some of this extended functionality is present in other

<acronym>RDBMS</acronym> products, and in many cases this

functionality is compatible andconsistant between various products.

functionality is compatible andconsistent between various products.

</para>

Functions returning a <type>numeric</type> result take

<type>numeric</type> input arguments, unless otherwise specified.

Many of these functions are implemented on top

of the host system's C libraryand behavior in boundary cases could

therefore vary depending on theoperating system.

of the host system's C library; accuracyand behavior in boundary cases

couldtherefore vary depending on thehost system.

</para>

<table>

<entry><function>char_length</function>(<parameter>string</parameter>) or <function>character_length</function>(<parameter>string</parameter>)</entry>

<entry><type>integer</type></entry>

<entry>

length of string

number of characters in string

<indexterm>

<primary>character strings</primary>

<secondary>length</secondary>

<parameter>characters</parameter> (a space by default) from the

beginning/end/both ends of the <parameter>string</parameter>.

</entry>

<entry><literal>trim(both 'x' from 'xTomx')</literal></entry>

<entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>

<entry><literal>Tom</literal></entry>

</row>

<para>

Additional string manipulation functions are available and are

listed below. Some of them are used internally to implement the

<acronym>SQL</acronym> string functions listed above.

<acronym>SQL</acronym>-standard string functions listed above.

</para>

<table id="functions-string-other">

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v 1.26 2001/11/18 00:59:00 tgl Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v 1.27 2001/11/19 09:05:01 tgl Exp $ -->

<chapter id="indexes">

<title id="indexes-title">Indexes</title>

</para>

<para>

Indexes can also benefit <command>UPDATE</command>s and

<command>DELETE</command>s with search conditions. Note that a

query or data manipulation commands can only use at most one index

per table. Indexes can also be used in table join methods. Thus,

Indexes can benefit <command>UPDATE</command>s and

<command>DELETE</command>s with search conditions. Indexes can also be

used in join queries. Thus,

an index defined on a column that is part of a join condition can

significantly speed up queries with joins.

</para>

When an index is created, it has to be kept synchronized with the

table. This adds overhead to data manipulation operations.

Therefore indexes that are non-essential or do not get used at all

should be removed.

should be removed. Note that a

query or data manipulation command can use at most one index

per table.

</para>

</sect1>

<primary>R-tree</primary>

<see>indexes</see>

</indexterm>

R-tree indexes are especially suited forspacial data. To create

R-tree indexes are especially suited forspatial data. To create

an R-tree index, use a command of the form

<synopsis>

CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING RTREE (<replaceable>column</replaceable>);

name varchar

);

</programlisting>

(Say, you keepyouyour <filename class="directory">/dev</filename>

(Say, you keep your <filename class="directory">/dev</filename>

directory in a database...) and you frequently make queries like

<programlisting>

SELECT name FROM test2 WHERE major = <replaceable>constant</replaceable> AND minor = <replaceable>constant</replaceable>;

<para>

For example, a common way to do case-insensitive comparisons is to

use the <function>lower</function>:

use the <function>lower</function> function:

<programlisting>

SELECT * FROM test1 WHERE lower(col1) = 'value';

</programlisting>

In order for thatqueryto be able touse an index,ithasto be

Thisquerycanuse an index,if onehasbeen

defined on the result of the <literal>lower(column)</literal>

operation:

<programlisting>

<tip>

<para>

The restrictions mentioned in the previous paragraph can easily be

worked around by defining customfunctions to use in the index

definition thatcall the desiredfunction(s) internally.

worked around by definingacustomfunction to use in the index

definition thatcomputes any desiredresult internally.

</para>

</tip>

</sect1>

A <firstterm>partial index</firstterm> is an index built over a

subset of a table; the subset is defined by a conditional

expression (called the <firstterm>predicate</firstterm> of the

partial index).

partial index). The index contains entries for only those table

rows that satisfy the predicate.

</para>

<para>

A major motivation for partial indexes is to avoid indexing common

values. Since a query conditionalized on a common value will not

use the index anyway, there is no point in keeping those rows in the

index at all. This reduces the size of the index, which will speed

up queries that do use the index. It will also speed up manydata

manipulation operations because the index does not need to be

up queries that do use the index. It will also speed up manytable

update operations because the index does not need to be

updated in all cases. <xref linkend="indexes-partial-ex1"> shows a

possible application of this idea.

</para>

<para>

Although indexes in <productname>PostgreSQL</> do not need

maintenance and tuning, it is still importantthat it is checked

maintenance and tuning, it is still importantto check

which indexes are actually used by the real-life query workload.

Examining index usage is done with the <command>EXPLAIN</> command;

its application for this purpose is illustrated in <xref

<para>

If you do not succeed in adjusting the costs to be more

appropriate, then you may have todo with forcing index usage

appropriate, then you may have toresort to forcing index usage

explicitly. You may also want to contact the

<productname>PostgreSQL</> developers to examine the issue.

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.50 2001/11/08 23:34:33 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.51 2001/11/19 09:05:01 tgl Exp $

-->

<chapter id="sql-syntax">

Transaction identifiers are 32-bit quantities. In a long-lived

database it is possible for transaction IDs to wrap around. This

is not a fatal problem given appropriate maintenance procedures;

see the Administrator's Guide for details. However, it is unwise

to depend on uniqueness of transaction IDs over the long term

see the<citetitle>Administrator's Guide</> for details. However, it is

unwiseto depend on uniqueness of transaction IDs over the long term

(more than one billion transactions).

</para>

if (tm->tm_year>0)

tm->tm_year=-(tm->tm_year-1);

elog(ERROR,"Inconsistant use of year %04d and 'BC'",tm->tm_year);

elog(ERROR,"Inconsistent use of year %04d and 'BC'",tm->tm_year);

}

elseif (is2digits)

{

if (tm->tm_year>0)

tm->tm_year=-(tm->tm_year-1);

elog(ERROR,"Inconsistant use of year %04d and 'BC'",tm->tm_year);

elog(ERROR,"Inconsistent use of year %04d and 'BC'",tm->tm_year);

}

elseif (is2digits)

{