The Quote_Literal Function in Postgres SQL
In this article, we will study the Quote_Literal function in Postgres SQL, including the following perspectives:
- What? What is the general idea and syntax of the QUOTE_LITERAL() function?
- Why? In what circumstances do we use the QUOTE_LITERAL function in our PostgreSQL queries?
- How? How do we use the QUOTE_LITERAL function in our SQL?
- Similar functions We’ll also look at some functions that may complement or be used in place of QUOTE_LITERAL(), including the CHR() function.
- Additional concepts We’ll use some Python and the Postgres “||” concatenation symbols and CASE value branch statement.
The QUOTE_LITERAL function overview, syntax, and parameters
As you probably understand, apostrophe (otherwise known as semi-quotes or single quotes) and quote symbols are used in Postgres and many programming languages to delineate text strings and string-based commands. For example, a typical WHERE clause may look like
WHERE t_name = 'database'. Things can get tricky when we want to send actual apostrophes or quotes as part of our string. Like, if an apostrophe is part of a name, like so:
Franky Threefist O'Brien. How would we include something like that in a WHERE clause where we use apostrophes to delineate? Stay tuned because we’re going to show you.
Before we go further, an important bit of information: There is more than one way to accomplish this feat, including “escaping”, use of the “chr()” function, use of the “format()” function, and the SQL “USING” command. An advantage to “CHR()” is simplicity and advantages to “USING” are performance and safety, as it is parameterized. But you searched for “quote_literal” so that is what we’ll instruct on – mostly – in this article – for now. That said, we will next show you how to use the “CHR” function to accomplish the same task.
- “t_string” is the string we supplied that has a special character – like a single quote in the middle of our string – we need to deal with. So if we supply our quote_literal function with a t_string of “Franky Threefist O’Brien”, the result we get back is “Franky Threefist O\’Brien” where the apostrophe is “escaped” so that apostrophes can be used to encapsulate the entire phrase.
Why use the Quote_Literal function?
No good reason now. This is a legacy function we do not recommend using. Instead, as mentioned above, use a more modern alternative, such as…
An alternative we recommend, the CHR function.
Let’s begin by looking at some Python code where we will include the quote character in an SQL string we are building:
First, we want to be sure you know the ASCII code for
"is 34 and the ASCII code for
We’ll begin with using the ASCII code to UPDATE our database with a word that includes the
' character. In other words, let’s say we want to convert all instances of “They will” with “They’ll”.
First, before getting into building an SQL script, let’s build a simple text string of that contraction word using Python:
# In Python
t_word = 'They' + CHR(39) + 'll'
-- In Postgres PL/SQL
t_word := 'They' || CHR(39) || 'll';
t_word now has a value of “They’ll” because we’ve combined “They” with the ASCII code for the “‘” symbol, used the CHR() function to insert that symbol, and then concatenated “ll” to the end.
Now let’s see how this works in a string we’re building using Python in order to change values in a PostgreSQL database table.
# In Python
# UPDATE from non-contraction to contraction
s = ""
s += "UPDATE users"
s += " SET"
s += " t_who = 'They" || CHR(39) || "ll'"
s += " WHERE"
s += " ("
s += " t_who = 'They will'"
s += " )"
UPDATE: Telling PostgreSQL which table we want to update; in this instance, “users”.
SET: Here we are using CHR(39), which generates the “‘” character. We’re asking Postgres here to, “Build a string for us. Start with “They”, add “‘”, and then add “ll” to the end. Notice how the word we are building is encapsulated – as a standard SQL practice – within “‘” symbols? That’s why we need CHR here!
WHERE: Filtering our data-to-change so only the phrase of “They will” is replaced with the text string we built in the SET section above.
The code above changes the data in this table
Note that every case of “They will” was changed to “They’ll”.
Now, let’s study the CHR function more by going more real world!
The goal for our mini-project: Compress space in our database by replacing all contraction-able words with contractions
First, we’ll need a list of words that we know contractions exist for. We’ll use the PostgreSQL CASE clause. We could instead choose to loop through the array in some Python code to rerun many queries, but the more efficient route is to do it all in SQL.
A reminder of what our original data looks like:
We’ll begin by looking at the data and picking out the words that lend themselves to being changed into contractions, like “they will”, “we will”, “You will”, and “I will”. We know there are many other “contraction-able” words, but the way we will set up our code is so that you can add as many as you want to the word list with ease.
A simple solution:
t_who = t_who_contracted
WHEN t_who = 'I will' THEN 'I' || CHR(39) || 'll'
WHEN t_who = 'They will' THEN 'They' || CHR(39) || 'll'
WHEN t_who = 'We will' THEN 'We' || CHR(39) || 'll'
WHEN t_who = 'You will' THEN 'You' || CHR(39) || 'll'
END AS t_who_contracted
UPDATE: Designating which PostgreSQL table we want to modify; “users”.
SET: Telling Postgres that we want to set the value for the current row of the “t_who” column to be equal to the results given us in the following “CASE” statement.
CASE: Here we choose from four different potential values that may be found in the “t_who” column, and depending on those values, we build our new replacement string using concatenation (“||”) and the CHR function. Remember, here that CHR(39) gives us the
'(single quote) symbol.
END AS: Instructs Postgres SQL what to call the results of the CASE statement.
In this article we learned how the Quote_Literal function works in Postgres SQL, why not to use it, and why to instead use either the CHR function, “USING”, or even Format, depending on your specific needs. We also used the “||” symbol combination for string concatenation. We also used some Python. Finally, we learned how to use the “CASE” statement in order to try different values in our test project.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started