CODEWITHSUNDEEP
postgresqlpostgisstring-literalswkb
thor
thor

Reputation: 22530

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

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.

Upvotes: 54

Views: 34681

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658422

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.

Upvotes: 11

Nor.Z
Nor.Z

Reputation: 1409

ex:

(in & after version 9.1)

select '\n'; -- \n -- literal string <=> r'\n' in py
select E'\n'; -- (a newline) -- escapable string
select '\u0024'; -- \u0024
select E'\u0024'; -- $ (a dollar sign)
select '\\u0024'; -- \\u0024
select E'\\u0024'; -- \u0024

--

warning for: standard_conforming_strings in diff version

If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. However, as of PostgreSQL 9.1, the default is on, meaning that backslash escapes are recognized only in escape string constants.

https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

Upvotes: 2

Dai
Dai

Reputation: 155568

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.

Upvotes: 53

Related Questions