<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.52 2006/09/29 21:22:21 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.53 2007/01/3022:29:22 momjian Exp $ -->

<sect1 id="arrays">

<title>Arrays</title>

</para>

<para>

As shown previously, when writing an array value youmay write double

As shown previously, when writing an array value youcan write double

quotes around any individual array element. You <emphasis>must</> do so

if the element value would otherwise confuse the array-value parser.

For example, elements containing curly braces, commas (or whatever the

delimiter character is), double quotes, backslashes, or leading or trailing

whitespace must be double-quoted. Empty strings and strings matching the

word <literal>NULL</> must be quoted, too. To put a double quote or

backslash in a

quoted array element value,precede it with a backslash. Alternatively, you

can usebackslash-escaping to protect all data characters that would

otherwisebe taken as array syntax.

backslash in a quoted array element value, use escape string syntax

andprecede it with a backslash. Alternatively, you can use

backslash-escaping to protect all data characters that would otherwise

be taken as array syntax.

</para>

<para>

backslashes you need. For example, to insert a <type>text</> array

value containing a backslash and a double quote, you'd need to write

<programlisting>

INSERT ... VALUES ('{"\\\\","\\""}');

INSERT ... VALUES (E'{"\\\\","\\""}');

</programlisting>

The string-literal processor removes one level of backslashes, so that

Theescapestring processor removes one level of backslashes, so that

what arrives at the array-value parser looks like <literal>{"\\","\""}</>.

In turn, the strings fed to the <type>text</> data type's input routine

become <literal>\</> and <literal>"</> respectively. (If we were working

with a data type whose input routine also treated backslashes specially,

<type>bytea</> for example, we might need as many as eight backslashes

in the command to get one backslash into the stored array element.)

Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">)may be

Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">)can be

used to avoid the need to double backslashes.

</para>

</note>

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.187 2007/01/29 13:24:30 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.188 2007/01/30 22:29:22 momjian Exp $ -->

<chapter id="datatype">

<title id="datatype-title">Data Types</title>

of a string literal in an <acronym>SQL</acronym> statement. In

general, to escape an octet, it is converted into the three-digit

octal number equivalent of its decimal octet value, and preceded

by two backslashes (or one backslash if

<varname>standard_conforming_strings</> is <literal>off</>).

<xref linkend="datatype-binary-sqlesc"> shows the characters

that must be escaped, and gives the alternate escape sequences

where applicable.

by two backslashes. <xref linkend="datatype-binary-sqlesc">

shows the characters that must be escaped, and gives the alternate

escape sequences where applicable.

</para>

<table id="datatype-binary-sqlesc">

<row>

<entry>0</entry>

<entry>zero octet</entry>

<entry><literal>'\\000'</literal></entry>

<entry><literal>SELECT '\\000'::bytea;</literal></entry>

<entry><literal>E'\\000'</literal></entry>

<entry><literal>SELECTE'\\000'::bytea;</literal></entry>

<entry><literal>\000</literal></entry>

</row>

<row>

<entry>39</entry>

<entry>single quote</entry>

<entry><literal>'\''</literal> or <literal>'\\047'</literal></entry>

<entry><literal>SELECT '\''::bytea;</literal></entry>

