What's the "E" before a Postgres string?

Question

I was reading a Postgres/PostGIS statement like this:

SELECT ST_AsBinary( ST_GeomFromWKB(   E'\\001\\001\\000\\000\\000\\321\\256B\\312O\\304Q\\300\\347\\030\\220\\275\\336%E@',   4326   ) ); 

The above creates something from a Well Known Binary (WKB). I haven't seen the specific way of quoting here where the string is single quoted with a E preceding the beginning quote.

What is this format called? And what are the formatting rules for this? e.g. is the 336%E@ at the very end special or just some binary value?

This is with Postgres9.3/9.4; PostGIS 2.1.

Answer 1

As per the PostgreSQL documentation https://www.postgresql.org/docs/9.0/sql-syntax-lexical.html (emphasis mine)

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent a special byte value

The use of \\ in your string means that it's escaping an escape sequence, probably to be safe in transit and storage in a .sql file. The verbatim string actually passed into the ST_GeomFromWKB function will be:

\001\001\000\000\000\321\256B\312O\304Q\300\347\030\220\275\336%E@ 

These sequences of 3 or 4 characters between slashes would then be interpreted by ST_GeoFromWKB directly.

The documentation for ST_GeoFromWKB ( https://postgis.net/docs/ST_GeomFromWKB.html ) states:

The ST_GeomFromWKB function, takes a well-known binary representation of a geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type. This function plays the role of the Geometry Factory in SQL. This is an alternate name for ST_WKBToSQL.

Unfortunately it doesn't state what format, exactly, the "well-known binary representation" actually is.

It turns out that the content of the string depends on the coordinate system you're using, which is specified by the SRID parameter. In this case 4326 corresponds to WGS84: https://en.wikipedia.org/wiki/World_Geodetic_System#WGS84

You'll need to do further reading and research to untangle that.

Answer 2

What you see does not look like hexadecimal, because the bytea string literal is in escape string syntax (which is rather outdated nowadays).

E'\\001\\001\\000\\000\\000\\321\\256B\\312O\\304Q\\300\\347\\030\\220\\275\\336%E@' 

The same as "standard conforming string":

'\001\001\000\000\000\321\256B\312O\304Q\300\347\030\220\275\336%E@' 

Both are in "escape format", which can be represented more efficiently in "hex format" as:

'\x0101000000d1ae42ca4fc451c0e71890bdde254540' 

You can use encode() and decode() to transform one form into the other.

I answered your follow-up question on gis.SE with more details.