0

I have the following string:

SELECT hello as "helloHello", '' as "empty" FROM tbl_test

I want to do something like this:

INSERT INTO tbl_x (a, b, c) VALUES (x, y, string_from_above)

The strings are going to be dynamic (basically they're strings of sql statements), so I do not want to escape all the ' and ". If I open the postgres database and double click on the varchar column, I can copy and paste the string and it goes in exactly as it looks. I want to be able to programmatically do so. Is there a way using an insert command? I tried using pgFormat, but that didn't work. See attached pic with what it looks like in the table.

enter image description here

Joel Peltonen
  • 13,025
  • 6
  • 64
  • 100
Just Me
  • 91
  • 4
  • 12
  • Does this answer to your question https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server – sairfan Dec 16 '21 at 18:40
  • No. I just added an image of what the value looks like in the postgres table. I'm able to add it if I double click and paste, but not via an sql statement. – Just Me Dec 16 '21 at 18:45
  • for that you also use escape char like `\"` and `\'` like `var str = "friend\'s \"cat\"";` – sairfan Dec 16 '21 at 18:53
  • Not quite sure what you are after. Does this work: `INSERT INTO tbl_x (a, b, c) VALUES (x, y, quote_literal(string_from_above));` – Adrian Klaver Dec 16 '21 at 19:18
  • Can't see the image... can you double check? – Hambone Dec 16 '21 at 20:43

1 Answers1

0

All PostgreSQL APIs worth their money have a way to escape strings for inclusion in a dynamic SQL statement.

For example, if you write in C, you can use libpq's PQescapeLiteral().

There are also SQL functions for this: quote_literal() and format(). They can be used in PL/pgSQL code.

Don't try to write your own code for this. Use prior art and avoid the risk of getting it wrong.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263