<entry><literal>''''</literal> or <literal>E'\\047'</literal></entry>

<entry><literal>SELECTE'\''::bytea;</literal></entry>

<entry><literal>'</literal></entry>

</row>

<row>

<entry>92</entry>

<entry>backslash</entry>

<entry><literal>'\\\\'</literal> or <literal>'\\134'</literal></entry>

<entry><literal>SELECT '\\\\'::bytea;</literal></entry>

<entry><literal>E'\\\\'</literal> or <literal>E'\\134'</literal></entry>

<entry><literal>SELECTE'\\\\'::bytea;</literal></entry>

<entry><literal>\\</literal></entry>

</row>

<row>

<entry>0 to 31 and 127 to 255</entry>

<entry><quote>non-printable</quote> octets</entry>

<entry><literal>'\\<replaceable>xxx'</></literal> (octal value)</entry>

<entry><literal>SELECT '\\001'::bytea;</literal></entry>

<entry><literal>E'\\<replaceable>xxx'</></literal> (octal value)</entry>

<entry><literal>SELECTE'\\001'::bytea;</literal></entry>

<entry><literal>\001</literal></entry>

</row>

string written as a string literal must pass through two parse

phases in the <productname>PostgreSQL</productname> server.

The first backslash of each pair is interpreted as an escape

character by the string-literal parser (assuming

<varname>standard_conforming_strings</> is<literal>off</>)

pair. The remaining backslash is then recognized by the

character by the string-literal parser (assuming escape string

syntax isused) and is therefore consumed, leaving the second backslash of the

of escaping.) The remaining backslash is then recognized by the

<type>bytea</type> input function as starting either a three

digit octal value or escaping another backslash. For example,

a string literal passed to the server as <literal>'\\001'</literal>

a string literal passed to the server as <literal>E'\\001'</literal>

becomes <literal>\001</literal> after passing through the

string-literal parser. The <literal>\001</literal> is then sent

escapestring parser. The <literal>\001</literal> is then sent

to the <type>bytea</type> input function, where it is converted

to a single octet with a decimal value of 1. Note that the

apostrophe character is not treated specially by <type>bytea</type>,

single-quote character is not treated specially by <type>bytea</type>,

so it follows the normal rules for string literals. (See also

<xref linkend="sql-syntax-strings">.)

</para>

<entry>92</entry>

<entry>backslash</entry>

<entry><literal>\\</literal></entry>

<entry><literal>SELECT '\\134'::bytea;</literal></entry>

<entry><literal>SELECTE'\\134'::bytea;</literal></entry>

<entry><literal>\\</literal></entry>

</row>

<row>

<entry>0 to 31 and 127 to 255</entry>

<entry><quote>non-printable</quote> octets</entry>

<entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>

<entry><literal>SELECT '\\001'::bytea;</literal></entry>

<entry><literal>SELECTE'\\001'::bytea;</literal></entry>

<entry><literal>\001</literal></entry>

</row>

<row>

<entry>32 to 126</entry>

<entry><quote>printable</quote> octets</entry>

<entry>client character set representation</entry>

<entry><literal>SELECT '\\176'::bytea;</literal></entry>

<entry><literal>SELECTE'\\176'::bytea;</literal></entry>

<entry><literal>~</literal></entry>

</row>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.354 2007/01/3002:39:27 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.355 2007/01/3022:29:22 momjian Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

Encode binary data to <acronym>ASCII</acronym>-only representation. Supported

types are: <literal>base64</>, <literal>hex</>, <literal>escape</>.

</entry>

<entry><literal>encode( '123\\000\\001', 'base64')</literal></entry>

<entry><literal>encode(E'123\\000\\001', 'base64')</literal></entry>

<entry><literal>MTIzAAE=</literal></entry>

</row>

<entry>

Return the given string suitably quoted to be used as a string literal

in an <acronym>SQL</acronym> statement string.

Embedded quotes and backslashes are properly doubled.

Embeddedsingle-quotes and backslashes are properly doubled.

</entry>

<entry><literal>quote_literal( 'O\'Reilly')</literal></entry>

<entry><literal>'O''Reilly'</literal></entry>

<secondary>concatenation</secondary>

</indexterm>

</entry>

<entry><literal>'\\\\Post'::bytea || '\\047gres\\000'::bytea</literal></entry>

<entry><literal>E'\\\\Post'::bytea ||E'\\047gres\\000'::bytea</literal></entry>

<entry><literal>\\Post'gres\000</literal></entry>

</row>

<primary>get_bit</primary>

</indexterm>

</entry>

<entry><literal>get_bit('Th\\000omas'::bytea, 45)</literal></entry>

<entry><literal>get_bit(E'Th\\000omas'::bytea, 45)</literal></entry>

<entry><literal>1</literal></entry>

</row>

<primary>get_byte</primary>

</indexterm>

</entry>

<entry><literal>get_byte('Th\\000omas'::bytea, 4)</literal></entry>

<entry><literal>get_byte(E'Th\\000omas'::bytea, 4)</literal></entry>

<entry><literal>109</literal></entry>

</row>

<row>

<entry><literal><function>octet_length</function>(<parameter>string</parameter>)</literal></entry>

<entry><type>int</type></entry>

<entry>Number of bytes in binary string</entry>

<entry><literal>octet_length( 'jo\\000se'::bytea)</literal></entry>

<entry><literal>octet_length(E'jo\\000se'::bytea)</literal></entry>

<entry><literal>5</literal></entry>

</row>

<row>

<entry><literal><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</literal></entry>

<entry><type>int</type></entry>

<entry>Location of specified substring</entry>

<entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>

<entry><literal>position(E'\\000om'::bytea inE'Th\\000omas'::bytea)</literal></entry>

<entry><literal>3</literal></entry>

</row>

<primary>set_bit</primary>

</indexterm>

</entry>

<entry><literal>set_bit('Th\\000omas'::bytea, 45, 0)</literal></entry>

<entry><literal>set_bit(E'Th\\000omas'::bytea, 45, 0)</literal></entry>

<entry><literal>Th\000omAs</literal></entry>

</row>

<primary>set_byte</primary>

</indexterm>

</entry>

<entry><literal>set_byte('Th\\000omas'::bytea, 4, 64)</literal></entry>

<entry><literal>set_byte(E'Th\\000omas'::bytea, 4, 64)</literal></entry>

<entry><literal>Th\000o@as</literal></entry>

</row>

<primary>substring</primary>

</indexterm>

</entry>

<entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>

<entry><literal>substring(E'Th\\000omas'::bytea from 2 for 3)</literal></entry>

<entry><literal>h\000o</literal></entry>

</row>

<parameter>bytes</parameter> from the start

and end of <parameter>string</parameter>

</entry>

<entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>

<entry><literal>trim(E'\\000'::bytea fromE'\\000Tom\\000'::bytea)</literal></entry>

<entry><literal>Tom</literal></entry>

</row>

</tbody>

in <parameter>bytes</parameter> from the start and end of

<parameter>string</parameter>

</entry>

<entry><literal>btrim('\\000trim\\000'::bytea, '\\000'::bytea)</literal></entry>

<entry><literal>btrim(E'\\000trim\\000'::bytea,E'\\000'::bytea)</literal></entry>

<entry><literal>trim</literal></entry>

</row>

Decode binary string from <parameter>string</parameter> previously

encoded with <function>encode</>. Parameter type is same as in <function>encode</>.

</entry>

<entry><literal>decode('123\\000456', 'escape')</literal></entry>

<entry><literal>decode(E'123\\000456', 'escape')</literal></entry>

<entry><literal>123\000456</literal></entry>

</row>

Encode binary string to <acronym>ASCII</acronym>-only representation. Supported

types are: <literal>base64</>, <literal>hex</>, <literal>escape</>.

</entry>

<entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>

<entry><literal>encode(E'123\\000456'::bytea, 'escape')</literal></entry>

<entry><literal>123\000456</literal></entry>

</row>

<see>binary strings, length</see>

</indexterm>

</entry>

<entry><literal>length('jo\\000se'::bytea)</literal></entry>

<entry><literal>length(E'jo\\000se'::bytea)</literal></entry>

<entry><literal>5</literal></entry>

</row>

Calculates the MD5 hash of <parameter>string</parameter>,

returning the result in hexadecimal

</entry>

<entry><literal>md5('Th\\000omas'::bytea)</literal></entry>

<entry><literal>md5(E'Th\\000omas'::bytea)</literal></entry>

<entry><literal>8ab2d3c9689aaf18 b4958c334c82d8b1</literal></entry>

</row>

</tbody>

<para>

Note that the backslash already has a special meaning in string

literals, so to write a pattern constant that contains a backslash

you must write two backslashes in an SQL statement. Thus, writing a pattern

you must write two backslashes in an SQL statement (assuming escape

string syntax is used). Thus, writing a pattern

that actually matches a literal backslash means writing four backslashes

in the statement. You can avoid this by selecting a different escape

character with <literal>ESCAPE</literal>; then a backslash is not special

substring matching the entire pattern should be inserted. Write

<literal>\\</> if you need to put a literal backslash in the replacement

text. (As always, remember to double backslashes written in literal

constant strings.)

constant strings, assuming escape string syntax is used.)

The <replaceable>flags</> parameter is an optional text

string containing zero or more single-letter flags that change the

function's behavior. Flag <literal>i</> specifies case-insensitive

<lineannotation>fooXbaz</lineannotation>

regexp_replace('foobarbaz', 'b..', 'X', 'g')

<lineannotation>fooXX</lineannotation>

regexp_replace('foobarbaz', 'b(..)', 'X\\1Y', 'g')

regexp_replace('foobarbaz', 'b(..)',E'X\\1Y', 'g')

<lineannotation>fooXarYXazY</lineannotation>

</programlisting>

</para>

Remember that the backslash (<literal>\</literal>) already has a special

meaning in <productname>PostgreSQL</> string literals.

To write a pattern constant that contains a backslash,

you must write two backslashes in the statement.

you must write two backslashes in the statement, assuming escape

string syntax is used.

</para>

</note>

Keep in mind that an escape's leading <literal>\</> will need to be

doubled when entering the pattern as an SQL string constant. For example:

<programlisting>

'123' ~ '^\\d{3}' <lineannotation>true</lineannotation>

'123' ~E'^\\d{3}' <lineannotation>true</lineannotation>

</programlisting>

</para>

</note>

<listitem>

<para>

If you want to have a double quote in the output you must

precede it with a backslash, for example <literal>'\\"YYYY

precede it with a backslash, for example <literal>E'\\"YYYY

Month\\"'</literal>. <!-- "" font-lock sanity :-) -->

(Two backslashes are necessary because the backslash already

has a special meaningin astringconstant.)

has a special meaningwhen using the escapestringsyntax.)

</para>

</listitem>

<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.221 2006/12/19 01:53:36 adunstan Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.222 2007/01/30 22:29:22 momjian Exp $ -->

<chapter id="libpq">

<title><application>libpq</application> - C Library</title>

<para>

If no value for <literal>standard_conforming_strings</> is reported,

applications may assume it is <literal>false</>, that is, backslashes

applications may assume it is <literal>off</>, that is, backslashes

are treated as escapes in string literals. Also, the presence of this

parameter may be taken as an indication that the escape string syntax

(<literal>E'...'</>) is accepted.

of a <type>bytea</type> literal in an <acronym>SQL</acronym>

statement. In general, to escape a byte, it is converted into the

three digit octal number equal to the octet value, and preceded by

one or two backslashes. The single quote (<literal>'</>) and backslash

usually two backslashes. The single quote (<literal>'</>) and backslash

(<literal>\</>) characters have special alternative escape

sequences. See <xref linkend="datatype-binary"> for more

information. <function>PQescapeByteaConn</function> performs this