Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Numeric Operators

PostgreSQL's numeric operator support can be divided into three general groups:

Mathematical operators

Mathematical operators affect one or two values, perform a mathematical operation, and return a value of a numeric data type.

Numeric comparison operators

Numeric comparison operators draw a conclusion based on two numeric values (such as whether one is larger than the other) and returns a value of type boolean, set to either true or false.

Binary (or bit string) operators

Binary, or bit string, operators manipulate numeric values at the bit level of zeroes and ones. The following sections address each of these operator groups.

Mathematical operators

Mathematical operators can be used in the target list, in the WHERE clause of a SELECT statement, or anywhere else a numeric result may be appropriate. This sometimes will include the ORDER BY clause, a JOIN qualifier, or a GROUP BY clause.

Table 5-4 describes each of the mathematical operators available in PostgreSQL, along with example usage.

Table 5-4. Mathematical operators

Operator Usage Description

+

a + b

Addition of numeric quantities a and b

-

a - b

Subtraction of numeric quantity b from a

*

a * b

Multiplication of numeric quantities a and b

/

a / b

Division of numeric quantity a by b

%

a % b

Modulus, or remainder, from dividing a by b

^

a ^ b

Exponential operator, the value of a to the power of b

|/

|/ a

Square root of a

||/

||/ a

Cube root of a

!

a !

Factorial of a

!!

!! a

Factorial prefix, factorial of a , different only in syntactic placement from !

@

@ a

Absolute value of a

As an example of mathematical operators in the target list, the statement in Example 5-9 takes the retail price for each book and divides the cost with the / operator in order to determine the profit margin. This value is then typecast to a truncated numeric value with only two digits of precision. Finally, the integer constant 1 is subtracted from the division result, to yield only the percentage points over 100.

Example 5-9. Using Mathematical Operators

booktown=# 
SELECT isbn,

booktown-# 
       (retail / cost)::numeric(3, 2) - 1 AS margin

booktown-# 
       FROM stock

booktown-# 
       ORDER BY margin DESC

booktown-# 
       LIMIT 4;

    isbn    | margin
------------+--------
 0451457994 |   0.35
 0760720002 |   0.33
 0451198492 |   0.30
 0441172717 |   0.29
(4 rows)

Notice that the column name is temporarily aliased to margin by using the AS keyword. Remember that the column name created by the AS keyword is a temporary name, and used only for the duration of the query.

Numeric comparison operators

Comparison operators are used to compare values of types such as integer or text to one another, but they will always return a value of type boolean. These operators are most commonly used in the WHERE clause, but may be used anywhere in a SQL statement where a value of type boolean would be valid.

Table 5-5 shows the available comparison operators.

Table 5-5. Comparison operators

Operator

Description

<

Less-than, returns true if the value to the left is smaller in quantity than the value to the right

>

Greater-than, returns true if the value to the left is greater in quantity than the value to the right

<=

Less-than or equal-to, returns true if the value to the left is smaller, or equal to, in quantity than the value to the right

>=

Greater-than or equal-to, returns true if the value to the left is greater, or equal to, in quantity than the value to the right

=

Equal-to, returns true if the values to the left and right of the operator are equivalent

< > or !=

Not-equal, returns true if the values to the left and right of the operator not equivalent

Note: The < > operator exists as an alias to the != operator for functional compatibility with other SQL-capable database implementations. They are effectively identical.

For an example of mathematical comparison operator usage, observe Example 5-10. The query involved uses the <= operator first, to check if the retail value is less-than or equal-to 25. Subsequently, the != operator is employed with the AND keyword to ensure that only books which are in stock (whose stock value are not equal to 0) are returned.

Example 5-10. Using comparison operators

booktown=# 
SELECT isbn, stock

booktown-# 
       FROM stock

booktown-# 
       WHERE retail <= 25

booktown-# 
       AND stock != 0;

    isbn    | stock
------------+-------
 0441172717 |    77
 0590445065 |    10
 0679803335 |    18
 0760720002 |    28
 0929605942 |    25
 1885418035 |    77
(6 rows)

Numeric comparison keywords

The BETWEEN keyword (sometimes called an operator) allows you to check a value for existence within a range of values. For instance, Example 5-11 shows a SELECT statement that looks for books with cost between 10 and 17 dollars.

Example 5-11. Using BETWEEN

booktown=# 
SELECT isbn FROM stock

booktown-# 
       WHERE cost BETWEEN 10 AND 17;

    isbn
------------
 0394800753
 0441172717
 0451457994
(3 rows)

You can achieve the same output using the less-than-or-equal-to operator (<=) in conjunction with the greater-than-or-equal-to (>=) operator. See Example 5-12.

Example 5-12. Operator equivalents to BETWEEN

booktown=# 
SELECT isbn FROM stock

booktown-# 
       WHERE cost >= 10 AND cost <= 17;

    isbn
------------
 0394800753
 0441172717
 0451457994
(3 rows)

The BETWEEN syntax simply adds to the readability of an SQL statement. Since both the keyword and operator forms are equally valid to PostgreSQL, it's mostly a matter of user preference.

Binary operators

Binary operators perform bitwise operations on the literal bits of a bit string or integer. These operators may affect integer values, or directly on bit string values. Each of PostgreSQL's binary operators are described in Table 5-6.

Table 5-6. Bit-string operators

Operator Usage Description

&

a & b

Binary AND between bit string values of a and b (which may be provided as integers)

|

a | b

Binary OR between bit string values of a and b (which may be provided as integers)

#

a # b

Binary XOR between bit string values of a and b (which may be provided as integers)

~

~ b

Binary NOT, returns the inverted bit string of b

<<

b << n

Binary shifts b to the left by n bits

>>

b >> n

Binary shifts b to the right by n bits

Example 5-13 demonstrates shifting a numeric value, and its equivalent bit string, two bits to the right with the >> operator. It also demonstrates the use of the bittoint4() function, described in the Section called Functions ."

Example 5-13. Shifting bit strings

booktown=# 
SELECT b'1000' >> 2 AS "8 shifted right",

booktown-# 
       bittoint4(b'1000' >> 2) AS integer,

booktown-# 
       8 >> 2 AS likewise;

 8 shifted right | integer | likewise
-----------------+---------+----------
 0010            |       2 |        2
(1 row)

Note: When shifting bit strings, the original length of the string does not change, and any digits pushed either to the left or right of the bit string will be truncated. When using &, |, or #, the bit strings operated on must be of equal length in order to properly compare each bit on a one-to-one basis.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire