<!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.3.6 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Logical Operators" HREF="functions-logical.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="2009-02-03T04:34:16"></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.3.6 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions-logical.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="AEN6681" ></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="AEN6731" ></A > In addition to the comparison operators, the special <TT CLASS="TOKEN" >BETWEEN</TT > construct is available. </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. <A NAME="AEN6753" ></A > <TT CLASS="TOKEN" >BETWEEN SYMMETRIC</TT > is the same as <TT CLASS="LITERAL" >BETWEEN</TT > except there is no requirement that the argument to the left of <TT CLASS="LITERAL" >AND</TT > be less than or equal to the argument on the right; the proper range is automatically determined. </P ><P > <A NAME="AEN6759" ></A > <A NAME="AEN6761" ></A > <A NAME="AEN6763" ></A > <A NAME="AEN6765" ></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="AEN6773" ></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 might 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-compatible.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 ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > If the <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > is row-valued, then <TT CLASS="LITERAL" >IS NULL</TT > is true when the row expression itself is null or when all the row's fields are null, while <TT CLASS="LITERAL" >IS NOT NULL</TT > is true when the row expression itself is non-null and all the row's fields are non-null. This definition conforms to the SQL standard, and is a change from the inconsistent behavior exhibited by <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > versions prior to 8.2. </P ></BLOCKQUOTE ></DIV ><P > <A NAME="AEN6800" ></A > <A NAME="AEN6802" ></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 [<SPAN CLASS="OPTIONAL" > NOT </SPAN >] 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 > <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > IS NOT DISTINCT FROM <TT CLASS="REPLACEABLE" ><I >expression</I ></TT ></PRE ><P> For non-null inputs, <TT CLASS="LITERAL" >IS DISTINCT FROM</TT > 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. Similarly, <TT CLASS="LITERAL" >IS NOT DISTINCT FROM</TT > is identical to <TT CLASS="LITERAL" >=</TT > for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than <SPAN CLASS="QUOTE" >"unknown"</SPAN >. </P ><P > <A NAME="AEN6818" ></A > <A NAME="AEN6820" ></A > <A NAME="AEN6822" ></A > <A NAME="AEN6824" ></A > <A NAME="AEN6826" ></A > <A NAME="AEN6828" ></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-logical.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" >Logical 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 >