/ postgresql

Escape character sequence "E" in PostgreSQL explained

You might have encountered situations like where you have to insert some data into your PostgreSQL table that has special characters.

eg: this is a very large sentence \n and hence this is broken down into two sentence

and when you try to do that without the escape sequence postgreSQL starts to behave wieirdly with you, and might start throwing errors like:

nonstandard use of escape in a string literal

Most often this is handle in the code where the programming language takes care of this for you or the ORM do the magic for you.

If you are interested in knowing a bit more about the escape character sequences in PostgreSQL, keep reading.

Escape String Constants.

PostgreSQL also has the C-styled escape characters as it closely follows the SQL standard. In postgreSQL you can specify the escape character by prefixing the letter E

From the PostgreSQL docs

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

So you can write the above statement using the escape sequence as:

E'this is a very large sentence \n and hence this is broken down into two sentence'

This is particularly useful when you are using the PostgreSQL \copy statement and trying to load the data from a CSV file.

Manu S Ajith

Manu S Ajith

Tech Entrepreneur, dating Elixir, in long-term ❤️ w/ Ruby, had multiple one night stands w/ Go. Into functional paradigms DDD/CQRS/EventSourcing architecture these days. @manusajith on the interwebs

Read More