A literal is a value written directly in the text of a statement. Most of a dump is literals, so how each dialect writes strings is the first thing a reader has to get right. A wrong reading of one quote changes the meaning of everything after it.
Strings
In standard SQL a string is between single quotes, and a single quote inside it is written twice. Any byte can be inside, including newlines.
'plain text'
'it''s doubled'
'two
lines'The dialects add to this:
| Dialect | Also accepts |
|---|---|
| MySQL, MariaDB | Double quotes for strings, unless the session has ANSI_QUOTES. Backslash escapes in both, unless it has NO_BACKSLASH_ESCAPES. Character set introducers, such as _utf8mb4'text'. |
| PostgreSQL | Dollar quoting, and E'…' strings with backslash escapes. In a plain '…' string a backslash is an ordinary character, provided standard_conforming_strings is on, the default since version 9.1. |
| SQL Server | N'…' for text in the national character set, which is how Unicode text is written. |
| SQLite | Single quotes only are a string in standard use. A double-quoted word that names no column is read as a string in some builds, which the documentation advises against. |
MySQL’s backslash sequences are:
| Sequence | Means |
|---|---|
\0 | NUL byte |
\', \" | A quote |
\b, \n, \r, \t | Backspace, newline, carriage return, tab |
\Z | Character 26 (Control-Z) |
\\ | A backslash |
\%, \_ | The two characters as written, used for patterns |
\ and anything else | That character |
PostgreSQL’s E'…' strings have \b, \f,
\n, \r and \t, octal and hex forms
(\101, \x41), and Unicode forms (A,
\U00000041). Any other character after a backslash stands for itself.
Dollar quoting
PostgreSQL can quote a string between two matching tags, with nothing escaped inside it. The tag can be empty, and otherwise follows the rules for an identifier, without a dollar sign. It is case-sensitive.
$$it's a string, with \ and ' left alone$$
$tag$can hold $$ inside$tag$pg_dump uses dollar quoting for the bodies of functions.
Numbers
Numbers are written plainly, as in 42, -1.5,
.5 and 1e10.
Binary data
| Form | Dialect |
|---|---|
X'4142' | MySQL, SQLite, SQL Standard |
0x4142 | MySQL, SQL Server |
'\x4142' | PostgreSQL bytea in hex format |
b'0101', B'0101' | Bit strings in MySQL and PostgreSQL |
mysqldump writes binary columns as escaped strings unless --hex-blob is
used, in which case they are 0x….
Dates and times
There is no separate literal for dates in dumps. They are written as strings, such as
'2024-05-01 10:00:00', and it is the column’s type that makes them dates.
SQL Server scripts can wrap them, as in
CAST(N'2024-05-01T10:00:00.000' AS DateTime).
NULL and booleans
NULL is the keyword for no value. It differs from an empty string
''. In a COPY block it is written \N.
Booleans are TRUE and FALSE, or t and
f in a COPY block. MySQL and SQLite store them as the numbers
1 and 0.