| PostgreSQL | ||
|---|---|---|
| Prev | Next | |
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) or 'epoch' to return total elapsed seconds (for timespan).
Table 10-1. Mathematical Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| float(int) | float8 | convert integer to floating point | float(2) |
| float4(int) | float4 | convert integer to floating point | float4(2) |
| int | integer(float) | convert floating point to integer | integer(2.0) |
Many of the string functions are available for text, varchar(), and char() types. At the moment, some functions are available only for the text type.
Table 10-2. String Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| lower(text) | text | convert text to lower case | lower('TOM') |
| lpad(text,int,text) | text | left pad string to specified length | lpad('hi',4,'??') |
| ltrim(text,text) | text | left trim characters from text | ltrim('xxxxtrim','x') |
| position(text,text) | text | extract specified substring | position('high','ig') |
| rpad(text,int,text) | text | right pad string to specified length | rpad('hi',4,'x') |
| rtrim(text,text) | text | right trim characters from text | rtrim('trimxxxx','x') |
| substr(text,int[,int]) | text | extract specified substring | substr('hi there',3,5) |
| upper(text) | text | convert text to upper case | upper('tom') |
Table 10-3. Date/Time Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| isfinite(abstime) | bool | TRUE if this is a finite time | isfinite('now'::abstime) |
| datetime(abstime) | datetime | convert to datetime | datetime('now'::abstime) |
| datetime(date) | datetime | convert to datetime | datetime('today'::date) |
| datetime(date,time) | datetime | convert to datetime | datetime('1998-02-24':datetime, '23:07'::time); |
| age(datetime,datetime) | timespan | span preserving months and years | age('now','1957-06-13':datetime) |
| date_part(text,datetime) | float8 | specified portion of date field | date_part('dow','now'::datetime) |
| date_trunc(text,datetime) | datetime | truncate date at specified units | date_trunc('month','now'::abstime) |
| isfinite(datetime) | bool | TRUE if this is a finite time | isfinite('now'::datetime) |
| abstime(datetime) | abstime | convert to abstime | abstime('now'::datetime) |
| timespan(reltime) | timespan | convert to timespan | timespan('4 hours'::reltime) |
| datetime(date,time) | datetime | convert to datetime | datetime('1998-02-25'::date,'06:41'::time) |
| date_part(text,timespan) | float8 | specified portion of time field | date_part('hour','4 hrs 3 mins'::timespan) |
| isfinite(timespan) | bool | TRUE if this is a finite time | isfinite('4 hrs'::timespan) |
| reltime(timespan) | reltime | convert to reltime | reltime('4 hrs'::timespan) |
Table 10-4. Geometric Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| box(point,point) | box | convert points to box | box('(0,0)'::point,'(1,1)'::point) |
| area(box) | float8 | area of box | area('((0,0),(1,1))'::box) |
| isopen(path) | bool | TRUE if this is an open path | isopen('[(0,0),(1,1),(2,0)]'::path) |
| isclosed(path) | bool | TRUE if this is a closed path | isclosed('((0,0),(1,1),(2,0))'::path) |
| circle(point,float8) | circle | convert to circle | circle('(0,0)'::point,2.0) |
| polygon(npts,circle) | polygon | convert to polygon with npts points | polygon(12,'((0,0),2.0)'::circle) |
| center(circle) | float8 | center of object | center('((0,0),2.0)'::circle) |
| radius(circle) | float8 | radius of circle | radius('((0,0),2.0)'::circle) |
| diameter(circle) | float8 | diameter of circle | diameter('((0,0),2.0)'::circle) |
| area(circle) | float8 | area of circle | area('((0,0),2.0)'::circle) |
SQL92 defines functions with specific syntax. Some of these are implemented using other Postgres functions.
Table 10-5. SQL92 Text Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| position(text in text) | int4 | extract specified substring | position('o' in 'Tom') |
| substring(text [from int] [for int]) | text | extract specified substring | substring('Tom' from 2 for 2) |
| trim([leading|trailing|both] [text] from text) | text | trim characters from text | trim(both 'x' from 'xTomx') |
| Prev | Home | Next |
| Operators | Up | Arrays |