2

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?

serenesat
  • 4,611
  • 10
  • 37
  • 53
Karthi
  • 708
  • 1
  • 19
  • 38
  • Does [this post](http://stackoverflow.com/questions/935/string-literals-and-escape-characters-in-postgresql) helps ? – merours Mar 24 '15 at 11:52
  • You can escape single quotes in Postgres by using `''`. See [this SO article](http://stackoverflow.com/questions/12316953/insert-varchar-with-single-quotes-in-postgresql) for more information. – Tim Biegeleisen Mar 24 '15 at 11:53
  • Are you executing the query from Pentaho Spoon or Pentaho report designer? – Marlon Abeykoon Mar 24 '15 at 11:58
  • I am executing the query from Pentaho spoon. – Karthi Mar 24 '15 at 11:59
  • does the ${UNIVERSITY} really refers to a column in education table or is is just a value you want to add to the result? if its not a table column I can give you a solution from spoon – Marlon Abeykoon Mar 24 '15 at 12:10
  • Just a value . I am getting this values from GET and SET variables – Karthi Mar 24 '15 at 12:13

3 Answers3

3

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

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

Marlon Abeykoon
  • 11,927
  • 4
  • 54
  • 75
3

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

enter image description here

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:

enter image description here

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

Brian.D.Myers
  • 2,448
  • 2
  • 20
  • 17
2

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.

Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
  • Could you please explain on using multiple quotes here. why do we use '''' (4 quotes) here. Thanks – Karthi Mar 24 '15 at 12:03
  • the syntax of REPLACE is REPLACE(whatTochange,stringToFind,StringToChange); in most cases it looks like REPLACE('someetext','ee', 'e'); So, here you have to single quotes already. Now you need to find single quote '. Escaped it looks like ''. If you put that into single quotes, then you have '''' already. The next step is to change it to double quote. Double escaped quote is '''' and if you put that to the single quotes, then you have 6 single quotes already :) But as I said, that check the 2nd option. – Dmitrij Kultasev Mar 24 '15 at 12:06
  • you can use advanced quoting that postgres offers: select '''' is the same as select $'$ and select e'\''... this is much more clear than oracle '''' way... – Vao Tsun Mar 24 '15 at 12:20
  • @VaoTsun it is not Oracle way. It is standard ANSI way. – Dmitrij Kultasev Mar 24 '15 at 12:31
  • @sidux Cool! my accent was - Postgres lets you to use other quoting, which a lot of people find easier to read :) – Vao Tsun Mar 24 '15 at 12:41