Operators

An operator in openGauss is a reserved keyword or character, and it is generally used in the WHERE statement as a filter condition. Common operators are as follows:

Arithmetic Operators

Description: Addition

Example:

```
openGauss=# SELECT 2+3 AS RESULT;
 result 
--------
      5
(1 row)
```
Description: Subtraction

Example:

```
openGauss=# SELECT 2-3 AS RESULT;
 result 
--------
     -1
(1 row)
```
  • *

    Description: Multiplication

    Example:

    openGauss=# SELECT 2*3 AS RESULT;
     result 
    --------
          6
    (1 row)
    
  • /

    Description: Division (The result is not rounded.)

    Example:

    openGauss=# SELECT 4/2 AS RESULT;
     result 
    --------
          2
    (1 row)
    
    openGauss=# SELECT 4/3 AS RESULT;
          result      
    ------------------
     1.33333333333333
    (1 row)
    
  • +/-

    Description: Positive/Negative

    Example:

    openGauss=# SELECT -2 AS RESULT;
     result 
    --------
         -2
    (1 row)
    
  • %

    Description: Model (to obtain the remainder)

    Example:

    openGauss=# SELECT 5%4 AS RESULT;
     result 
    --------
          1
    (1 row)
    
  • @

    Description: Absolute value

    Example:

    openGauss=# SELECT @ -5.0 AS RESULT;
     result 
    --------
        5.0
    (1 row)
    
  • ^

    Description: Power (exponent calculation)

    Example:

    openGauss=# SELECT 2.0^3.0 AS RESULT;
           result       
    --------------------
     8.0000000000000000
    (1 row)
    
  • |/

    Description: Square root

    Example:

    openGauss=# SELECT |/ 25.0 AS RESULT;
     result 
    --------
          5
    (1 row)
    
  • ||/

    Description: Cubic root

    Example:

    openGauss=# SELECT ||/ 27.0 AS RESULT;
     result 
    --------
          3
    (1 row)
    
  • !

    Description: Factorial

    Example:

    openGauss=# SELECT 5! AS RESULT;
     result 
    --------
        120
    (1 row)
    
  • !!

    Description: Factorial (prefix operator)

    Example:

    openGauss=# SELECT !!5 AS RESULT;
     result 
    --------
        120
    (1 row)
    
  • &

    Description: Binary AND

    Example:

    openGauss=# SELECT 91&15  AS RESULT;
     result 
    --------
         11
    (1 row)
    
  • |

    Description: Binary OR

    Example:

    openGauss=# SELECT 32|3  AS RESULT;
     result 
    --------
         35
    (1 row)
    
  • #

    Description: Binary XOR

    Example:

    openGauss=# SELECT 17#5  AS RESULT;
     result 
    --------
         20
    (1 row)
    
  • ~

    Description: Binary NOT

    Example:

    openGauss=# SELECT ~1 AS RESULT;
     result 
    --------
         -2
    (1 row)
    
  • «

    Description: Binary shift left

    Example:

    openGauss=# SELECT 1<<4 AS RESULT;
     result 
    --------
         16
    (1 row)
    
  • >>

    Description: Binary shift right

    Example:

    openGauss=# SELECT 8>>2 AS RESULT;
     result 
    --------
          2
    (1 row)
    

Comparison Operators

Comparison operators are available for the most data types and return Boolean values.

All comparison operators are binary operators. Only data types that are the same or that can be implicitly converted can be compared by using comparison operators.

Table 1 describes the comparison operators provided by openGauss.

Table 1 Comparison operators

Operator

Description

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

=

Equal to

<>, !=, or ^=

Not equal to

Comparison operators are available for all relevant data types. All comparison operators are binary operators that returned values of Boolean type. The calculation priority of the inequality sign is higher than that of the equality sign. If the entered data is different and cannot be implicitly converted, the comparison fails. For example, an expression such as 1<2<3 is invalid because the less-than sign (<) cannot be used to compare Boolean values and 3.

Example:

openGauss=# select 1<2;
 ?column?
----------
 t
(1 row)

openGauss=# select 1>2;
 ?column?
----------
 f
(1 row)

openGauss=# select 1>=2;
 ?column?
----------
 f
(1 row)

openGauss=# select 1<=2;
 ?column?
----------
 t
(1 row)

openGauss=# select 1=2;
 ?column?
----------
 f
(1 row)

openGauss=# select 1!=2;
 ?column?
----------
 t
(1 row)

Logical Operators

Common logical operators include AND, OR, and NOT. The operation result can be TRUE, FALSE, or NULL (which means unknown). Their priorities are NOT > AND > OR.

The operators AND and OR are commutative. That is, you can switch the left and right operand without affecting the result.

Table 2 lists the calculation rules, where a and b represent logical expressions.

Table 2 Operation rules

a

b

a AND b Result

a OR b Result

NOT a Result

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

FALSE

TRUE

NULL

NULL

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

NULL

FALSE

NULL

TRUE

NULL

NULL

NULL

NULL

NULL

For details, see Examples.

Feedback
编组 3备份
    openGauss 2024-04-22 00:47:24
    cancel