Insert ARRAY in PostgreSQL table with apostrophes in values

Question

I have a Python script with SQL insert statements that insert parsed data from file to PostgreSQL table. In case data has apostrophes, execution fails. In detail:

    <name>RYAZAN'</name>
or
    <name>CHYUL'BYU</name>

I found the solution when it is a string -> adding extra ' to apostrophe so it transforms to 'RYAZAN''' or 'CHYUL''BYU' in INSERT statement.

But in case my values are in list (from python script) like city = ["RYAZAN''", "CHYUL''BYU"] Python automatically puts double quotes instead of single quotes. As a result when trying to insert

INSERT INTO City (uuid, name) VALUES (uuid_generate_v4(), unnest(array{city}))

SQL fails with error

ERROR: column "RYAZAN''.." does not exist

because sql reads the double quotes as a column name or whatever. Is there a way to insert ARRAY with values that contain apostrophes?

Can you share the poython code, the idea is to use `Parameterized Query` so the special char will be handled by the driver, ie: https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/ — Phung Duy Phong, Jan 10 '20 at 09:14
python doesnt distinguish between double quotes or single quotes they are both just a string. as @PhungDuyPhong says, when ever you dont ant to have to worry about any special content in your values then used parameterised / placeholder inserts — Chris Doyle, Jan 10 '20 at 09:23

Jim Jones · Answer 1 · 2022-06-12T10:28:08.727

Try using $$ dollar-quoted strings

CREATE TEMP TABLE t (f TEXT);
INSERT INTO t VALUES ($$<name>CHYUL'BYU</name>$$),
                     ($$<name>RYAZAN'</name>$$);
SELECT * FROM t;

           f            
------------------------
 <name>CHYUL'BYU</name>
 <name>RYAZAN'</name>
(2 Zeilen)

There are also many other ways to escape quotes

INSERT INTO t 
VALUES (E'\'foo'),('''bar'''),
       ('"the answer is" -> ''42'''),($$It's "only"" $1.99$$);
SELECT * FROM t;

            f            
-------------------------
 'foo
 'bar'
 "the answer is" -> '42'
 It's "only"" $1.99
(4 Zeilen)

Demo: db<>fiddle

Insert ARRAY in PostgreSQL table with apostrophes in values

1 Answers1

Linked