The following lambda function which uses a table alias is supported.
select transform(array[1], x -> x + t.a) from (values 1, 2, 3) t(a);
=>
["2"]
["3"]
["4"]Some aggregate functions such as array_agg() produce different results depending on the order of input values. This ordering can be specified by writing an ORDER BY Clause within the aggregate function:
array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z) Optimized aggregations on INTERVAL types
SELECT CAST(INTERVAL '18000' SECOND AS VARCHAR);
=>0 05:00:00.000 Returns the geometry that represents all points whose distance from the specified geometry is less than or equal to the specified distance.
SELECT ST_BUFFER(ST_Point(1, 2), 2.0)
=> POLYGON ((3 2, 2.9957178464772056 2.130806258460286, 2.982889722747619 2.2610523844401027, 2.9615705608064595 2.390180644032256, 2.9318516525781355 2.517638090205041, 2.8938602589902103 2.6428789306063227, 2.8477590650225726 2.765366864730179, 2.7937454830653756 2.8845773804380017, 2.7320508075688767 2.999999999999999, 2.66293922460509 3.1111404660392035, 2.58670668058247 3.217522858017441, 2.5036796149579543 3.318691630200137, 2.414213562373095 3.4142135623730945, 2.3186916302001377 3.5036796149579543, 2.217522858017441 3.58670668058247, 2.1111404660392044 3.66293922460509, 2 3.7320508075688767, 1.8845773804380026 3.7937454830653765, 1.7653668647301795 3.847759065022573, 1.6428789306063232 3.893860258990211, 1.5176380902050415 3.9318516525781364, 1.3901806440322564 3.961570560806461, 1.2610523844401031 3.982889722747621, 1.130806258460286 3.995717846477207, 1 4, 0.8691937415397142 3.9957178464772056, 0.7389476155598972 3.982889722747619, 0.6098193559677438 3.9615705608064595, 0.48236190979495897 3.9318516525781355, 0.3571210693936774 3.8938602589902103, 0.2346331352698211 3.8477590650225726, 0.11542261956199817 3.7937454830653756, 7.7715611723760960e-16 3.7320508075688767, -0.11114046603920369 3.66293922460509, -0.21752285801744065 3.58670668058247, -0.318691630200137 3.5036796149579543, -0.4142135623730945 3.414213562373095, -0.5036796149579543 3.3186916302001377, -0.5867066805824699 3.217522858017441, -0.66293922460509 3.1111404660392044, -0.732050807568877 3, -0.7937454830653763 2.8845773804380026, -0.8477590650225733 2.7653668647301792, -0.8938602589902112 2.642878930606323, -0.9318516525781366 2.5176380902050415, -0.9615705608064609 2.3901806440322564, -0.9828897227476208 2.261052384440103, -0.995717846477207 2.1308062584602863, -1 2, -0.9957178464772056 1.8691937415397142, -0.9828897227476194 1.7389476155598973, -0.9615705608064595 1.6098193559677438, -0.9318516525781353 1.482361909794959, -0.8938602589902103 1.3571210693936773, -0.8477590650225726 1.2346331352698212, -0.7937454830653756 1.1154226195619983, -0.7320508075688765 1.0000000000000009, -0.6629392246050898 0.8888595339607963, -0.5867066805824699 0.7824771419825594, -0.5036796149579543 0.681308369799863, -0.4142135623730947 0.5857864376269055, -0.31869163020013747 0.4963203850420457, -0.2175228580174411 0.4132933194175301, -0.11114046603920436 0.33706077539491, 1.1102230246251565e-16 0.26794919243112303, 0.11542261956199751 0.2062545169346237, 0.23463313526982055 0.15224093497742675, 0.35712106939367694 0.10613974100978885, 0.4823619097949585 0.06814834742186338, 0.6098193559677435 0.03842943919353914, 0.7389476155598969 0.017110277252379236, 0.8691937415397138 0.004282153522793042, 1 0, 1.1308062584602858 0.004282153522794374, 1.2610523844401027 0.017110277252380568, 1.3901806440322562 0.03842943919354047, 1.517638090205041 0.06814834742186471, 1.6428789306063227 0.10613974100978973, 1.7653668647301788 0.1522409349774274, 1.8845773804380017 0.20625451693462438, 1.9999999999999991 0.2679491924311235, 2.1111404660392035 0.3370607753949102, 2.217522858017441 0.4132933194175301, 2.318691630200137 0.4963203850420457, 2.4142135623730945 0.5857864376269053, 2.5036796149579543 0.6813083697998625, 2.58670668058247 0.7824771419825589, 2.66293922460509 0.8888595339607956, 2.7320508075688767 1, 2.7937454830653765 1.1154226195619974, 2.847759065022573 1.2346331352698205, 2.893860258990211 1.3571210693936768, 2.9318516525781364 1.4823619097949585, 2.961570560806461 1.6098193559677436, 2.982889722747621 1.7389476155598969, 2.995717846477207 1.869193741539714, 3 2)) Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null.
This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range of [0.0040625, 0.26000].
SELECT APPROX_DISTINCT(x, value) from tbl; Merges the two given maps into a single map by applying function to the pair of values with the same key. For keys only presented in one map, NULL will be passed as the value for the missing key.
map_zip_with(map<K, V1>, map<K, V2>, function<K, V1, V2, V3>) → map<K, V3> SELECT map_zip_with(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']), -- {1 -> ad, 2 -> be, 3 -> cf}
MAP(ARRAY[1, 2, 3], ARRAY['d', 'e', 'f']),
(k, v1, v2) -> concat(v1, v2));
SELECT map_zip_with(MAP(ARRAY['k1', 'k2'], ARRAY[1, 2]), -- {k1 -> ROW(1, null), k2 -> ROW(2, 4), k3 -> ROW(null, 9)}
MAP(ARRAY['k2', 'k3'], ARRAY[4, 9]),
(k, v1, v2) -> (v1, v2));
SELECT map_zip_with(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 8, 27]), -- {a -> a1, b -> b4, c -> c9}
MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]),
(k, v1, v2) -> k || CAST(v1/v2 AS VARCHAR)); Returns the cumulative distribution function value for a normal distribution.
Normal_cdf(mean, standard deviation, value) → double Determine if json is a scalar (i.e. a JSON number, a JSON string, true,false or null):
SELECT is_json_scalar('1');
=> true
SELECT is_json_scalar('[1, 2, 3]');
=> false Replaces every instance of the substring matched by the regular expression pattern in string with replacement. Capturing groups can be referenced in replacement using $g for a numbered group or ${name} for a named group. A dollar sign ($) may be included in the replacement by escaping it with a backslash (\$):
SELECT regexp_replace('1a 2b 14m', '(\d+)([ab]) ', '3c$2 ');
=> '3ca 3cb 14m' Generate a sequence of integers from start to stop, incrementing by 1 if start is less than or equal to stop,otherwise -1.
SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
st_issimple
-------------
t
(1 row)
SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
st_issimple
-------------
f
(1 row) Computes HMAC with md5 or sha1 or sha256 or sha512 of binary with the given key
hmac_md5(binary, key) → varbinaryComputes
hmac_sha1(binary, key) → varbinaryComputes
hmac_sha256(binary, key) → varbinaryComputes
hmac_sha512(binary, key) → varbinaryComputes Sorts and returns the array based on the given comparator function. The comparator will take two nullable arguments representing two nullable elements of the array. It returns -1, 0, or 1 as the first nullable element is less than, equal to, or greater than the second nullable element. If the comparator function returns other values (including NULL), the query will fail and raise an error
SELECT array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- [5, 3, 2, 2, 1]
SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- ['dc', 'bc', 'ab']
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null first with descending order
(x, y) -> CASE WHEN x IS NULL THEN -1
WHEN y IS NULL THEN 1
WHEN x < y THEN 1
WHEN x = y THEN 0
ELSE -1 END); -- [null, null, 5, 3, 2, 2, 1]
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null last with descending order
(x, y) -> CASE WHEN x IS NULL THEN 1
WHEN y IS NULL THEN -1
WHEN x < y THEN 1
WHEN x = y THEN 0
ELSE -1 END); -- [5, 3, 2, 2, 1, null, null]
SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], -- sort by string length
(x, y) -> IF(length(x) < length(y),
-1,
IF(length(x) = length(y), 0, 1))); -- ['a', 'abc', 'abcd']
SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], -- sort by array length
(x, y) -> IF(cardinality(x) < cardinality(y),
-1,
IF(cardinality(x) = cardinality(y), 0, 1))); -- [[1, 2], [2, 3, 1], [4, 2, 1, 4]] Returns a float between 0 and 1 representing the location of the closest point on the LineString to the given Point, as a fraction of total 2d line length.
Returns null if a LineString or a Point is empty or null.
SELECT LINE_LOCATE_POINT(GEOMFROMTEXT('LINESTRING(0 0, 5 5, 10 10)'), GEOMFROMTEXT('POINT(5 5)'));
-------------
col1
-------------
0.500
------------- Returns the type of the geometry.
SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
=> ST_LineString Returns a map created from the given array of entries.
SELECT multimap_from_entries(ARRAY[(1, 'x'), (2, 'y'), (1, 'z')]); -- {1 -> ['x', 'z'], 2 -> ['y']} Returns a collection of Bing tiles that surround the point specified by the latitude and longitude arguments at a given zoom level.
bing_tiles_around(latitude, longitude, zoom_level) → array<BingTile> Returns the number of geometries in the collection. If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the number of geometries, for single geometries returns 1, for empty geometries returns 0.
ST_NumGeometries(Geometry) → bigint SELECT ST_NumGeometries(ST_GeomFromEWKT('GEOMETRYCOLLECTION(MULTIPOINT(-2 3 , -2 2),
LINESTRING(5 5 ,10 10),
POLYGON((-7 4.2,-7.1 5,-7.1 4.3,-7 4.2)))')); Returns the geometry element at a given index (indices start at 1). If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the geometry at a given index. If the given index is less than 1 or greater than the total number of elements in the collection, returns NULL.Use :func:ST_NumGeometries to find out the total number of elements. Singular geometries (e.g., POINT, LINESTRING, POLYGON), are treated as collections of one element. Empty geometries are treated as empty collections.
SELECT gid, n, ST_GeometryN(the_geom, n)
FROM tbl CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(the_geom); Returns the minimum convex geometry that encloses all input geometries. This function doesn’t support geometry collections.
SELECT t.tbl_type, ST_ConvexHull(ST_Collect(t.the_geom)) As the_geom
FROM tbl As t
GROUP BY t.tbl_type; Returns the lower bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score Z.
assertFunction("ST_AsText(ST_EnvelopeAsPts(ST_GeometryFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')))",
VARCHAR, "MULTIPOINT ((1 2), (4 8))");