GNU/Linux man pages
Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :
GNU/Linux |
RedHat 5.2(Apollo) |
|
 |
pgbuiltin(3) |
 |
This section describes the data types, functions and operators available to users in Postgres as it is distributed.
Built-in types are installed in every database. psql has a \d command to show these types.
Users may add new types to Postgres using the define type command described in this manual.
There are some data types defined by SQL/92 syntax which are mapped directly into native Postgres types. Note that the "exact numerics" decimal and numeric have fully implemented syntax but currently (postgres v6.2) support only a limited range of the values allowed by SQL/92.
|
POSTGRES Type |
SQL/92 Type |
Meaning | |
|
char(n) |
character(n) |
fixed-length character string | |
|
varchar(n) |
character varying(n) |
variable-length character string | |
|
float4/8 |
float(p) |
floating-point number with precision p | |
|
float8 |
double precision |
double-precision floating-point number | |
|
float8 |
real |
double-precision floating-point number | |
|
int2 |
smallint |
signed two-byte integer | |
|
int4 |
int |
signed 4-byte integer | |
|
int4 |
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 | |
|
timestamp |
timestamp with time zone |
date/time | |
|
timespan |
interval |
general-use time span |
There are some constants and functions defined in SQL/92.
|
SQL/92 Function |
Meaning |
||
|
current_date |
date of current transaction |
||
|
current_time |
time of current transaction |
||
|
current_timestamp |
date and time of current transaction |
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.
Most date and time types share code for data input. For those types ( datetime, abstime, timestamp, timespan, reltime, date, and time) the input can have any of a wide variety of styles. For numeric date representations, European and US conventions can differ, and the proper interpretation is obtained by using the set(l) command before entering data. Output formats can be set to one of three styles: ISO-8601, SQL (traditional Oracle/Ingres), and traditional Postgres (see section on absolute time) with the SQL style having European and US variants (see set(l)).
In future releases, the number of date/time types will decrease, with the current implementation of datetime becoming timestamp, timespan becoming interval, and (possibly) abstime and reltime being deprecated in favor of timestamp and interval.
General-use date and time is input using a wide range of styles, including ISO-compatible, SQL-compatible, traditional Postgres (see section on absolute time) and other permutations of date and time. Output styles can be ISO-compatible, SQL-compatible, or traditional Postgres, with the default set to be compatible with Postgres v6.0.
datetime is specified using the following syntax:
Year-Month-Day
[ Hour : Minute : Second ] [AD,BC] [ Timezone ]
YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone
]
where
|
Year is 4013 BC, ..., very large | |
|
Month is Jan, Feb, ..., Dec or 1, 2, ..., 12 | |
|
Day is 1, 2, ..., 31 | |
|
Hour is 00, 02, ..., 23 | |
|
Minute is 00, 01, ..., 59 | |
|
Second is 00, 01, ..., 59 (60 for leap second) | |
|
Timezone is 3 characters or ISO offset to GMT |
Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future. Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time). Dates are stored internally in Greenwich Mean Time. Input and output routines translate time to the local time zone of the server.
The special values ’current’, ’infinity’ and ’-infinity’ are provided. ’infinity’ specifies a time later than any valid time, and ’-infinity’ specifies a time earlier than any valid time. ’current’ indicates that the current time should be substituted whenever this value appears in a computation.
The strings ’now’, ’today’, ’yesterday’, ’tomorrow’, and ’epoch’ can be used to specify time values. ’now’ means the current time, and differs from ’current’ in that the current time is immediately substituted for it. ’epoch’ means Jan 1 00:00:00 1970 GMT.
General-use time span is input using a wide range of syntaxes, including ISO-compatible, SQL-compatible, traditional Postgres (see section on relative time ) and other permutations of time span. Output formats can be ISO-compatible, SQL-compatible, or traditional Postgres, with the default set to be Postgres-compatible. Months and years are a "qualitative" time interval, and are stored separately from the other "quantitative" time intervals such as day or hour. For date arithmetic, the qualitative time units are instantiated in the context of the relevant date or time.
Time span is specified with the following syntax:
Quantity Unit
[Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
where
|
Quantity is ..., ’-1’, ’0’, ’1’, ’2’, ... | |
|
Unit is ’second’, ’minute’, ’hour’, ’day’, ’week’, ’month’, ’year’, | |
|
or abbreviations or plurals of these units. | |
|
Direction is ’ago’. |
Absolute time (abstime) is a limited-range (+/- 68 years) and limited-precision (1 sec) date data type. datetime may be preferred, since it covers a larger range with greater precision.
Absolute time is specified using the following syntax:
Month Day [ Hour : Minute : Second ] Year [ Timezone ]
where
|
Month is Jan, Feb, ..., Dec | |
|
Day is 1, 2, ..., 31 | |
|
Hour is 01, 02, ..., 24 | |
|
Minute is 00, 01, ..., 59 | |
|
Second is 00, 01, ..., 59 | |
|
Year is 1901, 1902, ..., 2038 |
Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04 2038 GMT. As of Version 3.0, times are no longer read and written using Greenwich Mean Time; the input and output routines default to the local time zone.
All special values allowed for datetime are also allowed for absolute time.
Relative time (reltime) is a limited-range (+/- 68 years) and limited-precision (1 sec) time span data type. timespan may be preferred, since it covers a larger range with greater precision, allows multiple units for an entry, and correctly handles qualitative time units such as year and month. For reltime, only one quantity and unit is allowed per entry, which can be inconvenient for complicated time spans.
Relative time is specified with the following syntax:
@ Quantity Unit [Direction]
where
|
Quantity is ’1’, ’2’, ... | |
|
Unit is ’’second’’, ’’minute’’, ’’hour’’, ’’day’’, ’’week’’, | |
|
’’month’’ (30-days), or ’’year’’ (365-days), | |
|
or PLURAL of these units. | |
|
Direction is ’’ago’’ |
(Note: Valid relative times are less than or equal to 68 years.) In addition, the special relative time “Undefined RelTime” is provided.
This is currently a limited-range absolute time which closely resembles the abstime data type. It shares the general input parser with the other date/time types. In future releases this type will absorb the capabilities of the datetime type and will move toward SQL92 compliance.
timestamp is specified using the same syntax as for datetime.
Time ranges are specified as:
[
’abstime’ ’abstime’]
where abstime is a time in the absolute time format.
Special abstime values such as “current”,
“infinity” and “-infinity” can be
used.
Points are specified using the following syntax:
( 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 |
Line segments are represented by pairs of points.
lseg is specified using the following syntax:
( ( x1 , y1 ) ,
( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
where
|
(x1,y1) and (x2,y2) are the endpoints of the segment |
Boxes are represented by pairs of points which are opposite corners of the box.
box is specified using the following syntax:
( ( x1 , y1 ) ,
( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
where
|
(x1,y1) and (x2,y2) are opposite corners |
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.
Paths are represented by 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 popen(p) and pclose(p) are supplied to force a path to be open or closed, and functions isopen(p) and isclosed(p) are supplied to select either type in a query.
path is specified using the following syntax:
( ( 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 |
Paths are output using the first syntax. Note that Postgres 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 upgradepath() is supplied to convert paths dumped and reloaded from pre-v6.1 databases.
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.
polygon is specified using the following syntax:
( ( 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 |
||
|
Polygons are output using the first syntax. The last format is supplied to be backward compatible with v6.0 and earlier path formats and will not be supported in future versions of Postgres. |
||
|
a single leading "(" indicates a v6.0-compatible format ( x1 , ... , xn , y1 , ... , yn ) Note that Postgres 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 upgradepoly() is supplied to convert polygons dumped and reloaded from pre-v6.1 databases. |
Circles are represented by a center point and a radius.
circle is specified using the following syntax:
< ( 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 |
Circles are output using the first syntax.
Postgres provides a large number of built-in operators on system types. These operators are declared in the system catalog “pg_operator”. Every entry in “pg_operator” includes the object ID of the procedure that implements the operator.
Users may invoke operators using the operator name, as in:
select * from emp where salary < 40000;
Alternatively, users may call the functions that implement the operators directly. In this case, the query above would be expressed as:
select * from emp where int4lt(salary, 40000);
psql has a \d command to show these operators.
Many data types have functions available for conversion to other related types. In addition, there are some type-specific functions. Functions which are also available through operators are documented as operators only.
Some functions defined for text are also available for char() and varchar().
For the date_part() and date_trunc() functions, arguments can be ’year’, ’month’, ’day’, ’hour’, ’minute’, and ’second’, as well as the more specialized quantities ’decade’, ’century’, ’millenium’, ’millisecond’, and ’microsecond’. date_part() allows ’dow’ to return day of week and ’epoch’ to return seconds since 1970 for datetime and ’epoch’ to return total elapsed seconds for timespan.
Functions:
integer
float8 float(int) convert integer to floating point
float4 float4(int) convert integer to floating point
float
int integer(float) convert floating point to integer
text
text lower(text) convert text to lower case
text lpad(text,int,text) left pad string to specified length
text ltrim(text,text) left trim characters from text
text position(text,text) extract specified substring
text rpad(text,int,text) right pad string to specified
length
text rtrim(text,text) right trim characters from text
text substr(text,int[,int]) extract specified substring
text upper(text) convert text to upper case
abstime
|
bool isfinite(abstime) TRUE if this is a finite time | |
|
datetime datetime(abstime) convert to datetime |
date
|
datetime datetime(date) convert to datetime | |
|
datetime datetime(date,time) convert to datetime |
datetime
|
timespan age(datetime,datetime) date difference preserving months and years | |
|
float8 date_part(text,datetime) specified portion of date field | |
|
datetime date_trunc(text,datetime) truncate date at specified units | |
|
bool isfinite(datetime) TRUE if this is a finite time | |
|
abstime abstime(datetime) convert to abstime |
reltime
|
timespan timespan(reltime) convert to timespan |
time
|
datetime datetime(date,time) convert to datetime |
timespan
|
float8 date_part(text,timespan) specified portion of time field | |
|
bool isfinite(timespan) TRUE if this is a finite time | |
|
reltime reltime(timespan) convert to reltime |
box
|
box box(point,point) convert points to box | |
|
float8 area(box) area of box |
path
|
bool isopen(path) TRUE if this is an open path | |
|
bool isclosed(path) TRUE if this is a closed path |
circle
|
circle circle(point,float8) convert to circle | |
|
polygon polygon(npts,circle) convert to polygon with npts points | |
|
float8 center(circle) radius of circle | |
|
float8 radius(circle) radius of circle | |
|
float8 diameter(circle) diameter of circle | |
|
float8 area(circle) area of circle |
SQL/92 defines functions with specific syntax. Some of these are implemented using other Postgres functions.
SQL/92 Functions:
text
text position(text in text) extract specified substring
text substring(text [from int] [for int])
extract specified substring
text trim([leading|trailing|both] [text] from text)
trim characters from text
psql has a variety of \d commands for showing system information. Consult those psql commands for more listings.
set(l), show(l), reset(l), psql(1). For examples on specifying literals of built-in types, see SQL(l).
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.
|
pgbuiltin(3) | |