postgresql how to insert a value with double quotes inside double quotes

0
votes

This is the sql code that I run on my postgresql database. It turns out to cause errors

INSERT INTO t1(
   group1
)
VALUES
("GIRL''S 16'" AND LESS BIKE");

but if I delete the double quotes after 16', it works fine. So I wonder is there a way to keep the double quote inside but also insert successfully into the table?

3
postgresql
String constants need to be enclosed in single quotes. Double quote do not need to be escaped. See the manual for details: postgresql.org/docs/current/static/… – a_horse_with_no_name

3 Answers

2
votes

I don't believe it will work fine if you remove one of the ", as that would be illegal SQL.

Strings always have to be surrounded by single quotes ('); a double quote (") has no special meaning.

This would be a correct string:

'GIRL''S 16" AND LESS BIKE'

except that would really be for large girls.

1
votes

Just encapsulate whatever text you need in double quotes under single quotes.

Example:

INSERT INTO t1
(group1)
VALUES
('"GIRL S 16" AND LESS BIKE');
1
votes

What about using dollar-quotes?

SELECT $$GIRL'S 16" AND LESS BIKE$$

Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.