oper.sgml

关系型数据库 Postgresql 6.5.2

 <Chapter Id="operators">
  <Title id="operators-title">Operators</Title>

  <Abstract>
   <Para>
    Describes the built-in operators available in 
    <ProductName>Postgres</ProductName>.
   </Para>
  </Abstract>

  <Para>
   <ProductName>Postgres</ProductName> provides a large number of 
   built-in operators on system types.
   These operators are declared in the system catalog
   pg_operator.  Every entry in pg_operator includes
   the name of the procedure that implements the operator and the
   class <Acronym>OIDs</Acronym> of the input and output types.
  </Para>

  <Para>
   To view all variations of the <Quote>||</Quote> string concatenation operator, 
   try
   <ProgramListing>
    SELECT oprleft, oprright, oprresult, oprcode
    FROM pg_operator WHERE oprname = '||';

oprleft|oprright|oprresult|oprcode
-------+--------+---------+-------
     25|      25|       25|textcat
   1042|    1042|     1042|textcat
   1043|    1043|     1043|textcat
(3 rows)
   </ProgramListing>
  </Para>

  <Para>
   Users may invoke operators using the operator name, as in:

   <ProgramListing>
select * from emp where salary < 40000;
   </ProgramListing>

   Alternatively, users may call the functions that implement the
   operators directly.  In this case, the query above would be expressed
   as:
   <ProgramListing>
select * from emp where int4lt(salary, 40000);
   </ProgramListing>
  </Para>

  <Para>
   <Application>psql</Application>
   has a command (<Command>\dd</Command>) to show these operators.
  </Para>

  <sect1>
   <title>Lexical Precedence</title>

   <para>
    Operators have a precedence which is currently hardcoded into the parser.
    Most operators have the same precedence and are left-associative. This may lead
    to non-intuitive behavior; for example the boolean operators "&lt;" and "&gt;"
    have a different precedence that the boolean operators "&lt;=" and "&gt;=".

<table tocentry="1">
<title>
Operator Ordering (decreasing precedence)
</title>

<tgroup cols="2">
<thead>
<row>
<entry>
Element
</entry>
<entry>
Precedence
</entry>
<entry>
Description
</entry>
</row>
</thead>

