<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Array Functions and 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="Conditional Expressions" HREF="functions-conditional.html"><LINK REL="NEXT" TITLE="Aggregate Functions" HREF="functions-aggregate.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-conditional.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-aggregate.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-ARRAY" >9.14. Array Functions and Operators</A ></H1 ><P > <A HREF="functions-array.html#ARRAY-OPERATORS-TABLE" >Table 9-35</A > shows the operators available for <TT CLASS="TYPE" >array</TT > types. </P ><DIV CLASS="TABLE" ><A NAME="ARRAY-OPERATORS-TABLE" ></A ><P ><B >Table 9-35. <TT CLASS="TYPE" >array</TT > Operators</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><THEAD ><TR ><TH >Operator</TH ><TH >Description</TH ><TH >Example</TH ><TH >Result</TH ></TR ></THEAD ><TBODY ><TR ><TD > <TT CLASS="LITERAL" >=</TT > </TD ><TD >equal</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]</TT ></TD ><TD ><TT CLASS="LITERAL" >t</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><></TT > </TD ><TD >not equal</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,2,3] <> ARRAY[1,2,4]</TT ></TD ><TD ><TT CLASS="LITERAL" >t</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><</TT > </TD ><TD >less than</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,2,3] < ARRAY[1,2,4]</TT ></TD ><TD ><TT CLASS="LITERAL" >t</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >></TT > </TD ><TD >greater than</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,4,3] > ARRAY[1,2,4]</TT ></TD ><TD ><TT CLASS="LITERAL" >t</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><=</TT > </TD ><TD >less than or equal</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,2,3] <= ARRAY[1,2,3]</TT ></TD ><TD ><TT CLASS="LITERAL" >t</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >>=</TT > </TD ><TD >greater than or equal</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,4,3] >= ARRAY[1,4,3]</TT ></TD ><TD ><TT CLASS="LITERAL" >t</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >||</TT > </TD ><TD >array-to-array concatenation</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,2,3] || ARRAY[4,5,6]</TT ></TD ><TD ><TT CLASS="LITERAL" >{1,2,3,4,5,6}</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >||</TT > </TD ><TD >array-to-array concatenation</TD ><TD ><TT CLASS="LITERAL" >ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]</TT ></TD ><TD ><TT CLASS="LITERAL" >{{1,2,3},{4,5,6},{7,8,9}}</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >||</TT > </TD ><TD >element-to-array concatenation</TD ><TD ><TT CLASS="LITERAL" >3 || ARRAY[4,5,6]</TT ></TD ><TD ><TT CLASS="LITERAL" >{3,4,5,6}</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" >||</TT > </TD ><TD >array-to-element concatenation</TD ><TD ><TT CLASS="LITERAL" >ARRAY[4,5,6] || 7</TT ></TD ><TD ><TT CLASS="LITERAL" >{4,5,6,7}</TT ></TD ></TR ></TBODY ></TABLE ></DIV ><P > See <A HREF="arrays.html" >Section 8.10</A > for more details about array operator behavior. </P ><P > <A HREF="functions-array.html#ARRAY-FUNCTIONS-TABLE" >Table 9-36</A > shows the functions available for use with array types. See <A HREF="arrays.html" >Section 8.10</A > for more discussion and examples of the use of these functions. </P ><DIV CLASS="TABLE" ><A NAME="ARRAY-FUNCTIONS-TABLE" ></A ><P ><B >Table 9-36. <TT CLASS="TYPE" >array</TT > Functions</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><COL><THEAD ><TR ><TH >Function</TH ><TH >Return Type</TH ><TH >Description</TH ><TH >Example</TH ><TH >Result</TH ></TR ></THEAD ><TBODY ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_cat</CODE > (<TT CLASS="TYPE" >anyarray</TT >, <TT CLASS="TYPE" >anyarray</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >anyarray</TT ></TD ><TD >concatenate two arrays</TD ><TD ><TT CLASS="LITERAL" >array_cat(ARRAY[1,2,3], ARRAY[4,5])</TT ></TD ><TD ><TT CLASS="LITERAL" >{1,2,3,4,5}</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_append</CODE > (<TT CLASS="TYPE" >anyarray</TT >, <TT CLASS="TYPE" >anyelement</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >anyarray</TT ></TD ><TD >append an element to the end of an array</TD ><TD ><TT CLASS="LITERAL" >array_append(ARRAY[1,2], 3)</TT ></TD ><TD ><TT CLASS="LITERAL" >{1,2,3}</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_prepend</CODE > (<TT CLASS="TYPE" >anyelement</TT >, <TT CLASS="TYPE" >anyarray</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >anyarray</TT ></TD ><TD >append an element to the beginning of an array</TD ><TD ><TT CLASS="LITERAL" >array_prepend(1, ARRAY[2,3])</TT ></TD ><TD ><TT CLASS="LITERAL" >{1,2,3}</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_dims</CODE > (<TT CLASS="TYPE" >anyarray</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >text</TT ></TD ><TD >returns a text representation of array's dimensions</TD ><TD ><TT CLASS="LITERAL" >array_dims(array[[1,2,3], [4,5,6]])</TT ></TD ><TD ><TT CLASS="LITERAL" >[1:2][1:3]</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_lower</CODE > (<TT CLASS="TYPE" >anyarray</TT >, <TT CLASS="TYPE" >integer</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >returns lower bound of the requested array dimension</TD ><TD ><TT CLASS="LITERAL" >array_lower(array_prepend(0, ARRAY[1,2,3]), 1)</TT ></TD ><TD ><TT CLASS="LITERAL" >0</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_upper</CODE > (<TT CLASS="TYPE" >anyarray</TT >, <TT CLASS="TYPE" >integer</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >returns upper bound of the requested array dimension</TD ><TD ><TT CLASS="LITERAL" >array_upper(ARRAY[1,2,3,4], 1)</TT ></TD ><TD ><TT CLASS="LITERAL" >4</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >array_to_string</CODE > (<TT CLASS="TYPE" >anyarray</TT >, <TT CLASS="TYPE" >text</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >text</TT ></TD ><TD >concatenates array elements using provided delimiter</TD ><TD ><TT CLASS="LITERAL" >array_to_string(array[1, 2, 3], '~^~')</TT ></TD ><TD ><TT CLASS="LITERAL" >1~^~2~^~3</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" > <CODE CLASS="FUNCTION" >string_to_array</CODE > (<TT CLASS="TYPE" >text</TT >, <TT CLASS="TYPE" >text</TT >) </TT > </TD ><TD ><TT CLASS="TYPE" >text[]</TT ></TD ><TD >splits string into array elements using provided delimiter</TD ><TD ><TT CLASS="LITERAL" >string_to_array( 'xx~^~yy~^~zz', '~^~')</TT ></TD ><TD ><TT CLASS="LITERAL" >{xx,yy,zz}</TT ></TD ></TR ></TBODY ></TABLE ></DIV ></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-conditional.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-aggregate.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Conditional Expressions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Aggregate Functions</TD ></TR ></TABLE ></DIV ></BODY ></HTML >