Online Documentation Server
 ПОИСК
ods.com.ua Web
 КАТЕГОРИИ
Home
Programming
Net technology
Unixes
Security
RFC, HOWTO
Web technology
Data bases
Other docs

 


 ПОДПИСКА

 О КОПИРАЙТАХ
Вся предоставленная на этом сервере информация собрана нами из разных источников. Если Вам кажется, что публикация каких-то документов нарушает чьи-либо авторские права, сообщите нам об этом.




Data Types

Chapter 3. Data Types

Describes the built-in data types available in Postgres.

Postgres has a rich set of native data types available to users. Users may add new types to Postgres using the CREATE TYPE command.

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.

Table 3-1. Postgres Data Types

Postgres TypeSQL92 or SQL3 TypeDescription
boolbooleanlogical boolean (true/false)
box rectangular box in 2D plane
char(n)character(n)fixed-length character string
cidr IP version 4 network or host address
circle circle in 2D plane
datedatecalendar date without time of day
decimaldecimal(p,s)exact numeric for p <= 9, s = 0
float4float(p), p < 7floating-point number with precision p
float8float(p), 7 <= p < 16floating-point number with precision p
inet IP version 4 network or host address
int2smallintsigned two-byte integer
int4int, integersigned 4-byte integer
int8 signed 8-byte integer
intervalintervalgeneral-use time span
line infinite line in 2D plane
lseg line segment in 2D plane
moneydecimal(9,2)US-style currency
numericnumeric(p,s)exact numeric for p == 9, s = 0
path open and closed geometric path in 2D plane
point geometric point in 2D plane
polygon closed geometric path in 2D plane
serial unique id for indexing and cross-reference
timetimetime of day
timetztime with time zonetime of day, including time zone
timestamptimestamp with time zonedate/time
varchar(n)character varying(n)variable-length character string

Note: 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.

Table 3-2. Postgres Function Constants

Postgres FunctionSQL92 ConstantDescription
getpgusername()current_useruser name in current session
date('now')current_datedate of current transaction
time('now')current_timetime of current transaction
timestamp('now')current_timestampdate and time of current transaction

Postgres has features at the forefront of ORDBMS development. In addition to SQL3 conformance, substantial portions of SQL92 are also supported. Although we strive for SQL92 compliance, there are some aspects of the standard which 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.

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 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.

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.

Note: 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

Numeric types consist of two- and four-byte integers, four- and eight-byte floating point numbers and fixed-precision decimals.

Table 3-3. Postgres Numeric Types

Numeric TypeStorageDescriptionRange
decimalvariableUser-specified precision~8000 digits
float44 bytesVariable-precision6 decimal places
float88 bytesVariable-precision15 decimal places
int22 bytesFixed-precision-32768 to +32767
int44 bytesUsual choice for fixed-precision-2147483648 to +2147483647
int88 bytesVery large range fixed-precision+/- > 18 decimal places
numericvariableUser-specified precisionno limit
serial4 bytesIdentifer or cross-reference0 to +2147483647

The numeric types have a full set of corresponding arithmetic operators and functions. Refer to Numerical Operators and Mathematical Functions for more information.

The int8 type may not be available on all platforms since it relies on compiler support for this.

The Serial Type

The serial type is a special-case type constructed by Postgres from other existing components. It is typically used to create unique identifiers for table entries. In the current implementation, specifying

CREATE TABLE tablename (colname SERIAL);
     
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename
    (colname INT4 DEFAULT nextval('tablename_colname_seq');
CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
     
Caution

The implicit sequence created for the serial type will not be automatically removed when the table is dropped.

Implicit sequences supporting the serial are not automatically dropped when a table containing a serial type is dropped. So, the following commands executed in order will likely fail:
CREATE TABLE tablename (colname SERIAL);
DROP TABLE tablename;
CREATE TABLE tablename (colname SERIAL);
     
The sequence will remain in the database until explicitly dropped using DROP SEQUENCE.



With any suggestions or questions please feel free to contact us