<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Comparison Operators</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="NEXT" TITLE="Mathematical Functions and Operators" HREF="functions-math.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions-math.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-COMPARISON" >9.2. Comparison Operators</A ></H1 ><A NAME="AEN5984" ></A ><P > The usual comparison operators are available, shown in <A HREF="functions-comparison.html#FUNCTIONS-COMPARISON-TABLE" >Table 9-1</A >. </P ><DIV CLASS="TABLE" ><A NAME="FUNCTIONS-COMPARISON-TABLE" ></A ><P ><B >Table 9-1. Comparison Operators</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><THEAD ><TR ><TH >Operator</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD > <TT CLASS="LITERAL" ><</TT > </TD ><TD >less than</TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >></TT > </TD ><TD >greater than</TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><=</TT > </TD ><TD >less than or equal to</TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >>=</TT > </TD ><TD >greater than or equal to</TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >=</TT > </TD ><TD >equal</TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><></TT > or <TT CLASS="LITERAL" >!=</TT > </TD ><TD >not equal</TD ></TR ></TBODY ></TABLE ></DIV ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > The <TT CLASS="LITERAL" >!=</TT > operator is converted to <TT CLASS="LITERAL" ><></TT > in the parser stage. It is not possible to implement <TT CLASS="LITERAL" >!=</TT > and <TT CLASS="LITERAL" ><></TT > operators that do different things. </P ></BLOCKQUOTE ></DIV ><P > Comparison operators are available for all data types where this makes sense. All comparison operators are binary operators that return values of type <TT CLASS="TYPE" >boolean</TT >; expressions like <TT CLASS="LITERAL" >1 < 2 < 3</TT > are not valid (because there is no <TT CLASS="LITERAL" ><</TT > operator to compare a Boolean value with <TT CLASS="LITERAL" >3</TT >). </P ><P > <A NAME="AEN6034" ></A > In addition to the comparison operators, the special <TT CLASS="TOKEN" >BETWEEN</TT > construct is available.<A NAME="AEN6037" ></A > </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >a</I ></TT > BETWEEN <TT CLASS="REPLACEABLE" ><I >x</I ></TT > AND <TT CLASS="REPLACEABLE" ><I >y</I ></TT ></PRE ><P> is equivalent to </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >a</I ></TT > >= <TT CLASS="REPLACEABLE" ><I >x</I ></TT > AND <TT CLASS="REPLACEABLE" ><I >a</I ></TT > <= <TT CLASS="REPLACEABLE" ><I >y</I ></TT ></PRE ><P> Similarly, </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >a</I ></TT > NOT BETWEEN <TT CLASS="REPLACEABLE" ><I >x</I ></TT > AND <TT CLASS="REPLACEABLE" ><I >y</I ></TT ></PRE ><P> is equivalent to </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >a</I ></TT > < <TT CLASS="REPLACEABLE" ><I >x</I ></TT > OR <TT CLASS="REPLACEABLE" ><I >a</I ></TT > > <TT CLASS="REPLACEABLE" ><I >y</I ></TT ></PRE ><P> There is no difference between the two respective forms apart from the <ACRONYM CLASS="ACRONYM" >CPU</ACRONYM > cycles required to rewrite the first one into the second one internally. </P ><P > <A NAME="AEN6059" ></A > <A NAME="AEN6061" ></A > <A NAME="AEN6063" ></A > <A NAME="AEN6065" ></A > To check whether a value is or is not null, use the constructs </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS NULL <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS NOT NULL</PRE ><P> or the equivalent, but nonstandard, constructs </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > ISNULL <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > NOTNULL</PRE ><P> <A NAME="AEN6073" ></A > </P ><P > Do <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >not</I ></SPAN > write <TT CLASS="LITERAL" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > = NULL</TT > because <TT CLASS="LITERAL" >NULL</TT > is not <SPAN CLASS="QUOTE" >"equal to"</SPAN > <TT CLASS="LITERAL" >NULL</TT >. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard. </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > Some applications may expect that <TT CLASS="LITERAL" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > = NULL</TT > returns true if <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if that cannot be done the <A HREF="runtime-config.html#GUC-TRANSFORM-NULL-EQUALS" >transform_null_equals</A > configuration variable is available. If it is enabled, <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > will convert <TT CLASS="LITERAL" >x = NULL</TT > clauses to <TT CLASS="LITERAL" >x IS NULL</TT >. This was the default behavior in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > releases 6.5 through 7.1. </P ></BLOCKQUOTE ></DIV ><P > <A NAME="AEN6094" ></A > The ordinary comparison operators yield null (signifying <SPAN CLASS="QUOTE" >"unknown"</SPAN >) when either input is null. Another way to do comparisons is with the <TT CLASS="LITERAL" >IS DISTINCT FROM</TT > construct: </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS DISTINCT FROM <TT CLASS="REPLACEABLE" ><I >expression</I ></TT ></PRE ><P> For non-null inputs this is the same as the <TT CLASS="LITERAL" ><></TT > operator. However, when both inputs are null it will return false, and when just one input is null it will return true. Thus it effectively acts as though null were a normal data value, rather than <SPAN CLASS="QUOTE" >"unknown"</SPAN >. </P ><P > <A NAME="AEN6104" ></A > <A NAME="AEN6106" ></A > <A NAME="AEN6108" ></A > <A NAME="AEN6110" ></A > <A NAME="AEN6112" ></A > <A NAME="AEN6114" ></A > Boolean values can also be tested using the constructs </P><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS TRUE <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS NOT TRUE <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS FALSE <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS NOT FALSE <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS UNKNOWN <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS NOT UNKNOWN</PRE ><P> These will always return true or false, never a null value, even when the operand is null. A null input is treated as the logical value <SPAN CLASS="QUOTE" >"unknown"</SPAN >. Notice that <TT CLASS="LITERAL" >IS UNKNOWN</TT > and <TT CLASS="LITERAL" >IS NOT UNKNOWN</TT > are effectively the same as <TT CLASS="LITERAL" >IS NULL</TT > and <TT CLASS="LITERAL" >IS NOT NULL</TT >, respectively, except that the input expression must be of Boolean type. </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="functions-math.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Functions and Operators</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Mathematical Functions and Operators</TD ></TR ></TABLE ></DIV ></BODY ></HTML >