diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 164 |
1 files changed, 93 insertions, 71 deletions
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index c81c0bb9700..e4f3f7adabd 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.106.2.2 2002/11/21 23:31:37 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.106.2.3 2003/01/29 01:09:03 tgl Exp $ --> <chapter id="datatype"> @@ -87,18 +87,18 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.106.2.2 2002/11/21 23:31: </row> <row> - <entry><type>character(<replaceable>n</replaceable>)</type></entry> - <entry><type>char(<replaceable>n</replaceable>)</type></entry> - <entry>fixed-length character string</entry> - </row> - - <row> <entry><type>character varying(<replaceable>n</replaceable>)</type></entry> <entry><type>varchar(<replaceable>n</replaceable>)</type></entry> <entry>variable-length character string</entry> </row> <row> + <entry><type>character(<replaceable>n</replaceable>)</type></entry> + <entry><type>char(<replaceable>n</replaceable>)</type></entry> + <entry>fixed-length character string</entry> + </row> + + <row> <entry><type>cidr</type></entry> <entry></entry> <entry>IP network address</entry> @@ -248,7 +248,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.106.2.2 2002/11/21 23:31: The following types (or spellings thereof) are specified by <acronym>SQL</acronym>: <type>bit</type>, <type>bit varying</type>, <type>boolean</type>, <type>char</type>, - <type>character</type>, <type>character varying</type>, + <type>character varying</type>, <type>character</type>, <type>varchar</type>, <type>date</type>, <type>double precision</type>, <type>integer</type>, <type>interval</type>, <type>numeric</type>, <type>decimal</type>, <type>real</type>, @@ -654,10 +654,11 @@ NUMERIC </indexterm> <para> - The <type>serial</type> data types are not truly types, but are a - notational convenience for setting up unique identifier columns - in tables. - In the current implementation, specifying + The <type>serial</type> data type is not a true type, but merely + a notational convenience for setting up identifier columns + (similar to the <literal>AUTO_INCREMENT</literal> property + supported by some other databases). In the current + implementation, specifying <programlisting> CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( @@ -684,32 +685,49 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( </para> <para> + To use a <type>serial</type> column to insert the next value of + the sequence into the table, specify that the <type>serial</type> + column should be assigned the default value. This can be done + either be excluding from the column from the list of columns in + the <command>INSERT</command> statement, or through the use of + the <literal>DEFAULT</literal> keyword. + </para> + + <para> The type names <type>serial</type> and <type>serial4</type> are equivalent: both create <type>integer</type> columns. The type names <type>bigserial</type> and <type>serial8</type> work just the same way, except that they create a <type>bigint</type> column. <type>bigserial</type> should be used if you anticipate - the use of more than 2<superscript>31</> identifiers over the lifetime of the table. + the use of more than 2<superscript>31</> identifiers over the + lifetime of the table. </para> <para> - The sequence created by a <type>serial</type> type is automatically - dropped when - the owning column is dropped, and cannot be dropped otherwise. - (This was not true in <productname>PostgreSQL</productname> releases - before 7.3. Note that this automatic drop linkage will not occur for a - sequence created by reloading a dump from a pre-7.3 database; the dump - file does not contain the information needed to establish the dependency - link.) + The sequence created by a <type>serial</type> type is + automatically dropped when the owning column is dropped, and + cannot be dropped otherwise. (This was not true in + <productname>PostgreSQL</productname> releases before 7.3. Note + that this automatic drop linkage will not occur for a sequence + created by reloading a dump from a pre-7.3 database; the dump + file does not contain the information needed to establish the + dependency link.) Furthermore, this dependency between sequence + and column is made only for the <type>serial</> column itself; if + any other columns reference the sequence (perhaps by manually + calling the <function>nextval()</>) function), they may be broken + if the sequence is removed. Using <type>serial</> columns in + fashion is considered bad form. </para> - <note><para> - Prior to <productname>PostgreSQL</productname> 7.3, <type>serial</type> - implied <literal>UNIQUE</literal>. This is no longer automatic. If - you wish a serial column to be <literal>UNIQUE</literal> or a - <literal>PRIMARY KEY</literal> it must now be specified, same as with - any other data type. - </para></note> + <note> + <para> + Prior to <productname>PostgreSQL</> 7.3, <type>serial</type> + implied <literal>UNIQUE</literal>. This is no longer automatic. + If you wish a serial column to be <literal>UNIQUE</literal> or a + <literal>PRIMARY KEY</literal> it must now be specified, just as + with any other data type. + </para> + </note> </sect2> </sect1> @@ -794,14 +812,14 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( </thead> <tbody> <row> - <entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry> - <entry>fixed-length, blank padded</entry> - </row> - <row> <entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry> <entry>variable-length with limit</entry> </row> <row> + <entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry> + <entry>fixed-length, blank padded</entry> + </row> + <row> <entry><type>text</type></entry> <entry>variable unlimited length</entry> </row> @@ -817,29 +835,29 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( <para> <acronym>SQL</acronym> defines two primary character types: - <type>character(<replaceable>n</>)</type> and <type>character - varying(<replaceable>n</>)</type>, where <replaceable>n</> is a - positive integer. Both of these types can store strings up to + <type>character varying(<replaceable>n</>)</type> and + <type>character(<replaceable>n</>)</type>, where <replaceable>n</> + is a positive integer. Both of these types can store strings up to <replaceable>n</> characters in length. An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces, in which case - the string will be truncated to the maximum length. (This - somewhat bizarre exception is required by the - <acronym>SQL</acronym> standard.) If the string to be stored is - shorter than the declared length, values of type - <type>character</type> will be space-padded; values of type - <type>character varying</type> will simply store the shorter + the string will be truncated to the maximum length. (This somewhat + bizarre exception is required by the <acronym>SQL</acronym> + standard.) If the string to be stored is shorter than the declared + length, values of type <type>character</type> will be space-padded; + values of type <type>character varying</type> will simply store the + shorter string. </para> <note> <para> - If one explicitly casts a value to - <type>character(<replaceable>n</>)</type> or <type>character - varying(<replaceable>n</>)</type>, then an overlength value will - be truncated to <replaceable>n</> characters without raising an - error. (This too is required by the <acronym>SQL</acronym> - standard.) + If one explicitly casts a value to <type>character + varying(<replaceable>n</>)</type> or + <type>character(<replaceable>n</>)</type>, then an over-length + value will be truncated to <replaceable>n</> characters without + raising an error. (This too is required by the + <acronym>SQL</acronym> standard.) </para> </note> @@ -852,14 +870,14 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( </note> <para> - The notations <type>char(<replaceable>n</>)</type> and - <type>varchar(<replaceable>n</>)</type> are aliases for - <type>character(<replaceable>n</>)</type> and <type>character - varying(<replaceable>n</>)</type>, - respectively. <type>character</type> without length specifier is - equivalent to <type>character(1)</type>; if <type>character - varying</type> is used without length specifier, the type accepts - strings of any size. The latter is a <productname>PostgreSQL</> extension. + The notations <type>varchar(<replaceable>n</>)</type> and + <type>char(<replaceable>n</>)</type> are aliases for <type>character + varying(<replaceable>n</>)</type> and + <type>character(<replaceable>n</>)</type>, respectively. + <type>character</type> without length specifier is equivalent to + <type>character(1)</type>; if <type>character varying</type> is used + without length specifier, the type accepts strings of any size. The + latter is a <productname>PostgreSQL</> extension. </para> <para> @@ -943,19 +961,18 @@ SELECT b, char_length(b) FROM test2; <para> There are two other fixed-length character types in - <productname>PostgreSQL</productname>, shown in <xref linkend="datatype-character-special-table">. - The <type>name</type> type - exists <emphasis>only</emphasis> for storage of internal catalog - names and is not intended for use by the general user. Its length - is currently defined as 64 bytes (63 usable characters plus terminator) - but should be referenced using the constant - <symbol>NAMEDATALEN</symbol>. The length is set at compile time - (and is therefore adjustable for special uses); the default - maximum length may change in a future release. The type - <type>"char"</type> (note the quotes) is different from - <type>char(1)</type> in that it only uses one byte of storage. It - is internally used in the system catalogs as a poor-man's - enumeration type. + <productname>PostgreSQL</productname>, shown in <xref + linkend="datatype-character-special-table">. The <type>name</type> + type exists <emphasis>only</emphasis> for storage of internal + catalog names and is not intended for use by the general user. Its + length is currently defined as 64 bytes (63 usable characters plus + terminator) but should be referenced using the constant + <symbol>NAMEDATALEN</symbol>. The length is set at compile time (and + is therefore adjustable for special uses); the default maximum + length may change in a future release. The type <type>"char"</type> + (note the quotes) is different from <type>char(1)</type> in that it + only uses one byte of storage. It is internally used in the system + catalogs as a poor-man's enumeration type. </para> <table id="datatype-character-special-table"> @@ -1280,8 +1297,7 @@ SELECT b, char_length(b) FROM test2; fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of <replaceable>p</replaceable> is from 0 to 6 for the - <type>timestamp</type> and <type>interval</type> types, 0 to 13 - for the <type>time</type> types. + <type>timestamp</type> and <type>interval</type> types. </para> <note> @@ -1298,6 +1314,12 @@ SELECT b, char_length(b) FROM test2; </note> <para> + For the <type>time</type> types, the allowed range of + <replaceable>p</replaceable> is from 0 to 6 when eight-byte integer + storage is used, or from 0 to 10 when floating-point storage is used. + </para> + + <para> Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900's, @@ -1468,7 +1490,7 @@ SELECT b, char_length(b) FROM test2; <para> The <type>time</type> type can be specified as <type>time</type> or as <type>time without time zone</type>. The optional precision - <replaceable>p</replaceable> should be between 0 and 13, and + <replaceable>p</replaceable> should be between 0 and 6, and defaults to the precision of the input time literal. </para> |