datatype.sgml

来自「关系型数据库 Postgresql 6.5.2」· SGML 代码 · 共 1,926 行 · 第 1/4 页

<para>
<type>timestamp</type> is specified using the same syntax as for 
<type>datetime</type>.
</para>
</sect2>

<sect2>
<title><type>interval</type></title>

<para>
<type>interval</type> is an <acronym>SQL92</acronym> data type which is
currently mapped to the <type>timespan</type> 
<productname>Postgres</productname> data type.
</para>
</sect2>

<sect2>
<title>tinterval</title>

<para>
Time ranges are specified as:

<programlisting>
[ 'abstime' 'abstime']
where
    abstime is a time in the absolute time format.
</programlisting>

Special abstime values such as 
<literal>current', <literal>infinity' and <literal>-infinity' can be used.</literal></literal></literal>
</para></sect2>

</sect1>

<sect1>
<title>Boolean Type</title>

<para>
<productname>Postgres</productname> supports <type>bool</type> as
the <acronym>SQL3</acronym> boolean type.
<type>bool</type> can have one of only two states: 'true' or 'false'. 
A third state, 'unknown', is not
implemented and is not suggested in <acronym>SQL3</acronym>; 
<acronym>NULL</acronym> is an
effective substitute. <type>bool</type> can be used in any boolean expression, 
and boolean expressions
always evaluate to a result compatible with this type.</para>

<para>
<type>bool</type> uses 1 byte of storage.
</para>

<para>
<table tocentry="1">
<title><productname>Postgres</productname> Boolean Type</title>
<titleabbrev>Booleans</titleabbrev>
<tgroup cols="3">
<thead>
  <row>
    <entry>State</entry>
    <entry>Output</entry>
    <entry>Input</entry>
  </row>
</thead>
<tbody>
  <row>
    <entry>True</entry>
    <entry>'t'</entry>
    <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
  </row>
  <row>
    <entry>False</entry>
    <entry>'f'</entry>
    <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
  </row>
</tbody>
</tgroup>
</table>
</para>
</sect1>

<sect1>
<title>Geometric Types</title>

<para>
Geometric types represent two-dimensional spatial objects. 
The most fundamental type,
the point, forms the basis for all of the other types.
</para>

<para>
<table tocentry="1">
<title><productname>Postgres</productname> Geometric Types</title>
<titleabbrev>Geometrics</titleabbrev>
<tgroup cols="4">
<thead>
  <row>
    <entry>Geometric Type</entry>
    <entry>Storage</entry>
    <entry>Representation</entry>
    <entry>Description</entry>
  </row>
</thead>
<tbody>
  <row>
    <entry>point</entry>
    <entry>16 bytes</entry>
    <entry>(x,y)</entry>
    <entry>Point in space</entry>
  </row>
  <row>
    <entry>line</entry>
    <entry>32 bytes</entry>
    <entry>((x1,y1),(x2,y2))</entry>
    <entry>Infinite line</entry>
  </row>
  <row>
    <entry>lseg</entry>
    <entry>32 bytes</entry>
    <entry>((x1,y1),(x2,y2))</entry>
    <entry>Finite line segment</entry>
  </row>
  <row>
    <entry>box</entry>
    <entry>32 bytes</entry>
    <entry>((x1,y1),(x2,y2))</entry>
    <entry>Rectangular box</entry>
  </row>
  <row>
    <entry>path</entry>
    <entry>4+32n bytes</entry>
    <entry>((x1,y1),...)</entry>
    <entry>Closed path (similar to polygon)</entry>
  </row>
  <row>
    <entry>path</entry>
    <entry>4+32n bytes</entry>
    <entry>[(x1,y1),...]</entry>
    <entry>Open path</entry>
  </row>
  <row>
    <entry>polygon</entry>
    <entry>4+32n bytes</entry>
    <entry>((x1,y1),...)</entry>
    <entry>Polygon (similar to closed path)</entry>
  </row>
  <row>
    <entry>circle</entry>
    <entry>24 bytes</entry>
    <entry><(x,y),r></entry>
    <entry>Circle (center and radius)</entry>
  </row>
