You might have encountered situations like where you have to insert some data into your PostgreSQL table that has special characters.
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
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.
Subscribe to Today I learned
Get the latest posts delivered right to your inbox