array_ndims
The number of an array's dimensions. | array_ndims($a::anyarray)
=>
CAST(CASE WHEN length($a) > 0 THEN countSubstrings(toTypeName($a), 'Array(') END AS Nullable(Int32))
| | | |
array_dims
The text of an array's dimensions, such as [1:2][1:3]. | array_dims($a::anyarray)
=>
CASE WHEN length($a) > 0 THEN arrayStringConcat(arrayMap(d -> concat('[1:', toString(CASE d WHEN 1 THEN JSONLength(toJSONString($a)) WHEN 2 THEN JSONLength(toJSONString($a), 1) WHEN 3 THEN JSONLength(toJSONString($a), 1, 1) WHEN 4 THEN JSONLength(toJSONString($a), 1, 1, 1) WHEN 5 THEN JSONLength(toJSONString($a), 1, 1, 1, 1) WHEN 6 THEN JSONLength(toJSONString($a), 1, 1, 1, 1, 1) END), ']'), range(1, assumeNotNull(CAST(CASE WHEN length($a) > 0 THEN countSubstrings(toTypeName($a), 'Array(') END AS Nullable(Int32))) + 1)), '') END
| | | |
array_lower
The lower bound of a dimension: 1, as a list's index starts there. | array_lower($a::anyarray, $d::integer)
=>
CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN length($a) > 0 THEN countSubstrings(toTypeName($a), 'Array(') END AS Nullable(Int32)) THEN 1 END AS Nullable(Int32))
| | | |
array_upper
The upper bound of a dimension: its length. | array_upper($a::anyarray, $d::integer)
=>
CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN length($a) > 0 THEN countSubstrings(toTypeName($a), 'Array(') END AS Nullable(Int32)) THEN CASE $d WHEN 1 THEN JSONLength(toJSONString($a)) WHEN 2 THEN JSONLength(toJSONString($a), 1) WHEN 3 THEN JSONLength(toJSONString($a), 1, 1) WHEN 4 THEN JSONLength(toJSONString($a), 1, 1, 1) WHEN 5 THEN JSONLength(toJSONString($a), 1, 1, 1, 1) WHEN 6 THEN JSONLength(toJSONString($a), 1, 1, 1, 1, 1) END END AS Nullable(Int32))
| | | |
array_length
The length of a dimension; NULL for a dimension the array does not have. | array_length($a::anyarray, 1)
=>
CAST(CASE WHEN length($a) > 0 THEN length($a) END AS Nullable(Int32))
array_length($a::anyarray, $d::integer)
=>
CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN length($a) > 0 THEN countSubstrings(toTypeName($a), 'Array(') END AS Nullable(Int32)) THEN CASE $d WHEN 1 THEN JSONLength(toJSONString($a)) WHEN 2 THEN JSONLength(toJSONString($a), 1) WHEN 3 THEN JSONLength(toJSONString($a), 1, 1) WHEN 4 THEN JSONLength(toJSONString($a), 1, 1, 1) WHEN 5 THEN JSONLength(toJSONString($a), 1, 1, 1, 1) WHEN 6 THEN JSONLength(toJSONString($a), 1, 1, 1, 1, 1) END END AS Nullable(Int32))
| | | |
array_fill
An array of a value repeated to the dimensions given. | array_fill($v::anyelement, $dims::integer[])
=>
arrayWithConstant(throwIf(length($dims) <> 1, 'postgresql-error 0A000: array_fill does not read this value <end of postgresql-error>') + assumeNotNull($dims[1]), $v)
array_fill($v::anyelement, $dims::integer[], $lower_bounds::integer[])
=>
arrayWithConstant(throwIf(length($dims) <> 1 OR NOT arrayAll(_b -> _b = 1, $lower_bounds), 'postgresql-error 0A000: array_fill does not read this value <end of postgresql-error>') + assumeNotNull($dims[1]), $v)
| - Only one dimension with a lower bound of 1: any other shape is refused.
0A000 array_fill does not read this value
| | |
array_eq
Two arrays equal, a NULL element equal to a NULL element. | array_eq($a::anyarray, $b::anyarray)
=>
$a = $b
| | | |
array_ne
Two arrays not equal, a NULL element equal to a NULL element. | array_ne($a::anyarray, $b::anyarray)
=>
$a <> $b
| | | |
array_position
The position of the first element equal to a value, a NULL equal to a NULL; NULL for none. | array_position($a::anycompatiblearray, $v::anycompatible, $s::integer)
=>
refused (its error is under the limitations)
| - Refused: array_position($a::anycompatiblearray, $v::anycompatible, $s::integer).
0A000 array_position() has no form on clickhouse
| | |
array_positions
The positions of every element equal to a value, a NULL equal to a NULL. | array_positions($a::anycompatiblearray, $v::anycompatible)
=>
arrayMap(_p -> toInt32(_p.2), arrayFilter(_p -> ((_p.1 IS NULL AND $v IS NULL) OR _p.1 = $v), arrayZip($a, arrayEnumerate($a))))
| | | |
array_replace
Each element equal to a value replaced by another, a NULL equal to a NULL. | array_replace($a::anycompatiblearray, $v::anycompatible, $w::anycompatible)
=>
arrayMap(_x -> CASE WHEN ((_x IS NULL AND $v IS NULL) OR _x = $v) THEN $w ELSE _x END, $a)
| | | |
array_sample
A number of an array's elements, chosen at random. | array_sample($a::anyarray, $n::integer)
=>
arraySlice(arrayShuffle($a), 1 + throwIf($n IS NULL OR $n < 0 OR $n > length($a), 'postgresql-error 0A000: array_sample does not read this value <end of postgresql-error>'), $n)
| - An out-of-range count is refused with 0A000, not PostgreSQL's 22023 and its message.
0A000 array_sample does not read this value
| | |
array_shuffle
An array's elements in a random order. | array_shuffle($a::anyarray)
=>
arrayShuffle($a)
| | | |
trim_array
An array without its last elements. | trim_array($a::anyarray, $n::integer)
=>
arraySlice($a, 1 + throwIf($n IS NULL OR $n < 0 OR $n > length($a), 'postgresql-error 0A000: trim_array does not read this value <end of postgresql-error>'), length($a) - $n)
| - An out-of-range count is refused with 0A000, not PostgreSQL's 2202E and its message.
0A000 trim_array does not read this value
| | |
generate_subscripts
The subscripts of a dimension, as rows, in either order. | generate_subscripts($a::anyarray, $d::integer, $reverse::boolean)
=>
refused (its error is under the limitations)
generate_subscripts($a::anyarray, $d::integer)
=>
refused (its error is under the limitations)
| - Refused: generate_subscripts($a::anyarray, $d::integer, $reverse::boolean).
0A000 generate_subscripts() has no form on clickhouse - Refused: generate_subscripts($a::anyarray, $d::integer).
0A000 generate_subscripts() has no form on clickhouse
| | |
array_append
An array with an element added at its end; a NULL array is read as an empty one. | array_append(NULL::anycompatiblearray, $v::anycompatible)
=>
[$v]
| - Only a NULL array is written: ClickHouse has no array_append, so a call on an array that is not NULL fails with ClickHouse's own error.
| | |
array_prepend
An array with an element added at its start; a NULL array is read as an empty one. | array_prepend($v::anycompatible, NULL::anycompatiblearray)
=>
[$v]
| - Only a NULL array is written: ClickHouse has no array_prepend, so a call on an array that is not NULL fails with ClickHouse's own error.
| | |
array_cat
Two arrays concatenated; a NULL array is the other one, and two NULL arrays are NULL. | array_cat(NULL::anycompatiblearray, $b::anycompatiblearray)
=>
$b
array_cat($a::anycompatiblearray, NULL::anycompatiblearray)
=>
$a
array_cat($a::anycompatiblearray, $b::anycompatiblearray)
=>
refused (its error is under the limitations)
| - Refused: array_cat($a::anycompatiblearray, $b::anycompatiblearray).
0A000 array_cat() has no form on clickhouse
| | |
array_lt
One array before another, a NULL element after any value. | array_lt($a::anyarray, $b::anyarray)
=>
$a < $b
| | | |
array_le
One array before or equal to another, a NULL element after any value. | array_le($a::anyarray, $b::anyarray)
=>
$a <= $b
| | | |
array_gt
One array after another, a NULL element after any value. | array_gt($a::anyarray, $b::anyarray)
=>
$a > $b
| | | |
array_ge
One array after or equal to another, a NULL element after any value. | array_ge($a::anyarray, $b::anyarray)
=>
$a >= $b
| | | |
array_larger
The larger of two arrays, a NULL element after any value. | array_larger($a::anyarray, $b::anyarray)
=>
CASE WHEN $a > $b THEN $a WHEN $a <= $b THEN $b END
| | | |
array_smaller
The smaller of two arrays, a NULL element after any value. | array_smaller($a::anyarray, $b::anyarray)
=>
CASE WHEN $a < $b THEN $a WHEN $a >= $b THEN $b END
| | | |
btarraycmp
btree's comparison of two arrays: -1, 0 or 1, a NULL element after any value. | btarraycmp($a::anyarray, $b::anyarray)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
pg_timezone_names
The zones of the tz database, each with its abbreviation, offset and daylight flag now. | pg_timezone_names()
=>
refused (its error is under the limitations)
| - Refused: pg_timezone_names().
0A000 pg_timezone_names() has no form on clickhouse
| | |
record_eq
Two records equal, a NULL field equal to a NULL field. | record_eq($a, $b)
=>
function provisa.transpiler.polyfill.records.record_eq_on_clickhouse: record_eq on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_ne
Two records not equal, a NULL field equal to a NULL field. | record_ne($a, $b)
=>
function provisa.transpiler.polyfill.records.record_ne_on_clickhouse: record_ne on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_image_eq
Two records of equal images, a NULL field equal to a NULL field. | record_image_eq($a, $b)
=>
function provisa.transpiler.polyfill.records.record_image_eq_on_clickhouse: record_image_eq on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_image_ne
Two records of images not equal, a NULL field equal to a NULL field. | record_image_ne($a, $b)
=>
function provisa.transpiler.polyfill.records.record_image_ne_on_clickhouse: record_image_ne on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_lt
One record before another. | record_lt($a, $b)
=>
function provisa.transpiler.polyfill.records.record_lt_on_clickhouse: record_lt on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_le
One record before or equal to another. | record_le($a, $b)
=>
function provisa.transpiler.polyfill.records.record_le_on_clickhouse: record_le on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_gt
One record after another. | record_gt($a, $b)
=>
function provisa.transpiler.polyfill.records.record_gt_on_clickhouse: record_gt on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_ge
One record after or equal to another. | record_ge($a, $b)
=>
function provisa.transpiler.polyfill.records.record_ge_on_clickhouse: record_ge on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_image_lt
One record image before another. | record_image_lt($a, $b)
=>
function provisa.transpiler.polyfill.records.record_image_lt_on_clickhouse: record_image_lt on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_image_le
One record image before or equal to another. | record_image_le($a, $b)
=>
function provisa.transpiler.polyfill.records.record_image_le_on_clickhouse: record_image_le on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_image_gt
One record image after another. | record_image_gt($a, $b)
=>
function provisa.transpiler.polyfill.records.record_image_gt_on_clickhouse: record_image_gt on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
record_image_ge
One record image after or equal to another. | record_image_ge($a, $b)
=>
function provisa.transpiler.polyfill.records.record_image_ge_on_clickhouse: record_image_ge on ClickHouse: of two ROWs of literals, else refused by name.
| - Only of two ROWs of literals none NULL (and, for an order, none text): any other is refused by name, as ClickHouse's tuples compare a NULL as NULL and text by its bytes.
| | |
money_text
money's text, as cash_out writes it: $1,234.50, NULL for a NULL. | money_text($v)
=>
refused (its error is under the limitations)
| - Refused: money_text($v).
XX000 cast to text on a money column needs money's own semantics, which clickhouse neither has nor emulates; it is not answered over the column's text form
| | |
interval_in
interval_in of a NULL: PostgreSQL's NULL of interval; of a constant, its interval. | interval_in(NULL, $o, $t)
=>
refused (its error is under the limitations)
interval_in($v, NULL, $t)
=>
refused (its error is under the limitations)
interval_in($v, $o, NULL)
=>
refused (its error is under the limitations)
interval_in('infinity', $o, $t)
=>
refused (its error is under the limitations)
interval_in('-infinity', $o, $t)
=>
refused (its error is under the limitations)
| - Refused: interval_in(NULL, $o, $t).
0A000 interval_in does not read this value - Refused: interval_in($v, NULL, $t).
0A000 interval_in does not read this value - Refused: interval_in($v, $o, NULL).
0A000 interval_in does not read this value - Refused: interval_in('infinity', $o, $t).
0A000 interval_in does not read this value - Refused: interval_in('-infinity', $o, $t).
0A000 interval_in does not read this value - A NULL of interval would be described as text: the call is refused.
0A000 interval_in does not read this value - A constant is folded to its text, which pgwire describes as interval; infinity, which pgwire has no interval value of, is refused.
0A000 interval_in does not read this value
| | |
jsonb_in
jsonb_in of a NULL: PostgreSQL's NULL of jsonb. | jsonb_in(NULL)
=>
refused (its error is under the limitations)
| - Refused: jsonb_in(NULL).
0A000 jsonb_in does not read this value - A NULL of jsonb would be described as text: the call is refused.
0A000 jsonb_in does not read this value
| | |
xid8in
xid8in of a NULL: PostgreSQL's NULL of xid8. | xid8in(NULL)
=>
refused (its error is under the limitations)
| - Refused: xid8in(NULL).
0A000 xid8in does not read this value - A NULL of xid8 would be described as text: the call is refused.
0A000 xid8in does not read this value
| | |
regexp_replace
The first match replaced, or every one with the g flag; i and c set the case. | regexp_replace($s, $p, $r, $f::text)
=>
function provisa.transpiler.polyfill.regex.flagged: regexp_replace with PostgreSQL's flags read: the first match or every one, its case.
| - A flag other than g, i, c and t, a flag that is no constant, and a first match whose pattern or replacement is no constant, whose replacement names the whole match (\0) or whose pattern names a group by number are refused.
0A000 regexp_replace() has no form on clickhouse
| | |
pg_collation_for
The collation a COLLATE clause gives a value, as quote_ident writes its name. | pg_collation_for($v::text COLLATE "default")
=>
'"default"'
pg_collation_for($v::character COLLATE "default")
=>
'"default"'
pg_collation_for($v::name COLLATE "default")
=>
'"default"'
pg_collation_for($v::text COLLATE "C")
=>
'"C"'
pg_collation_for($v::character COLLATE "C")
=>
'"C"'
pg_collation_for($v::name COLLATE "C")
=>
'"C"'
pg_collation_for($v::text COLLATE "POSIX")
=>
'"POSIX"'
pg_collation_for($v::character COLLATE "POSIX")
=>
'"POSIX"'
pg_collation_for($v::name COLLATE "POSIX")
=>
'"POSIX"'
pg_collation_for($v::text COLLATE "ucs_basic")
=>
'ucs_basic'
pg_collation_for($v::character COLLATE "ucs_basic")
=>
'ucs_basic'
pg_collation_for($v::name COLLATE "ucs_basic")
=>
'ucs_basic'
pg_collation_for($v::text COLLATE ucs_basic)
=>
'ucs_basic'
pg_collation_for($v::character COLLATE ucs_basic)
=>
'ucs_basic'
pg_collation_for($v::name COLLATE ucs_basic)
=>
'ucs_basic'
pg_collation_for($v::text COLLATE "unicode")
=>
'unicode'
pg_collation_for($v::character COLLATE "unicode")
=>
'unicode'
pg_collation_for($v::name COLLATE "unicode")
=>
'unicode'
pg_collation_for($v::text COLLATE unicode)
=>
'unicode'
pg_collation_for($v::character COLLATE unicode)
=>
'unicode'
pg_collation_for($v::name COLLATE unicode)
=>
'unicode'
pg_collation_for($v::text COLLATE "pg_c_utf8")
=>
'pg_c_utf8'
pg_collation_for($v::character COLLATE "pg_c_utf8")
=>
'pg_c_utf8'
pg_collation_for($v::name COLLATE "pg_c_utf8")
=>
'pg_c_utf8'
pg_collation_for($v::text COLLATE pg_c_utf8)
=>
'pg_c_utf8'
pg_collation_for($v::character COLLATE pg_c_utf8)
=>
'pg_c_utf8'
pg_collation_for($v::name COLLATE pg_c_utf8)
=>
'pg_c_utf8'
pg_collation_for($v COLLATE $c)
=>
refused (its error is under the limitations)
| - Refused: pg_collation_for($v COLLATE $c).
0A000 pg_collation_for does not read this value - Only the built-in collations (default, C, POSIX, ucs_basic, unicode, pg_c_utf8) of a text, character or name value: any other COLLATE is refused.
0A000 pg_collation_for does not read this value
| | |
text_lt
One text before another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_text_lt: text_lt: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
text_le
One text before or equal to another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_text_le: text_le: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
text_gt
One text after another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_text_gt: text_gt: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
text_ge
One text after or equal to another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_text_ge: text_ge: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
text_larger
The later of two texts in the database's collation. | function provisa.transpiler.polyfill.trino.refused_text_larger: text_larger: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
text_smaller
The earlier of two texts in the database's collation. | function provisa.transpiler.polyfill.trino.refused_text_smaller: text_smaller: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpcharlt
One character string before another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpcharlt: bpcharlt: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpcharle
One character string before or equal to another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpcharle: bpcharle: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpchargt
One character string after another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpchargt: bpchargt: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpcharge
One character string after or equal to another in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpcharge: bpcharge: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpcharcmp
btree's comparison of two character strings in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpcharcmp: bpcharcmp: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpchar_larger
The later of two character strings in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpchar_larger: bpchar_larger: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
bpchar_smaller
The earlier of two character strings in the database's collation. | function provisa.transpiler.polyfill.trino.refused_bpchar_smaller: bpchar_smaller: refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| - Refused by name: ClickHouse compares no text by a collation, and orders text by its bytes.
| | |
ts_delete
A vector without the lexemes a list holds; a NULL item of the list is skipped. | ts_delete_array($v, $x)
=>
CASE WHEN $v IS NULL THEN NULL ELSE if(throwIf(coalesce(NOT match($v, '\\A(?:''(?:[^'']|'''')*''(?::[0-9]+[A-C]?(?:,[0-9]+[A-C]?)*)?(?: ''(?:[^'']|'''')*''(?::[0-9]+[A-C]?(?:,[0-9]+[A-C]?)*)?)*)?\\z'), false), 'postgresql-error 0A000: ts_delete does not read this value <end of postgresql-error>') = 0, arrayStringConcat(arrayFilter(_t -> NOT has($x, replace(replace(extract(_t, '\\A''((?:[^'']|'''')*)'''), '''''', ''''), '\\\\', '\\')), extractAll(ifNull($v, ''), '''(?:[^'']|'''')*''(?::[0-9]+[A-C]?(?:,[0-9]+[A-C]?)*)?')), ' '), NULL) END
| | | |
broadcast
The broadcast address of a network. | function provisa.transpiler.polyfill.trino.refused_broadcast: broadcast of an inet or cidr: refused by name, as the dialect has no such type.
| - Refused by name: the dialect has no inet type, and no form of it.
| | |
family
An address's family: 4 or 6. | function provisa.transpiler.polyfill.trino.refused_family: family of an inet or cidr: refused by name, as the dialect has no such type.
| - Refused by name: the dialect has no inet type, and no form of it.
| | |
host
An address's text, without its mask length. | function provisa.transpiler.polyfill.trino.refused_host: host of an inet or cidr: refused by name, as the dialect has no such type.
| - Refused by name: the dialect has no inet type, and no form of it.
| | |
network
A network's address, its host bits cleared. | function provisa.transpiler.polyfill.trino.refused_network: network of an inet or cidr: refused by name, as the dialect has no such type.
| - Refused by name: the dialect has no inet type, and no form of it.
| | |
macaddr_and
The bitwise AND of two MAC addresses. | macaddr_and($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
macaddr_or
The bitwise OR of two MAC addresses. | macaddr_or($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
macaddr_not
The bitwise NOT of a MAC address. | macaddr_not($a)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
trunc
A MAC address with its last three (macaddr) or five (macaddr8) bytes set to zero. | trunc($m)
=>
function provisa.transpiler.polyfill.network.truncated_address: trunc of a MAC address: its first three bytes, the rest zero; any other trunc is kept.
| | | |
xid
The 32-bit transaction ID of an xid8. | function provisa.transpiler.polyfill.unheld.xid_of_xid8: xid of an xid8 on ClickHouse: its low 32 bits, as xid8 holds the epoch above them.
| | | |
lower
The lower bound of a range or multirange. | function provisa.transpiler.polyfill.trino.refused_lower: lower of a range or multirange: refused by name, as ClickHouse has no such type.
| - Of a range or multirange the statement says: refused by name, as ClickHouse has no such type. Of anything else, ClickHouse's own.
| | |
upper
The upper bound of a range or multirange. | function provisa.transpiler.polyfill.trino.refused_upper: upper of a range or multirange: refused by name, as ClickHouse has no such type.
| - Of a range or multirange the statement says: refused by name, as ClickHouse has no such type. Of anything else, ClickHouse's own.
| | |
unnest
The ranges of a multirange, or the lexemes of a tsvector, as rows. | function provisa.transpiler.polyfill.trino.refused_unnest: unnest of a multirange or tsvector: refused by name.
| - Of a range or multirange the statement says: refused by name, as ClickHouse has no such type. Of a tsvector: refused by name, as its rows are three columns (lexeme, positions, weights) of a text the engine holds it as. Of anything else, ClickHouse's own.
| | |
max
The largest value of an aggregate. | function provisa.transpiler.polyfill.trino.refused_max: max of an inet or a cidr: refused by name, as ClickHouse has no such type.
| - Of an inet or a cidr the statement says: refused by name, as ClickHouse has no such type. Of anything else, ClickHouse's own.
| | |
min
The smallest value of an aggregate. | function provisa.transpiler.polyfill.trino.refused_min: min of an inet or a cidr: refused by name, as ClickHouse has no such type.
| - Of an inet or a cidr the statement says: refused by name, as ClickHouse has no such type. Of anything else, ClickHouse's own.
| | |
int2_sum
A step of an integer sum, in a bigint that wraps past its range. | int2_sum($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int4_sum
A step of an integer sum, in a bigint that wraps past its range. | int4_sum($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int8shl
A bigint shifted left, its count taken modulo 64, the result wrapping. | int8shl($a, $b)
=>
bitShiftLeft(CAST($a AS Nullable(Int64)), bitAnd($b, 63))
| | | |
int2shr
A smallint shifted right, its sign kept, its count taken modulo 32. | int2shr($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int4shr
A integer shifted right, its sign kept, its count taken modulo 32. | int4shr($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int8shr
A bigint shifted right, its sign kept, its count taken modulo 64. | int8shr($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
factorial
The product of the integers from 1 to n. | factorial($n)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
numeric
An integer as a numeric. | numeric($v)
=>
function provisa.transpiler.polyfill.numbers.numeric_of_an_integer: numeric of an integer: its cast to DECIMAL(38, 0); of a json value, refused.
| - Of a jsonb value: a JSON number answers its own digits, at any precision and scale, never rounded; NULL answers NULL, and any other kind raises PostgreSQL's error (22023). A number written with an exponent (1e3), which PostgreSQL answers with its digits shifted (1000), is refused with 0A000. Text a jsonb column holds that is not valid JSON, which PostgreSQL refuses when it is stored, raises 22P02 (invalid input syntax for type json) when the call reads it, without PostgreSQL's detail line. Of a json value, refused by name.
| | |
bool
An integer as a truth value: false for 0. | bool($v)
=>
function provisa.transpiler.polyfill.numbers.bool_of_an_integer: bool of an integer: its cast to BOOLEAN; of a json value, refused.
| - Of a jsonb value: a JSON true or false answers its value, NULL answers NULL, and any other kind raises PostgreSQL's error (22023). Text a jsonb column holds that is not valid JSON, which PostgreSQL refuses when it is stored, raises 22P02 (invalid input syntax for type json) when the call reads it, without PostgreSQL's detail line. Of a json value, refused by name.
| | |
oid
A bigint as an oid. | oid($v)
=>
CAST($v + throwIf(ifNull($v < 0 OR $v > 4294967295, 0), 'postgresql-error 22003: OID out of range<end of postgresql-error>') AS Nullable(Int64))
| | | |
random
A uniform random integer between two bounds, both included. | random($lo, $hi)
=>
function provisa.transpiler.polyfill.numbers.random_between: random of two integers: a uniform integer from the one to the other, both included.
| - Bounds 2^53 or more apart, and bounds of no said integer type, refused by name: a double draws no wider range exactly.
| | |
date_mi
The days from one date to another. | date_mi($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
date_pli
A date moved forward by a number of days. | date_pli($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
date_mii
A date moved back by a number of days. | date_mii($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
make_date
A date of a year, a month and a day. | make_date($y, $m, $d)
=>
CASE WHEN $y IS NULL OR $m IS NULL OR $d IS NULL THEN CAST(NULL AS Nullable(Date32)) WHEN $y BETWEEN 1900 AND 2299 AND $m BETWEEN 1 AND 12 AND $d BETWEEN 1 AND toDayOfMonth(toLastDayOfMonth(makeDate32($y, $m, 1))) THEN makeDate32($y, $m, $d) ELSE CAST(throwIf(1, 'postgresql-error 0A000: make_date does not read this value<end of postgresql-error>') AS Nullable(Date32)) END
| - Only a year from 1900 to 2299 (ClickHouse's Date32) and a day the month has: any other is refused with 0A000.
| | |
transaction_timestamp
The time the transaction began: now(), as Provisa answers it. | transaction_timestamp()
=>
now64(6, 'UTC')
| | | |
generate_series
A series of integers, as rows. | function provisa.transpiler.polyfill.calls.series: generate_series of integers in a select list, as ClickHouse's arrayJoin of its range; of
| - In a select list, of integers only: of any other type, refused by name.
| | |
lag
The value of the previous row of a window. | function provisa.transpiler.polyfill.clickhouse.framed_lag: lag over a window: NULL where ClickHouse answers its type's default.
| - Of a value holding a call, refused by name: what a rule writes is not walked again.
| | |
lead
The value of the next row of a window. | function provisa.transpiler.polyfill.clickhouse.framed_lead: lead over a window: NULL where ClickHouse answers its type's default.
| - Of a value holding a call, refused by name: what a rule writes is not walked again.
| | |
string_agg
The values of an aggregate joined by a delimiter. | function provisa.transpiler.polyfill.trino.refused_string_agg: string_agg of bytea or of a delimiter that is no constant: refused by name.
| - Of bytea, or of a delimiter that is no constant text, refused by name: Trino's listagg takes a constant separator and no bytes.
| | |
json_object
A JSON object of arrays of keys and values. | function provisa.transpiler.polyfill.trino.refused_json_object: json_object of arrays: refused by name.
| - Of arrays, refused by name: Trino builds an object of text keys only.
| | |
pg_conf_load_time
The time the server's configuration was last read. | function provisa.transpiler.polyfill.trino.refused_pg_conf_load_time: pg_conf_load_time: Refused by name: the dialect has no form of it.
| - Refused by name: the dialect has no form of it.
| | |
pg_my_temp_schema
The oid of the session's temporary schema. | function provisa.transpiler.polyfill.trino.refused_pg_my_temp_schema: pg_my_temp_schema: Refused by name: the dialect has no form of it.
| - Refused by name: the dialect has no form of it.
| | |
pg_postmaster_start_time
The time the server started. | function provisa.transpiler.polyfill.trino.refused_pg_postmaster_start_time: pg_postmaster_start_time: Refused by name: the dialect has no form of it.
| - Refused by name: the dialect has no form of it.
| | |
age
The age of a transaction id: INT_MAX for one that is no normal id (0, 1, 2). | function provisa.transpiler.polyfill.server.age_of_xid: age(xid): the special xids answered, a normal one refused where it runs; age of anything
| - Of a normal xid, refused by name where it runs: its age is the current transaction id less it, and the engine holds no transaction id.
0A000 age does not read this value - Of a timestamp, or of an xid within a call: refused by name, as no form of an interval is held here.
| | |
mxid_age
The age of a multixact id: INT_MAX for 0, and 1 less it, as Provisa makes no multixact. | mxid_age($x)
=>
if(throwIf(ifNull($x < -2147483648 OR $x > 4294967295, 0), 'postgresql-error 0A000: mxid_age does not read this value <end of postgresql-error>') = 0, CAST(CASE WHEN $x = 0 THEN 2147483647 WHEN 1 - if($x < 0, $x + CAST(4294967296 AS Int64), $x) < -2147483648 THEN 1 - if($x < 0, $x + CAST(4294967296 AS Int64), $x) + 4294967296 ELSE 1 - if($x < 0, $x + CAST(4294967296 AS Int64), $x) END AS Nullable(Int32)), NULL)
| - An xid past what xid's input reads (-2^31 to 2^32 - 1) is refused by name where it runs.
0A000 mxid_age does not read this value
| | |
pg_char_to_encoding
An encoding's number by any of PostgreSQL's names of it; -1 for a name of none. | pg_char_to_encoding($n)
=>
if(throwIf(ifNull(length($n) > 63, 0), 'postgresql-error 0A000: pg_char_to_encoding does not read this value <end of postgresql-error>') = 0, CAST(CASE WHEN $n IS NULL THEN NULL ELSE CASE lower(replaceRegexpAll($n, '[^A-Za-z0-9]', '')) WHEN 'abc' THEN 19 WHEN 'alt' THEN 20 WHEN 'big5' THEN 36 WHEN 'euccn' THEN 2 WHEN 'eucjis2004' THEN 5 WHEN 'eucjp' THEN 1 WHEN 'euckr' THEN 3 WHEN 'euctw' THEN 4 WHEN 'gb18030' THEN 39 WHEN 'gbk' THEN 37 WHEN 'iso88591' THEN 8 WHEN 'iso885910' THEN 13 WHEN 'iso885913' THEN 14 WHEN 'iso885914' THEN 15 WHEN 'iso885915' THEN 16 WHEN 'iso885916' THEN 17 WHEN 'iso88592' THEN 9 WHEN 'iso88593' THEN 10 WHEN 'iso88594' THEN 11 WHEN 'iso88595' THEN 25 WHEN 'iso88596' THEN 26 WHEN 'iso88597' THEN 27 WHEN 'iso88598' THEN 28 WHEN 'iso88599' THEN 12 WHEN 'johab' THEN 40 WHEN 'koi8' THEN 22 WHEN 'koi8r' THEN 22 WHEN 'koi8u' THEN 34 WHEN 'latin1' THEN 8 WHEN 'latin10' THEN 17 WHEN 'latin2' THEN 9 WHEN 'latin3' THEN 10 WHEN 'latin4' THEN 11 WHEN 'latin5' THEN 12 WHEN 'latin6' THEN 13 WHEN 'latin7' THEN 14 WHEN 'latin8' THEN 15 WHEN 'latin9' THEN 16 WHEN 'mskanji' THEN 35 WHEN 'muleinternal' THEN 7 WHEN 'shiftjis' THEN 35 WHEN 'shiftjis2004' THEN 41 WHEN 'sjis' THEN 35 WHEN 'sqlascii' THEN 0 WHEN 'tcvn' THEN 19 WHEN 'tcvn5712' THEN 19 WHEN 'uhc' THEN 38 WHEN 'unicode' THEN 6 WHEN 'utf8' THEN 6 WHEN 'vscii' THEN 19 WHEN 'win' THEN 23 WHEN 'win1250' THEN 29 WHEN 'win1251' THEN 23 WHEN 'win1252' THEN 24 WHEN 'win1253' THEN 30 WHEN 'win1254' THEN 31 WHEN 'win1255' THEN 32 WHEN 'win1256' THEN 18 WHEN 'win1257' THEN 33 WHEN 'win1258' THEN 19 WHEN 'win866' THEN 20 WHEN 'win874' THEN 21 WHEN 'win932' THEN 35 WHEN 'win936' THEN 37 WHEN 'win949' THEN 38 WHEN 'win950' THEN 36 WHEN 'windows1250' THEN 29 WHEN 'windows1251' THEN 23 WHEN 'windows1252' THEN 24 WHEN 'windows1253' THEN 30 WHEN 'windows1254' THEN 31 WHEN 'windows1255' THEN 32 WHEN 'windows1256' THEN 18 WHEN 'windows1257' THEN 33 WHEN 'windows1258' THEN 19 WHEN 'windows866' THEN 20 WHEN 'windows874' THEN 21 WHEN 'windows932' THEN 35 WHEN 'windows936' THEN 37 WHEN 'windows949' THEN 38 WHEN 'windows950' THEN 36 ELSE -1 END END AS Nullable(Int32)), NULL)
| - A name of more than 63 bytes is refused by name where it runs: PostgreSQL cuts it to them as it reads it.
0A000 pg_char_to_encoding does not read this value
| | |
int4multirange
A multirange of integers, of ranges or of a VARIADIC array of them. | | - Of a VARIADIC cast to an array of ranges, of anything but the text of an array of one dimension: refused by name, as no array of a range type is held.
0A000 int4multirange() has no form on clickhouse
| | |
int8multirange
A multirange of bigints, of ranges or of a VARIADIC array of them. | | - Of a VARIADIC cast to an array of ranges, of anything but the text of an array of one dimension: refused by name, as no array of a range type is held.
0A000 int8multirange() has no form on clickhouse
| | |
nummultirange
A multirange of numerics, of ranges or of a VARIADIC array of them. | | - Of a VARIADIC cast to an array of ranges, of anything but the text of an array of one dimension: refused by name, as no array of a range type is held.
0A000 nummultirange() has no form on clickhouse
| | |
datemultirange
A multirange of dates, of ranges or of a VARIADIC array of them. | | - Of a VARIADIC cast to an array of ranges, of anything but the text of an array of one dimension: refused by name, as no array of a range type is held.
0A000 datemultirange() has no form on clickhouse
| | |
tsmultirange
A multirange of timestamps, of ranges or of a VARIADIC array of them. | | - Of a VARIADIC cast to an array of ranges, of anything but the text of an array of one dimension: refused by name, as no array of a range type is held.
0A000 tsmultirange() has no form on clickhouse
| | |
tstzmultirange
A multirange of timestamps with time zone, of ranges or of a VARIADIC array of them. | | - Of a VARIADIC cast to an array of ranges, of anything but the text of an array of one dimension: refused by name, as no array of a range type is held.
0A000 tstzmultirange() has no form on clickhouse
| | |
json_agg
A JSON array of an aggregate's values. | function provisa.transpiler.polyfill.json_values.json_array: json_agg of a number, a text or a truth value the statement says: its values as a JSON
| - Of a number, a text or a truth value the statement says the type of: any other value, and an order, DISTINCT, a FILTER or a window, refused by name.
| | |
a NULL array
A call PostgreSQL answers NULL for its NULL array argument, as that NULL. | function provisa.transpiler.polyfill.clickhouse.null_of_a_null_array: A call PostgreSQL answers NULL for its NULL array argument, as that NULL, named for the
| - A set-returning call of a NULL array is its own row's: generate_subscripts and pg_options_to_table are refused by name.
| | |
array_agg
The values of an aggregate as an array, a NULL kept; NULL of no rows. | array_agg($a)
=>
function provisa.transpiler.polyfill.clickhouse.kept_nulls: array_agg of every value, a NULL kept, and NULL of no rows.
| | | |
date_trunc
The start of a unit of a timestamp. | date_trunc($u, $t)
=>
function provisa.transpiler.polyfill.clickhouse.started: date_trunc of a timestamp: its start as DateTime64(6, 'UTC'), whatever the unit.
| | | |
date
A text cast to a date. | function provisa.transpiler.polyfill.clickhouse.dated: A text the statement writes cast to a date: its day, PostgreSQL's error of one that does
| - A text the statement writes: an ISO date of 1900 to 2299, any other refused by name. A text column's cast keeps ClickHouse's own read.
| | |
first_value
The value of a row of a window. | function provisa.transpiler.polyfill.clickhouse.framed_first_value: first_value over a window: NULL where ClickHouse answers its type's default.
| - Of a value holding a call, refused by name: what a rule writes is not walked again.
| | |
last_value
The value of a row of a window. | function provisa.transpiler.polyfill.clickhouse.framed_last_value: last_value over a window: NULL where ClickHouse answers its type's default.
| - Of a value holding a call, refused by name: what a rule writes is not walked again.
| | |
nth_value
The value of a row of a window. | function provisa.transpiler.polyfill.clickhouse.framed_nth_value: nth_value over a window: NULL where ClickHouse answers its type's default.
| - Of a value holding a call, refused by name: what a rule writes is not walked again.
| | |
ntile
The bucket of a row in a window, as an integer. | function provisa.transpiler.polyfill.clickhouse.bucketed: ntile over a window, as PostgreSQL's integer: every engine's is a bigint. On ClickHouse a
| - A count of buckets that is no constant is refused by name: ClickHouse's ntile takes a constant.
| | |
numeric_mod
The remainder of one numeric divided by another, at the larger scale of the two. | numeric_mod($a, $b)
=>
CASE WHEN $a IS NULL OR $b IS NULL THEN CAST(NULL AS Nullable(String)) WHEN $b = 0 THEN CAST(throwIf(1, 'postgresql-error 22012: division by zero<end of postgresql-error>') AS Nullable(String)) ELSE trimRight(toDecimalString(CAST($a AS Nullable(Decimal(38, 18))) % CAST(nullIf($b, 0) AS Nullable(Decimal(38, 18))), greatest(toUInt8OrZero(extract(toTypeName($a), 'Decimal\\(\\d+, (\\d+)\\)')), toUInt8OrZero(extract(toTypeName($b), 'Decimal\\(\\d+, (\\d+)\\)')))), '.') END
| - The scale is each operand's type's: a column holds one scale for all its values, where PostgreSQL's numeric holds each value's own.
| | |
current_schemas
The schemas of the search path. | function provisa.transpiler.polyfill.trino.refused_current_schemas: current_schemas: refused by name, as no form names the search path.
| - Refused by name: ClickHouse's own currentSchemas answers its database, not PostgreSQL's search path.
| | |
spg_poly_quad_compress
A polygon's bounding box, as the SP-GiST quad tree's compress function answers it. | spg_poly_quad_compress($v)
=>
CASE WHEN $v IS NULL THEN NULL ELSE arrayMap(_n -> concat('(', arrayFilter(_y -> toFloat64(_y) = arrayMax(arrayMap(_z -> toFloat64(_z), arrayFilter((_t, _i) -> _i % 2 = 1, _n, arrayEnumerate(_n)))), arrayFilter((_t, _i) -> _i % 2 = 1, _n, arrayEnumerate(_n)))[1], ',', arrayFilter(_y -> toFloat64(_y) = arrayMax(arrayMap(_z -> toFloat64(_z), arrayFilter((_t, _i) -> _i % 2 = 0, _n, arrayEnumerate(_n)))), arrayFilter((_t, _i) -> _i % 2 = 0, _n, arrayEnumerate(_n)))[1], '),(', arrayFilter(_y -> toFloat64(_y) = arrayMin(arrayMap(_z -> toFloat64(_z), arrayFilter((_t, _i) -> _i % 2 = 1, _n, arrayEnumerate(_n)))), arrayFilter((_t, _i) -> _i % 2 = 1, _n, arrayEnumerate(_n)))[1], ',', arrayFilter(_y -> toFloat64(_y) = arrayMin(arrayMap(_z -> toFloat64(_z), arrayFilter((_t, _i) -> _i % 2 = 0, _n, arrayEnumerate(_n)))), arrayFilter((_t, _i) -> _i % 2 = 0, _n, arrayEnumerate(_n)))[1], ')', repeat('', throwIf(ifNull(arrayExists(_y -> NOT match(_y, '^-?(0|[1-9][0-9]*)([.][0-9]*[1-9])?$'), _n), 0), 'postgresql-error 0A000: spg_poly_quad_compress does not read this value<end of postgresql-error>'))), [extractAll(toString($v), '-?[0-9.]+(?:e[-+]?[0-9]+)?')])[1] END
| - A polygon with a number PostgreSQL writes otherwise than as it was written (an exponent, a trailing zero) is refused by name when the statement runs.
| | |