</tbody>
</tgroup>
</table>
</para>

<para>
A rich set of functions and operators is available to perform various geometric
operations such as scaling, translation, rotation, and determining 
intersections.
</para>

<sect2>
<title>Point</title>

    <para>
     Points are the fundamental two-dimensional building block for geometric types.
    </para>

<para>
<type>point</type> is specified using the following syntax:

<programlisting>
( x , y )
  x , y
where
    x is the x-axis coordinate as a floating point number
    y is the y-axis coordinate as a floating point number
</programlisting>
</para>
</sect2>

<sect2>
<title>Line Segment</title>

<para>
Line segments (lseg) are represented by pairs of points.
</para>

<para>
<type>lseg</type> is specified using the following syntax:
<programlisting>
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )  
    x1 , y1   ,   x2 , y2    
where
    (x1,y1) and (x2,y2) are the endpoints of the segment
</programlisting>
</para>
</sect2>

<sect2>
<title>Box</title>

<para>
Boxes are represented by pairs of points which are opposite
corners of the box.
</para>

<para>
     <type>box</type> is specified using the following syntax:

<programlisting>
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )  
    x1 , y1   ,   x2 , y2    
where
    (x1,y1) and (x2,y2) are opposite corners
</programlisting>

Boxes are output using the first syntax.
The corners are reordered on input to store
the lower left corner first and the upper right corner last.
Other corners of the box can be entered, but the lower
left and upper right corners are determined from the input and stored.
</para>
</sect2>

<sect2>
<title>Path</title>

<para>
Paths are represented by connected sets of points. Paths can be "open", where
the first and last points in the set are not connected, and "closed",
where the first and last point are connected. Functions
<function>popen(p)</function>
and
<function>pclose(p)</function>
are supplied to force a path to be open or closed, and functions
<function>isopen(p)</function>
and
<function>isclosed(p)</function>
are supplied to select either type in a query.
</para>

<para>
path is specified using the following syntax:

<programlisting>
( ( x1 , y1 ) , ... , ( xn , yn ) )
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
  ( x1 , y1 ) , ... , ( xn , yn )  
  ( x1 , y1   , ... ,   xn , yn )  
    x1 , y1   , ... ,   xn , yn    
where
    (x1,y1),...,(xn,yn) are points 1 through n
    a leading "[" indicates an open path
    a leading "(" indicates a closed path
</programlisting>
Paths are output using the first syntax.
Note that <productname>Postgres</productname> versions prior to
v6.1 used a format for paths which had a single leading parenthesis, 
a "closed" flag,
an integer count of the number of points, then the list of points followed by a
closing parenthesis. 
The built-in function <function>upgradepath</function> is supplied to convert
paths dumped and reloaded from pre-v6.1 databases.
</para>
</sect2>

<sect2>
<title>Polygon</title>

<para>
Polygons are represented by sets of points. Polygons should probably be
considered equivalent to closed paths, but are stored differently 
and have their own set of support routines.
</para>

<para>
<type>polygon</type> is specified using the following syntax:

<programlisting>
( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )  
  ( x1 , y1   , ... ,   xn , yn )  
    x1 , y1   , ... ,   xn , yn    
where
    (x1,y1),...,(xn,yn) are points 1 through n
</programlisting>

Polygons are output using the first syntax.
Note that <productname>Postgres</productname> versions prior to
v6.1 used a format for polygons which had a single leading parenthesis, the list
of x-axis coordinates, the list of y-axis coordinates, 
followed by a closing parenthesis.
The built-in function <function>upgradepoly</function> is supplied to convert
polygons dumped and reloaded from pre-v6.1 databases.
</para>
</sect2>

<sect2>
<title>Circle</title>

<para>
Circles are represented by a center point and a radius.
</para>

<para>
circle is specified using the following syntax:

<programlisting>
< ( x , y ) , r >
( ( x , y ) , r )
  ( x , y ) , r  
    x , y   , r  
