Postgres SQL Single quote Escape

Question

I need a help in escaping single quotes in postgresql insert. I am using a ETL tool for data extraction from source and loading in a postgresql database.The Select statement includes some variables.

For example My select query is

SELECT ${UNIVERSITY},date from education.

The variable ${UNIVERSITY} has the value Duke's university. This statement gives me an error

ERROR: syntax error at or near "s"

and not getting loaded into the postgres table. Could anyone help me in esacping the single quote and how should I use in variables?

Answer 1

Since you have mentioned it does not refer a column in the table education. You can achieve the same expected output through this transformation.

Here in
- Table input step you can write the query SELECT date from education
- In split fields step you can add a new column to your result as University
- From Get Variables step you can assign the parameter values to the new column University
-Obtain the result in Select Values step

Answer 2

@Marlon Abeykoon's answer is very good. You can also do it this way.

Put this in a job and set a parameter or variable there called UNIVERSITY and give it the "Duke's University" value. Launch the above transform from there. The pick a join condition that always results in true. I used 'yr_key IS NOT NULL'. The following is the result:

Then you can just run the results into a Table Output step to load into PostgreSQL.

Answer 3

You can do SELECT REPLACE(${UNIVERSITY},'''', ''''''),date from education. But probably you just need to do SELECT '${UNIVERSITY}',date from education as your query looks like:

SELECT Duke's university,date from education

That is definitely wrong by SQL syntax.