<tbody>
<row>
<entry>
UNION
</entry>
<entry>
left
</entry>
<entry>
SQL select construct
</entry>
</row>
<row>
<entry>
::
</entry>
<entry>
</entry>
<entry>
<productname>Postgres</productname> typecasting
</entry>
</row>
<row>
<entry>
[ ]
</entry>
<entry>
left
</entry>
<entry>
array delimiters
</entry>
</row>
<row>
<entry>
.
</entry>
<entry>
left
</entry>
<entry>
table/column delimiter
</entry>
</row>
<row>
<entry>
-
</entry>
<entry>
right
</entry>
<entry>
unary minus
</entry>
</row>
<row>
<entry>
;
</entry>
<entry>
left
</entry>
<entry>
statement termination, logarithm
</entry>
</row>
<row>
<entry>
:
</entry>
<entry>
right
</entry>
<entry>
exponentiation
</entry>
</row>
<row>
<entry>
|
</entry>
<entry>
left
</entry>
<entry>
start of interval
</entry>
</row>
<row>
<entry>
* / %
</entry>
<entry>
left
</entry>
<entry>
multiplication, division
</entry>
</row>
<row>
<entry>
+ -
</entry>
<entry>
left
</entry>
<entry>
addition, subtraction
</entry>
</row>
<row>
<entry>
IS
</entry>
<entry>
</entry>
<entry>
test for TRUE, FALSE, NULL
</entry>
</row>
<row>
<entry>
ISNULL
</entry>
<entry>
</entry>
<entry>
test for NULL
</entry>
</row>
<row>
<entry>
NOTNULL
</entry>
<entry>
</entry>
<entry>
test for NOT NULL
</entry>
</row>
<row>
<entry>
(all other operators)
</entry>
<entry>
</entry>
<entry>
native and user-defined
</entry>
</row>
<row>
<entry>
IN
</entry>
<entry>
</entry>
<entry>
set membership
</entry>
</row>
<row>
<entry>
BETWEEN
</entry>
<entry>
</entry>
<entry>
containment
</entry>
</row>
<row>
<entry>
LIKE
</entry>
<entry>
</entry>
<entry>
string pattern matching
</entry>
</row>
<row>
<entry>
&lt; &gt;
</entry>
<entry>
</entry>
<entry>
boolean inequality
</entry>
</row>
<row>
<entry>
=
</entry>
<entry>
right
</entry>
<entry>
equality
</entry>
</row>
<row>
<entry>
NOT
</entry>
<entry>
right
</entry>
<entry>
negation
</entry>
</row>
<row>
<entry>
AND
</entry>
<entry>
left
</entry>
<entry>
logical intersection
</entry>
</row>
<row>
<entry>
OR
</entry>
<entry>
left
</entry>
<entry>
logical union
</entry>
</row>
</tbody>
</tgroup>
</table>
</para>
</sect1>

  <sect1>
   <title>General Operators</title>

   <para>
    The operators listed here are defined for a number of native data types, 
    ranging from numeric types to data/time types.
   </para>

   <Para>
    <TABLE TOCENTRY="1">
     <TITLE><ProductName>Postgres</ProductName> Operators</TITLE>
     <TITLEABBREV>Operators</TITLEABBREV>
     <TGROUP COLS="3">
      <THEAD>
       <ROW>
	<ENTRY>Operator</ENTRY>
	<ENTRY>Description</ENTRY>
	<ENTRY>Usage</ENTRY>
       </ROW>
      </THEAD>
      <TBODY>
       <ROW>
	<ENTRY> &lt; </ENTRY>
	<ENTRY>Less than?</ENTRY>
	<ENTRY>1 &lt; 2</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> &lt;= </ENTRY>
	<ENTRY>Less than or equal to?</ENTRY>
	<ENTRY>1 &lt;= 2</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> &lt;&gt; </ENTRY>
	<ENTRY>Not equal?</ENTRY>
	<ENTRY>1 &lt;&gt; 2</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> = </ENTRY>
	<ENTRY>Equal?</ENTRY>
	<ENTRY>1 = 1</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> &gt; </ENTRY>
	<ENTRY>Greater than?</ENTRY>
	<ENTRY>2 &gt; 1</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> &gt;= </ENTRY>
	<ENTRY>Greater than or equal to?</ENTRY>
	<ENTRY>2 &gt;= 1</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> || </ENTRY>
	<ENTRY>Concatenate strings</ENTRY>
	<ENTRY>'Postgre' || 'SQL'</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> !!= </ENTRY>
	<ENTRY>NOT IN</ENTRY>
	<ENTRY>3 !!= i</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> ~~ </ENTRY>
	<ENTRY>LIKE</ENTRY>
	<ENTRY>'scrappy,marc,hermit' ~~ '%scrappy%'</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> !~~ </ENTRY>
	<ENTRY>NOT LIKE</ENTRY>
	<ENTRY>'bruce' !~~ '%al%'</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> ~ </ENTRY>
	<ENTRY>Match (regex), case sensitive</ENTRY>
	<ENTRY>'thomas' ~ '.*thomas.*'</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> ~* </ENTRY>
	<ENTRY>Match (regex), case insensitive</ENTRY>
	<ENTRY>'thomas' ~* '.*Thomas.*'</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> !~ </ENTRY>
	<ENTRY>Does not match (regex), case sensitive</ENTRY>
	<ENTRY>'thomas' !~ '.*Thomas.*'</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> !~* </ENTRY>
	<ENTRY>Does not match (regex), case insensitive</ENTRY>
	<ENTRY>'thomas' !~ '.*vadim.*'</ENTRY>
       </ROW>
      </TBODY>
     </TGROUP>
    </TABLE>
   </Para>
  </sect1>

  <sect1>
   <title id="math-opers">Numerical Operators</title>

   <Para>
    <TABLE TOCENTRY="1">
     <TITLE><ProductName>Postgres</ProductName> Numerical Operators</TITLE>
     <TITLEABBREV>Operators</TITLEABBREV>
     <TGROUP COLS="3">
      <THEAD>
       <ROW>
	<ENTRY>Operator</ENTRY>
	<ENTRY>Description</ENTRY>
	<ENTRY>Usage</ENTRY>
       </ROW>
      </THEAD>
      <TBODY>
       <ROW>
	<ENTRY> !  </ENTRY>
	<ENTRY>Factorial</ENTRY>
	<ENTRY>3 !</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> !!  </ENTRY>
	<ENTRY>Factorial (left operator)</ENTRY>
	<ENTRY>!! 3</ENTRY>
       </ROW>
       <ROW>
	<ENTRY> % </ENTRY>
	<ENTRY>Modulo</ENTRY>