How Can I Properly Insert Text with Single Quotes into PostgreSQL?

Insert text containing single quotes in PostgreSQL

Inserting values ​​containing single quotes can be tricky in PostgreSQL. However, there are ways to ensure proper escaping.

Use single quotes

To insert values ​​containing single quotes, double them in the string:

<code class="language-sql">INSERT INTO test VALUES (1, 'user''s log');</code>

Escape with backslash

In PostgreSQL versions where standard_conforming_strings is set to off, or when POSIX escaped string syntax is enabled using the E prefix, single quotes can be escaped using backslashes:

<code class="language-sql">INSERT INTO test VALUES (1, E'user\'s log');</code>

Use dollar signs to quote strings

Another option, especially for complex strings containing multiple levels of escaping, is to quote the string with dollar signs:

<code class="language-sql">INSERT INTO test VALUES (1, $token$escape ' with ''$token$);</code>

Functions for quoting strings

PostgreSQL provides functions for correctly quoting strings:

• quote_literal() or quote_nullable(): Quote a string, or return NULL for empty input.
• %L with format() specifier: Equivalent to quote_nullable().

For example:

<code class="language-sql">INSERT INTO test VALUES (1, format('%L', 'user''s log'));</code>

The above is the detailed content of How Can I Properly Insert Text with Single Quotes into PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!