PostgreSQL 常用関数
PostgreSQL 組み込み関数は集約関数とも呼ばれ、文字列または数値データに対して処理を実行するために使用されます。
以下は、一般的な PostgreSQL 組み込み関数のリストです：
- COUNT 関数：データベーステーブルの行数を計算するために使用されます。
- MAX 関数：特定の列の最大値を照会するために使用されます。
- MIN 関数：特定の列の最小値を照会するために使用されます。
- AVG 関数：特定の列の平均値を計算するために使用されます。
- SUM 関数：数値列のすべての値の合計を計算するために使用されます。
- ARRAY 関数：入力値（nullを含む）を配列に追加するために使用されます。
- Numeric 関数：SQL で必要とされるオペランドの関数を完全に一覧表示します。
- String 関数：SQL で必要とされる文字を操作する関数を完全に一覧表示します。
数学関数
以下は PostgreSQL で提供される数学関数のリストです。注意すべき点として、これらの関数の多くは複数の形式が存在し、違いはパラメータの型だけです。特に指定がない限り、特定の形式の関数はパラメータと同じデータ型を返します。
| 関数 | 戻り値の型 | 説明 | 例 | 結果 |
|---|---|---|---|---|
| abs(x) | 絶対値 | abs(-17.4) | 17.4 | |
| cbrt(double) | 立方根 | cbrt(27.0) | 3 | |
| ceil(double/numeric) | 引数以上の最小の整数 | ceil(-42.8) | -42 | |
| degrees(double) | ラジアンを度に変換 | degrees(0.5) | 28.6478897565412 | |
| exp(double/numeric) | 自然指数 | exp(1.0) | 2.71828182845905 | |
| floor(double/numeric) | 引数以下の最大の整数 | floor(-42.8) | -43 | |
| ln(double/numeric) | 自然対数 | ln(2.0) | 0.693147180559945 | |
| log(double/numeric) | 10を底とする対数 | log(100.0) | 2 | |
| log(b numeric,x numeric) | numeric | 指定された底の対数 | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | 剰余を求める | mod(9,4) | 1 | |
| pi() | double | "π" 定数 | pi() | 3.14159265358979 |
| power(a double, b double) | double | a の b 乗を求める | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | a の b 乗を求める | power(9.0, 3.0) | 729 |
| radians(double) | double | 度をラジアンに変換 | radians(45.0) | 0.785398163397448 |
| random() | double | 0.0 から 1.0 までの間の乱数値 | random() | |
| round(double/numeric) | 最も近い整数に丸める | round(42.4) | 42 | |
| round(v numeric, s int) | numeric | s 桁の小数に丸める | round(42.438,2) | 42.44 |
| sign(double/numeric) | 引数の符号（-1, 0, +1） | sign(-8.4) | -1 | |
| sqrt(double/numeric) | 平方根 | sqrt(2.0) | 1.4142135623731 | |
| trunc(double/numeric) | 切り捨て（ゼロに向かって） | trunc(42.8) | 42 | |
| trunc(v numeric, s int) | numeric | s 桁の小数位置で切り捨て | trunc(42.438,2) | 42.43 |
三角関数リスト
| 関数 | 説明 |
|---|---|
| acos(x) | 逆余弦 |
| asin(x) | 逆正弦 |
| atan(x) | 逆正接 |
| atan2(x, y) | y/x の逆正接 |
| cos(x) | 余弦 |
| cot(x) | 余接 |
| sin(x) | 正弦 |
| tan(x) | 正接 |
文字列関数と演算子
以下は PostgreSQL で提供される文字列演算子のリストです：
| 関数 | 戻り値の型 | 説明 | 例 | 結果 |
|---|---|---|---|---|
| string || string | text | 文字列連結 | 'Post' || 'greSQL' | PostgreSQL |
| bit_length(string) | int | 文字列内のビット数 | bit_length('jose') | 32 |
| char_length(string) | int | 文字列内の文字数 | char_length('jose') | 4 |
| convert(string using conversion_name) | text | 指定された変換名を使用してエンコーディングを変更します。 | convert('PostgreSQL' using iso_8859_1_to_utf8) | 'PostgreSQL' |
| lower(string) | text | 文字列を小文字に変換 | lower('TOM') | tom |
| octet_length(string) | int | 文字列内のバイト数 | octet_length('jose') | 4 |
| overlay(string placing string from int [for int]) | text | 部分文字列を置換 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
| position(substring in string) | int | 指定された部分文字列の位置 | position('om' in 'Thomas') | 3 |
| substring(string [from int] [for int]) | text | 部分文字列を抽出 | substring('Thomas' from 2 for 3) | hom |
| substring(string from pattern) | text | POSIX 正規表現に一致する部分文字列を抽出 | substring('Thomas' from '…$') | mas |
| substring(string from pattern for escape) | text | SQL 正規表現に一致する部分文字列を抽出 | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
| trim([leading | trailing | both] [characters] from string) | text | 文字列 string の先頭/末尾/両側から、characters（デフォルトは空白）のみを含む最も長い文字列を削除します。 | trim(both 'x' from 'xTomxx') | Tom |
| upper(string) | text | 文字列を大文字に変換します。 | upper('tom') | TOM |
| ascii(text) | int | 引数の最初の文字のASCIIコード | ascii('x') | 120 |
| btrim(string text [, characters text]) | text | string の先頭と末尾から、characters（デフォルトは空白）に含まれる文字のみからなる最も長い文字列を削除します。 | btrim('xyxtrimyyx','xy') | trim |
| chr(int) | text | 指定されたASCIIコードに対応する文字を返します | chr(65) | A |
| convert(string text, [src_encoding name,] dest_encoding name) | text | 文字列を dest_encoding に変換します。 | convert( 'text_in_utf8', 'UTF8', 'LATIN1') | ISO 8859-1 エンコーディングで表された text_in_utf8 |
| initcap(text) | text | 各単語の最初の文字を大文字に変換し、他の文字は小文字のままにします。単語は英数字からなる一連の文字であり、非英数字によって区切られます。 | initcap('hi thomas') | Hi Thomas |
| length(string text) | int | string 内の文字数 | length('jose') | 4 |
| lpad(string text, length int [, fill text]) | text | パディング文字 fill（デフォルトは空白）を使用して、string を長さ length になるまで埋めます。string が既に length より長い場合は、（右側で）切り詰めます。 | lpad('hi', 5, 'xy') | xyxhi |
| ltrim(string text [, characters text]) | text | 文字列 string の先頭から、characters（デフォルトは空白）のみを含む最も長い文字列を削除します。 | ltrim('zzzytrim','xyz') | trim |
| md5(string text) | text | 与えられた string の MD5 ハッシュを計算し、結果を16進数で返します。 | md5('abc') | |
| repeat(string text, number int) | text | string を number 回繰り返します。 | repeat('Pg', 4) | PgPgPgPg |
| replace(string text, from text, to text) | text | 文字列 string 内に出現するすべての部分文字列 from を部分文字列 to に置換します。 | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | パディング文字 fill（デフォルトは空白）を使用して、string を長さ length になるまで埋めます。string が既に length より長い場合は切り詰めます。 | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text [, character text]) | text | 文字列 string の末尾から、character（デフォルトは空白）のみを含む最も長い文字列を削除します。 | rtrim('trimxxxx','x') | trim |
| split_part(string text, delimiter text, field int) | text | delimiter で string を分割し、生成された field 番目の部分文字列を返します（1 始まり）。 | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
| strpos(string, substring) | text | 指定された部分文字列の位置。 | strpos('high','ig') | 2 |
| substr(string, from [, count]) | text | 部分文字列を抽出します。 | substr('alphabet', 3, 2) | ph |
| to_ascii(text [, encoding]) | text | text を他のエンコーディングから ASCII に変換します。 | to_ascii('Karel') | Karel |
| to_hex(number int/bigint) | text | number を対応する16進数表現に変換します。 | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | string 内の、from 内の文字に一致する任意の文字を、to 内の対応する文字に変換します。 | translate('12345', '14', 'ax') | a23x5 |
型変換関連関数
| 関数 | 戻り値の型 | 説明 | 例 |
|---|---|---|---|
| to_char(timestamp, text) | text | タイムスタンプを文字列に変換します | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | 時間間隔を文字列に変換します | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | 整数を文字列に変換します | to_char(125, '999') |
| to_char(double precision, text) | text | 倍精度浮動小数点数を文字列に変換します | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | 数値を文字列に変換します | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | 文字列を日付に変換します | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | 文字列を数値に変換します | to_number('12,454.8-', '99G999D9S') |
| to_timestamp(text, text) | timestamp | 指定された時刻形式に変換します。time zone 付きで文字列をタイムスタンプに変換します。 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double precision) | timestamp | UNIXエポックをタイムスタンプに変換します。 | to_timestamp(1284352323) |
その他の拡張参考記事：https://blog.csdn.net/sun5769675/article/details/50628979