<!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 9.0.15 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="2014-01-24T13:16:52"></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" ><A HREF="index.html" >PostgreSQL 9.0.15 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Logical Operators" HREF="functions-logical.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="Mathematical Functions and Operators" 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 ><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 relevant data types. 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 > 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> Notice that <TT CLASS="TOKEN" >BETWEEN</TT > treats the endpoint values as included in the range. <TT CLASS="LITERAL" >NOT BETWEEN</TT > does the opposite comparison: </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> <TT CLASS="LITERAL" >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. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied. </P ><P > 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> </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 >. </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. Because of this behavior, <TT CLASS="LITERAL" >IS NULL</TT > and <TT CLASS="LITERAL" >IS NOT NULL</TT > do not always return inverse results for row-valued expressions, i.e., a row-valued expression that contains both NULL and non-null values will return false for both tests. 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 > Ordinary comparison operators yield null (signifying <SPAN CLASS="QUOTE" >"unknown"</SPAN >), not true or false, when either input is null. For example, <TT CLASS="LITERAL" >7 = NULL</TT > yields null. When this behavior is not suitable, use the <TT CLASS="LITERAL" >IS [<SPAN CLASS="OPTIONAL" > NOT </SPAN >] DISTINCT FROM</TT > constructs: </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, if both inputs are null it returns false, and if only one input is null it returns 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 > 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 >