where
    (x,y) is the center of the circle
    r is the radius of the circle
</programlisting>

Circles are output using the first syntax.
</para>
</sect2>

</sect1>

<sect1>
<title>IP Version 4 Networks and Host Addresses</title>

<para>
The <type>cidr</type> type stores networks specified 
in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
variation in representation to represent simple host TCP/IP addresses.
</para>

<para>
<table tocentry="1">
<title><productname>Postgres</productname>IP Version 4 Types</title>
<titleabbrev>IPV4</titleabbrev>
<tgroup cols="4">
<thead>
  <row>
    <entry>IPV4 Type</entry>
    <entry>Storage</entry>
    <entry>Description</entry>
    <entry>Range</entry>
  </row>
</thead>
<tbody>
  <row>
    <entry>cidr</entry>
    <entry>variable</entry>
    <entry>CIDR networks</entry>
    <entry>Valid IPV4 CIDR blocks</entry>
  </row>
  <row>
    <entry>inet</entry>
    <entry>variable</entry>
    <entry>nets and hosts</entry>
    <entry>Valid IPV4 CIDR blocks</entry>
  </row>
</tbody>
</tgroup>
</table>
</para>

<sect2>
<title>CIDR</title>

<para>
The <type>cidr</type> type holds a CIDR network.
The format for specifying classless networks is <replaceable class="parameter">x.x.x.x/y</replaceable>
 where <replaceable class="parameter">x.x.x.x</replaceable> is the
network and <replaceable class="parameter">/y</replaceable> is the number of bits in the netmask.
If <replaceable class="parameter">/y</replaceable> omitted, it is calculated using assumptions from
the older classfull naming system except that it is extended to include at least
all of the octets in the input.
</para>

<para> 
Here are some examples:

     <table tocentry="1">
      <title><productname>Postgres</productname>IP Types Examples</title>
      <tgroup cols="2">
       <thead> 
	<row> 
	 <entry>CIDR Input</entry>
	 <entry>CIDR Displayed</entry>
	</row>
       </thead>
       <tbody>
	<row>
	 <entry>192.168.1</entry>
	 <entry>192.168.1/24</entry>
	</row>
	<row>
	 <entry>192.168</entry>
	 <entry>192.168.0/24</entry>
	</row>
	<row>
	 <entry>128.1</entry>
	 <entry>128.1/16</entry>
	</row>
	<row>
	 <entry>128</entry>
	 <entry>128.0/16</entry>
	</row>
	<row>
	 <entry>128.1.2</entry>
	 <entry>128.1.2/24</entry>
	</row>
	<row>
	 <entry>10.1.2</entry>
	 <entry>10.1.2/24</entry>
	</row>
	<row>
	 <entry>10.1</entry>
	 <entry>10.1/16</entry>
	</row>
	<row>
	 <entry>10</entry>
	 <entry>10/8</entry>
	</row>
       </tbody>
      </tgroup>
     </table>
    </para>
   </sect2>

   <sect2>
    <title id="inet-type"><type>inet</type></title>

<para>
The <type>inet</type> type is designed to hold, in one field, all of the information
about a host including the CIDR-style subnet that it is in.
Note that if you want to store proper CIDR networks,
you should use the <type>cidr</type> type.
The <type>inet</type> type is similar to the <type>cidr</type> type except that the bits in the
host part can be non-zero.
Functions exist to extract the various elements of the field.
</para>

<para>  
     The input format for this function is
 <replaceable class="parameter">x.x.x.x/y</replaceable> 
where <replaceable class="parameter">x.x.x.x</replaceable> is
an internet host and <replaceable class="parameter">y</replaceable> 
is the number of bits in the netmask.
If the <replaceable class="parameter">/y</replaceable> part is left off, 
it is treated as <literal>/32</literal>.
On output, the <replaceable class="parameter">/y</replaceable> part is not printed 
if it is <literal>/32</literal>.
This allows the type to be used as a straight host type by just leaving off
the bits part.
</para></sect2>
</sect1>

</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->