From 526427f6d3901274d4058ec411cdfd930e2f1662 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 13 Jan 2001 18:34:51 +0000 Subject: [PATCH] Add information about bit types. Adjust some other things to promote SQL type names over internal type names. --- doc/src/sgml/datatype.sgml | 487 ++++++++++++++++++++++++--------------------- doc/src/sgml/func.sgml | 129 ++++++++---- doc/src/sgml/syntax.sgml | 4 +- 3 files changed, 356 insertions(+), 264 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index a9ba42de14..c0f77a2ab4 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,17 +1,10 @@ Data Types - - - Describes the built-in data types available in - Postgres. - - - Postgres has a rich set of native data types available to users. @@ -20,229 +13,229 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.46 2000/12/22 18:57:49 pe - In the context of data types, the following sections will discuss - SQL standards compliance, porting issues, and usage. - - Some Postgres types correspond directly to - SQL92-compatible types. In other - cases, data types defined by SQL92 syntax are mapped directly - into native Postgres types. - - Many of the built-in types have obvious external formats. However, several - types are either unique to Postgres, - such as open and closed paths, or have - several possibilities for formats, such as the date and time types. + shows all general-purpose data types + available to users. Most of the alternative names listed in the + Aliases column are the names used internally by + Postgres 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 Postgres, such as open and closed + paths, or have several possibilities for formats, such as the date + and time types. - +
<productname>Postgres</productname> Data TypesData Types - Postgres Type - SQL92 or SQL99 Type + Type Name + Aliases Description + - bool - boolean - logical boolean (true/false) + bigint + int8 + signed eight-byte integer + - box + bit - rectangular box in 2D plane + fixed-length bit string + - char(n) - character(n) - fixed-length character string + bit varying(n) + varbit(n) + variable-length bit string + - cidr - - IP network address + boolean + bool + logical boolean (true/false) + - circle + box - circle in 2D plane + rectangular box in 2D plane + - date - date - calendar date without time of day + character(n) + char(n) + fixed-length character string + - decimal - decimal(p,s) - exact numeric with selectable precision + character varying(n) + varchar(n) + variable-length character string + - float4 - float(p), p < 7 - floating-point number with precision p + cidr + + IP network address + - float8 - float(p), 7 <= p < 16 - floating-point number with precision p + circle + + circle in 2D plane + - inet + date - IP network or host address + calendar date (year, month, day) + - int2 - smallint - signed two-byte integer + double precision + float8 + double precision floating-point number + - int4 - int, integer - signed 4-byte integer + inet + + IP host address + - int8 - - signed 8-byte integer + integer + int, int4 + signed four-byte integer + - interval - interval + interval + general-use time span + - line + line infinite line in 2D plane + - lseg + lseg line segment in 2D plane + - money - decimal(9,2) - US-style currency + macaddr + + MAC address + - numeric - numeric(p,s) - exact numeric with selectable precision + money + + US-style currency + - path - - open and closed geometric path in 2D plane + numeric(p, s) + decimal(p, s) + exact numeric with selectable precision + - point + oid - geometric point in 2D plane + object identifier + - polygon + path - closed geometric path in 2D plane + open and closed geometric path in 2D plane + - serial + point - unique id for indexing and cross-reference + geometric point in 2D plane + - text + polygon - variable-length character string + closed geometric path in 2D plane + - time - time [ without time zone ] - time of day + real + float4 + single precision floating-point number + - timetz - time with time zone - time of day, including time zone + smallint + int2 + signed two-byte integer + - timestamp - timestamp [ with time zone ] - date/time + serial + + autoincrementing four-byte integer + - varchar(n) - character varying(n) + text + variable-length character string - - -
- - - - - - The cidr and inet types are designed to handle any IP type - but only ipv4 is handled in the current implementation. - Everything here that talks about ipv4 will apply to ipv6 in a - future release. - - - - - - <productname>Postgres</productname> Function Constants - Constants - - - Postgres Function - SQL92 Constant - Description - - - - - date('now') - current_date - date of current transaction + time [ without time zone ] + + time of day + - time('now') - current_time - time of current transaction + time with time zone + + time of day, including time zone + - timestamp('now') - current_timestamp - date and time of current transaction + timestamp [ with time zone ] + + date and time
- - Postgres has features at the forefront of - ORDBMS development. In addition to - SQL99 conformance, substantial portions - of SQL92 are also supported. - Although we strive for SQL92 compliance, - there are some aspects of the standard - that are ill considered and which should not live through subsequent standards. - Postgres will not make great efforts to - conform to these features; however, these tend to apply in little-used - or obsure cases, and a typical user is not likely to run into them. - + + Compatibility + + The following types (or spellings thereof) are specified by SQL: + bit, bit varying, boolean, + char, character, character + varying, varchar, date, + double precision, integer, + interval, numeric, decimal, + real, smallint, time, + timestamp (both with or without time zone). + + Most of the input and output functions corresponding to the @@ -259,19 +252,9 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.46 2000/12/22 18:57:49 pe 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. - - - - Floating point numbers are allowed to retain - most of the intrinsic precision of the type (typically 15 digits for doubles, - 6 digits for 4-byte floats). - Other types with underlying floating point fields (e.g. geometric - types) carry similar precision. - - - + Numeric Types @@ -293,42 +276,32 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.46 2000/12/22 18:57:49 pe Range + - decimal - variable - User-specified precision - no limit - - - float4 - 4 bytes - Variable-precision - 6 decimal places - - - float8 - 8 bytes - Variable-precision - 15 decimal places - - - int2 + smallint 2 bytes Fixed-precision -32768 to +32767 - int4 + integer 4 bytes Usual choice for fixed-precision -2147483648 to +2147483647 - int8 + bigint 8 bytes Very large range fixed-precision - ~18 decimal places + about 18 decimal places + + + + decimal + variable + User-specified precision + no limit numeric @@ -336,6 +309,20 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.46 2000/12/22 18:57:49 pe User-specified precision no limit + + + real + 4 bytes + Variable-precision + 6 decimal places + + + double precision + 8 bytes + Variable-precision + 15 decimal places + + serial 4 bytes @@ -353,11 +340,11 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.46 2000/12/22 18:57:49 pe - The int8 type may not be available on all platforms since + The bigint type may not be available on all platforms since it relies on compiler support for eight-byte integers. - + The Serial Type @@ -403,7 +390,7 @@ CREATE TABLE tablename ( - + Monetary Type @@ -455,15 +442,15 @@ CREATE TABLE tablename ( - + Character Types - SQL92 defines two primary character types: - char and varchar. + SQL defines two primary character types: + character and character varying. Postgres supports these types, in addition to the more general text type, - which unlike varchar + which unlike character varying does not require an explicit declared upper limit on the size of the field. @@ -483,16 +470,16 @@ CREATE TABLE tablename ( - "char" - 1 byte - SQL92-compatible - Single character + character(n), char(n) + (4+n) bytes + SQL-compatible + Fixed-length blank padded - char(n) + character varying(n), varchar(n) (4+n) bytes - SQL92-compatible - Fixed-length blank padded + SQL-compatible + Variable-length with limit text @@ -500,26 +487,32 @@ CREATE TABLE tablename (Most flexible Variable unlimited length - - varchar(n) - (4+n) bytes - SQL92-compatible - Variable-length with limit - + + + + Although the type text is not SQL-compliant, many + other RDBMS packages have it as well. + + - There is one other fixed-length character type in Postgres. - The name type exists only for - storage of internal catalog names and - is not intended for use by the general user. - Its length is currently defined as 32 bytes (31 characters plus terminator) - but should be reference using NAMEDATALEN. - The length is set at compile time (and is therefore adjustable for - special uses); the default maximum length may change in a future release. + There are two other fixed-length character types in + Postgres. The name type + exists only for storage of internal catalog + names and is not intended for use by the general user. Its length + is currently defined as 32 bytes (31 characters plus terminator) + but should be referenced using the macro + NAMEDATALEN. 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 + "char" (note the quotes) is different from + char(1) in that it only uses one byte of storage. It + is internally used in the system catalogs as a poor-man's + enumeration type. @@ -536,6 +529,11 @@ CREATE TABLE tablename ( + "char" + 1 byte + Single character internal type + + name 32 bytes Thirty-one character internal type @@ -547,7 +545,7 @@ CREATE TABLE tablename ( - + Date/Time Types @@ -641,7 +639,7 @@ CREATE TABLE tablename ( - + Date/Time Input @@ -658,7 +656,7 @@ CREATE TABLE tablename ( - See + See for the exact parsing rules of date/time input and for the recognized time zones. @@ -956,7 +954,7 @@ CREATE TABLE tablename ( - Refer to for + Refer to for more examples of time zones. @@ -984,8 +982,8 @@ January 8 04:05:06 1999 PST - - <productname>Postgres</productname> Time Zone Input +
+ <productname>Postgres</productname> Time Zone InputTime Zone Inputs @@ -1112,7 +1110,7 @@ January 8 04:05:06 1999 PST - + Date/Time Output @@ -1231,7 +1229,7 @@ January 8 04:05:06 1999 PST - + Time Zones @@ -1351,7 +1349,7 @@ January 8 04:05:06 1999 PST - + Internals @@ -1370,22 +1368,21 @@ January 8 04:05:06 1999 PST - + Boolean Type - Postgres supports bool as - the SQL99 boolean type. - bool can have one of only two states: 'true' or 'false'. - A third state, 'unknown', is not - implemented and is not suggested in SQL99; - NULL is an - effective substitute. bool can be used in any boolean expression, - and boolean expressions - always evaluate to a result compatible with this type. + Postgres supports the + SQL99 boolean type. + boolean can have one of only two states: 'true' or + 'false'. A third state, 'unknown', is represented by the SQL NULL + state. boolean can be used in any boolean expression, + and boolean expressions always evaluate to a result compatible + with this type. + - bool uses 1 byte of storage. + boolean uses 1 byte of storage. @@ -1417,7 +1414,7 @@ January 8 04:05:06 1999 PST - + Geometric Types @@ -1746,7 +1743,7 @@ January 8 04:05:06 1999 PST - + Network Address Data Types @@ -1755,7 +1752,7 @@ January 8 04:05:06 1999 PST types, because these types offer input error checking and several specialized operators and functions. -
+
Network Address Data Types @@ -1799,7 +1796,7 @@ January 8 04:05:06 1999 PST - + <type>inet</type> @@ -1825,7 +1822,7 @@ January 8 04:05:06 1999 PST - + <type>cidr</> @@ -1918,7 +1915,7 @@ January 8 04:05:06 1999 PST - + <type>inet</type> vs <type>cidr</type> @@ -1936,7 +1933,7 @@ January 8 04:05:06 1999 PST - + <type>macaddr</></> <para> @@ -1955,6 +1952,40 @@ January 8 04:05:06 1999 PST </sect1> + <sect1 id="datatype-bit"> + <title>Bit String Types + + + Bit strings are strings of 1's and 0's. They can be used to store + or visualize bit masks. There are two SQL bit types: + BIT(x) and BIT + VARYING(x); the + x specifies the maximum length. + BIT type data is automatically padded with 0's on the + right to the maximum length, BIT VARYING is of + variable length. BIT without length is requivalent + to BIT(1), BIT VARYING means + unlimited length. Input data that is longer than the allowed + length will be truncated. Refer to for information about the syntax + of bit string constants. Bit-logical operators and string + manipulation functions are available; see . + + + + + Some examples: + +CREATE TABLE test (a BIT(3), b BIT VARYING(5)); +INSERT INTO test VALUES (B'101', B'00'); +SELECT SUBSTRING(b FROM 1 FOR 2) FROM test; + + + + + + + Functions and Operators @@ -184,42 +184,102 @@ @ -5.0 5.0 + + + & + Binary AND + 91 & 15 + 11 + + + + | + Binary OR + 32 | 3 + 35 + + + + # + Binary XOR + 17 # 5 + 20 + + + + ~ + Binary NOT + ~1 + -2 + + + + << + Binary shift left + 1 << 4 + 16 + + + + >> + Binary shift right + 8 >> 2 + 2 + +
- + + The binary operators are also available for the bit + string types BIT and BIT VARYING. + + + Bit String Binary Operators + + + + + Example + Result + + + + + + B'10001' & B'01101' + 00001 + + + B'10001' | B'01101' + 11101 + + + B'10001' # B'01101' + 11110 + + + ~ B'10001' + 01110 + + + B'10001' << 3 + 01000 + + + B'10001' >> 2 + 00100 + + + +
+ + Bit string arguments to &, |, + and # must be of equal length. When bit + shifting, the original length of the string is preserved, as shown + here. + Mathematical Functions @@ -484,7 +544,8 @@ wary of potential effects of the automatic padding when using the CHARACTER type. Generally the functions described here also work on data of non-string types by converting that data - to a string representation first. + to a string representation first. Some functions also exist + natively for bit string types. diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index d8cd5f18a2..45b893e1c5 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ @@ -232,7 +232,7 @@ SELECT 'foo' 'bar'; - + Bit String Constants -- 2.11.0