Tuesday, 29 January 2019

postgres 12 case array, range operator

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

OperatorDescriptionExampleResult
=equalARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]t
<>not equalARRAY[1,2,3] <> ARRAY[1,2,4]t
<less thanARRAY[1,2,3] < ARRAY[1,2,4]t
>greater thanARRAY[1,4,3] > ARRAY[1,2,4]t
<=less than or equalARRAY[1,2,3] <= ARRAY[1,2,3]t
>=greater than or equalARRAY[1,4,3] >= ARRAY[1,4,3]t
@>containsARRAY[1,4,3] @> ARRAY[3,1]t
<@is contained byARRAY[2,7] <@ ARRAY[1,7,4,2,6]t
&&overlap (have elements in common)ARRAY[1,4,3] && ARRAY[2,1]t
||array-to-array concatenationARRAY[1,2,3] || ARRAY[4,5,6]{1,2,3,4,5,6}
||array-to-array concatenationARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]{{1,2,3},{4,5,6},{7,8,9}}
||element-to-array concatenation3 || ARRAY[4,5,6]{3,4,5,6}
||array-to-element concatenationARRAY[4,5,6] || 7{4,5,6,7}
OperatorDescriptionExampleResult
=equalint4range(1,5) = '[1,4]'::int4ranget
<>not equalnumrange(1.1,2.2) <> numrange(1.1,2.3)t
<less thanint4range(1,10) < int4range(2,3)t
>greater thanint4range(1,10) > int4range(1,5)t
<=less than or equalnumrange(1.1,2.2) <= numrange(1.1,2.2)t
>=greater than or equalnumrange(1.1,2.2) >= numrange(1.1,2.0)t
@>contains rangeint4range(2,4) @> int4range(2,3)t
@>contains element'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestampt
<@range is contained byint4range(2,4) <@ int4range(1,7)t
<@element is contained by42 <@ int4range(1,7)f
&&overlap (have points in common)int8range(3,7) && int8range(4,12)t
<<strictly left ofint8range(1,10) << int8range(100,110)t
>>strictly right ofint8range(50,60) >> int8range(20,30)t
&<does not extend to the right ofint8range(1,20) &< int8range(18,20)t
&>does not extend to the left ofint8range(7,20) &> int8range(5,10)t
-|-is adjacent tonumrange(1.1,2.2) -|- numrange(2.2,3.3)t
+unionnumrange(5,15) + numrange(10,20)[5,20)
*intersectionint8range(5,15) * int8range(10,20)[10,15)
-differenceint8range(5,15) - int8range(10,20)[5,10)

No comments:

Post a Comment