Description

pg_escape_bytea returns its argument with added characters which makes it safe for use in single-quoted SQL string constants. Unlike pg_escape_string, which works on text strings, pg_escape_bytea is meant for arbitrary binary data meant to be stored or used with PostgreSQL bytea (byte array) columns. This command allows arbitrary binary data to be safely sent through the PostgreSQL query mechanism in a text mode query. (An alternative to escaping and unescaping binary data is to use binary mode prepared queries. See pg_exec_prepared.)

Arguments

Return Value

The escaped data string. When placed in single quotes, it can safely be used in SQL statements.

Notes

See also pg_unescape_bytea, but note that these commands are not inverses of each other. pg_escape_bytea creates output that is meant to go through two levels of parsing by the database: one when parsing SQL, and one when processing the bytea data type. For example, a quote mark in the input string is translated to two quote marks by pg_escape_bytea, but is not translated back by pg_unescape_bytea.

The pgintcl implementation of this command is slow. Consider using binary prepared queries instead, to avoid having to escape and unescape your binary data.

This command was added in pgtclng-1.5.2 and in pgintcl-2.2.0.

The optional conn argument was added to this command in pgtclng-1.7.0 and in pgintcl-3.1.0.

The conn argument is used to get the state of the PostgreSQL server configuration parameter standard_conforming_strings. This is used to determine if backslashes need to be doubled. If the conn parameter is supplied, the setting can be accurately determined for the specific database connection being used. (Note that this information is maintained on the client side and does not necessitate any additional database server traffic.)

If the conn parameter is not supplied, a best guess will be made based on the most recent information from any database connection by the client. Therefore, if the client makes a single database connection, or multiple connections with the same setting for standard_conforming_strings, there is no need to provide the conn parameter.

This command uses or emulates the PostgreSQL libpq function PQescapeBytea, if no connection handle is supplied, or PQescapeByteaConn if a connection handle is supplied.

See Section 5.9, “Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures” for an example.

PostgreSQL-9.0.0 introduced hex mode encoding of bytea strings as an alternative to the escape mode encoding. In libpq-based pgtcl-ng, pg_escape_bytea returns a string with hex encoding if a connection handle is supplied and the connection is to a PostgreSQL 9.0 or higher database. In all other cases (no connection handle supplied, or connected to an older database), pgtcl-ng uses escape mode encoding. In the pure-Tcl pgintcl, pg_escape_bytea always returns a string with escape mode encoding.