Inserting a SQL statement as a record value into a PostgreSQL column

Question

I work with an application that uses PostgreSQL 10.7. Part of the application allows you to bundle a group of of database objects and SQL statements into a package that you can later run when creating a Dev environment.

Each object and SQL statement has its own record in the database. I have to create over 1000 records so I am trying to create a script that will insert the SQL statements into the database for me.

I created my script but I am getting an error once Postgres sees the second "Value" command that is part of the record I am trying to insert.

Here is an example of what I am trying to do:

````insert into MY_TABLE 
   ( NAME, 
     SQL_STMT, 
     ADDED_BY, 
     DATE_ADDED  ) 
values 
   ( 'package_1', 
     'INSERT INTO TABLE_1(NAME, OBJECT_ID, ORDER_NUMBER) VALUES 
  'LCMSMS','PEST',1);'
     'CJONES', 
     '9/11/2019' );````

I am expecting it to be inserted but I am getting the following error. Can anyone guide me on how to "insert my insert statement"?

LINE 8: ...NAME,SQL_STMT,ADDED_BY,DATE_ADDED) VALUES ('LCMSMS...````

I planned on doing it manually. I'm not familiar the the client library but that's an option I can look into. Thank you. — Jonescj911
– Jonescj911, Commented Sep 12, 2019 at 0:05

GMB · Accepted Answer · 2019-09-11 23:33:33Z

2

Your SQL statement contains emmbedded quotes, that clash with the surrounding quotes. You would need to double these quotes, like:

````insert into MY_TABLE 
   ( NAME, 
     SQL_STMT, 
     ADDED_BY, 
     DATE_ADDED  ) 
values 
   ( 'package_1', 
     'INSERT INTO TABLE_1(NAME, OBJECT_ID, ORDER_NUMBER) VALUES (''LCMSMS'', ''PEST'', 1);'
     'CJONES', 
     '9/11/2019' );````

As commented by mu is too short, another solution would be to use Postgres dollar quoting syntax. This saves you the effort of double quoting each and every embedded quote:

````insert into MY_TABLE 
   ( NAME, 
     SQL_STMT, 
     ADDED_BY, 
     DATE_ADDED  ) 
values 
   ( 'package_1', 
     $$INSERT INTO TABLE_1(NAME, OBJECT_ID, ORDER_NUMBER) VALUES ('LCMSMS', 'PEST', 1);$$
     'CJONES', 
     '9/11/2019' );````

edited Sep 11, 2019 at 23:33

answered Sep 11, 2019 at 23:14

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mu is too short Over a year ago

Dollar quoting would be another option since we're talking PostgreSQL.

GMB Over a year ago

@muistooshort: I updated my answer with this Postgres specific solution. Thanks for the improvement suggestion!

Jonescj911 Over a year ago

I tried both solutions and they both worked great. Thank you both for the quick replies. Much appreciated!

Collectives™ on Stack Overflow

Inserting a SQL statement as a record value into a PostgreSQL column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related