Thanks a lot John, the above query did help me. Can you provide me where I can get this query format or what language is this? So I can come up for my other scenarios that I'm trying to test. Thanks for the help again!
Glad to hear you are interesting how Replicate works 🙂 Well, it's not a special language but only a few 'tricks':
1- The whole passthrough filter expression in the GUI will be executed in source side database so its syntax relies on source database; that means you may use 3rd party JDBC/ODBC query tools to test your SQL, eg SQL Workbench/J, RazorSQL, DBeaver etc, it's much easier to debug SQL than using Replicate task running.
2- The whole passthrough filter expression (eg "xxxx" ) will be sent to source database combined with "WHERE ( xxxx )" sub-clause. No way to repress the WHERE sub-clause as it's hard coded, however we may use single right parenthesis ")" to end up the WHERE sub-clause prior to embedded ")".
3- "LIMIT nnnn" cannot be included in WHERE clause, we have to move it out of "WHERE ( xxxx )" string
4- To make sure no empty parenthesis "()" generated (it may lead syntax error), we add "1=1" (it's TRUE forever). Certainly you may add more clauses eg "1=1 and ID >= 20230101" if necessary (ID is your table column) then you may keep or remove "1=1".
5- Now we end up the expression by semicolon, it looks like "1=1) LIMIT 1000 ;". The last step is removing the latest useless embedded ")": We add comment string "--" before it, PostgreSQL will ignore the comment string "--)". So the whole expression in PostgreSQL is:
1=1) LIMIT 1000 ;--
With above expression, Replicate will execute the query as:
2023-02-10T17:00:46 [SOURCE_UNLOAD ]T: SELECT ... FROM ... WHERE (1=1) LIMIT 1000 ;--), postgres_construct_select_statement
Hope it helps,
Regards,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
