Subject: Having issues inserting a string with a ' into a varchar column

From: Sbob <[email-masked]>
Date: Fri, 15 Apr 2022 12:01:31 -0600

Hi all;

I have a table that includes a varchar column.

I am inserting some data and one of the strings = "Poker 5 card Hold'Em"

as a straight string it errors out.

I tried this :  quote_ident ( 'Poker 5 card Hold'Em')

but it still error's out

I also tried : quote_literal('Poker 5 card Hold\'Em')

but it also errors out:

psql:test.sql:1042: ERROR:  syntax error at or near "Em"
LINE 1: ...ed) VALUES ( quote_literal('Poker 5 card Hold\\'Em'), 2, 1...

Thanks in advance

---

From: Sbob <[email-masked]>
Date: Fri, 15 Apr 2022 12:02:57 -0600

correction, the quote_ident below should be quote_literal

I did not try quote_ident

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 15 Apr 2022 11:16:31 -0700

> I also tried :  quote_literal('Poker 5 card Hold\'Em')
You have the right idea you need to escape it.  But guessing isn’t a good
method to figure out how.

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

You can’t use quote_* on a literal if you do not express the literal
correctly.

David J.

---

From: Gavan Schneider <[email-masked]>
Date: Sat, 16 Apr 2022 08:11:27 +1000

> LINE 1: ...ed) VALUES ( quote_literal('Poker 5 card Hold\\'Em'), 2, 
Unbalanced single quote, try dollar quotes as a general get out of jail 
approach…

	pendari=# SELECT $x$Poker 5 card Hold'Em$x$::TEXT;
  	        text
	----------------------
  	 Poker 5 card Hold'Em
	(1 row)

Regards

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a 
well-known solution to every human problem — neat, plausible, and 
wrong.
— H. L. Mencken, 1920