Sample Header Ad - 728x90

Why are backslashes doubled in SQL's string encoding?

2 votes
1 answer
1352 views
The Postgres docs state: > While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or backslashes, since each of those must be doubled. I understand how single quotes are doubled, since that is how they are escaped according to [section 4.1.2.1](https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS) on string constants. But if backslashes were doubled, then surely this wouldn't be possible:
janus=> \d todo
                            Table "public.todo"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('todo_id_seq'::regclass)
 descr  | text    |           |          | 
 done   | boolean |           |          | 

janus=> truncate table todo;
TRUNCATE TABLE
janus=> insert into todo (descr,done) values ('\''', false);
INSERT 0 1
janus=> select length(descr), descr from todo;
 length | descr 
--------+-------
      2 | \'
(1 row)
As you can see, the backslash before the two single quotes (that are interpreted as one single quote), has no effect, and it is inserted literally. So how is the "standard" syntax referenced in the quote interacting with backslashes? There does not seem to be any special treatment of them, they are like any other character.
Asked by Janus Troelsen (139 rep)
Apr 11, 2023, 01:45 AM
Last activity: Apr 11, 2023, 07:56 AM