| PostgreSQL | ||
|---|---|---|
| Prev | Next | |
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 define type command described elsewhere.
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 date and time types.
Table 8-1. Postgres Data Types
Postgres | SQL92 SQL3 | Description |
|---|---|---|
| bool | boolean | logical boolean (true/false) |
| box | rectangular box in 2D plane | |
| char(n) | character(n) | fixed-length character string |
| circle | circle in 2D plane | |
| date | date | calendar date without time of day |
| float4/8 | float(p) | floating-point number with precision p |
| float8 | real, double precision | double-precision floating-point number |
| int2 | smallint | signed two-byte integer |
| int4 | int, integer | signed 4-byte integer |
| int4 | decimal(p,s) | exact numeric for p <= 9, s = 0 |
| int4 | numeric(p,s) | exact numeric for p == 9, s = 0 |
| line | infinite line in 2D plane | |
| lseg | line segment in 2D plane | |
| money | decimal(9,2) | US-style currency |
| path | open and closed geometric path in 2D plane | |
| point | geometric point in 2D plane | |
| polygon | closed geometric path in 2D plane | |
| time | time | time of day |
| timespan | interval | general-use time span |
| timestamp | timestamp with time zone | date/time |
| varchar(n) | character varying(n) | variable-length character string |
Table 8-2. Postgres Function Constants
Postgres | SQL92 | Description |
|---|---|---|
| getpgusername() | current_user | user name in current session |
| date('now') | current_date | date of current transaction |
| time('now') | current_time | time of current transaction |
| timestamp('now') | current_timestamp | date 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 cases in the standard which are ill considered and which should not live through subsequent standards. Postgres will not make great efforts to conform to these cases. However, these cases tend to be little-used and obsure, and a typical user is not likely to run into them.
Although most of the input and output functions corresponding to the base types (e.g., integers and floating point numbers) do some error-checking, some are not particularly rigorous about it. More importantly, few of the operators and functions (e.g., addition and multiplication) perform any error-checking at all. Consequently, many of the numeric operators can (for example) 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.
| Prev | Home | Next |
| Destroying a Database | Up | Numeric Types |