Thanks for the suggestion, Used || , actually I was reluctant to use this because my columns could have contained null values. But that I have handled using COALESCE. -----Original Message----- From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] Sent: Thursday, October 13, 2016 7:37 PM To: Jaisingkar, Piyush Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Passing of where clause to remote table in FDW "Jaisingkar, Piyush" <Piyush.Jaisingkar@xxxxxxxxxxx> writes: > While using fdw I am trying to get and filtered data from a foreign table. Filtering using a regex in where clause as follows: > EXPLAIN ANALYZE VERBOSE SELECT CAF1.uprn > FROM XYZ as CAF1 > WHERE > > (regexp_replace(upper(concat(CAF1.SUB_BUILDING_NAME,CAF1.BUILDING_NAME > ,CAF1.BUILDING_NUMBER,CAF1.STREET_DESCRIPTION , CAF1.TOWN_NAME)), > '[^a-zA-Z0-9]','') = '84WELLINGTONROADEXETER'); concat() is not immutable (it's only stable) so it can't be sent for remote execution. Use the || operator instead. regards, tom lane ______________________________________________________________________ Disclaimer: This email and any attachments are sent in strictest confidence for the sole use of the addressee and may contain legally privileged, confidential, and proprietary data. If you are not the intended recipient, please advise the sender by replying promptly to this email and then delete and destroy this email and any attachments without any further use, copying or forwarding. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
