v0.189
Add support for dereferencing row fields in lambda expressions
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"]
v0.190
Add support for ORDER BY in aggregation functions
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)
Add support for casting to INTERVAL types
Optimized aggregations on INTERVAL types
SELECT CAST(INTERVAL '18000' SECOND AS VARCHAR); =>0 05:00:00.000
Add ST_Buffer() geospatial function
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))
approx_distinct()function now supports a standard error in the range of [0.0040625, 0.26000]
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;
v0.193
Add map_zip_with() lambda function
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));
Add normal_cdf() function
Returns the cumulative distribution function value for a normal distribution.
Normal_cdf(mean, standard deviation, value) → double
v0.196
Add is_json_scalar() function
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
Add regexp_replace()function variant that executes a lambda for each replacement
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'
v0.197 and v0.198
Add DATE variant for sequence()function with an implicit one-day step increment
Generate a sequence of integers from start to stop, incrementing by 1 if start is less than or equal to stop,otherwise -1.
v0.197
Add ST_IsSimple() geospatial function
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)
v0.199
Add hmac_md5(), hmac_sha1(), hmac_sha256(), and hmac_sha512()functions
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
Add array_sort() function that takes a lambda as a comparator
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]]
Add line_locate_point()geospatial function
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
-------------
v0.200
Add ST_GeometryType() geospatial function
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
v0.202
Add multimap_from_entries()function
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']}
Add bing_tiles_around()geospatial functions
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>
Add ST_NumGeometries() geospatial functions
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)))'));
Add ST_GeometryN() geospatial functions
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);
Add ST_ConvexHull() geospatial functions
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;
Add wilson_interval_lower() and wilson_interval_upper() functions
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))");