Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmFFn-006qU1-1H for pgsql-general@arkaria.postgresql.org; Mon, 18 Mar 2024 15:50:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rmFFk-003Vcx-Jg for pgsql-general@arkaria.postgresql.org; Mon, 18 Mar 2024 15:50:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmFFi-003VcU-U4 for pgsql-general@lists.postgresql.org; Mon, 18 Mar 2024 15:50:20 +0000 Received: from fout5-smtp.messagingengine.com ([103.168.172.148]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmFFf-005Coa-CY for pgsql-general@lists.postgresql.org; Mon, 18 Mar 2024 15:50:18 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailfout.nyi.internal (Postfix) with ESMTP id 330FC13800EC; Mon, 18 Mar 2024 11:50:13 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Mon, 18 Mar 2024 11:50:13 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm1; t=1710777013; x=1710863413; bh=bVvvzrIw7dI5LBn7Tm1QSRipZG6W0uCBVSCbSe/IXTM=; b= OxhYLKbdT1amVVQExs0mknKkDa1mTe3Z1RKBkswkS/BN1x1x8o/IX+hHzn48qsbD wyCB8OYdjymk/0n+Cz8EHvpnRM0ABO1u3YcwYEE/dZUsMJfydDiOBVxw32SGYM+N iC2N3pcd2xJ7f0rH/CzHn8hkRk7i7ho38SxS8cY9dlSIUIQDd32/oSh+GRb453EY HPcBwUZ58jofPXJTi1ZnQLfODcvIybCMkkfT7jnI1oEP/x/eyzJDrrNVa+eLE6nz sPcuIBy/IwR+ekNpW/66h8DIRw5YdAii9CWVlktsJujKhjMINDgHCF8zUot4X2Y9 t+p+vB7VJinWvKXbs+zpiw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1710777013; x= 1710863413; bh=bVvvzrIw7dI5LBn7Tm1QSRipZG6W0uCBVSCbSe/IXTM=; b=L TZDwQgilPPEZV8+qphMKDP924ThSevV4r5RFpYhPu6Pzo7YOEXXj6Iw4lRJN5VqA TP0splUxELnUhMV0Jj4i9YQ/2fUpyQrN8yjpaOYxVr1R6GWAcptF2WVoyUsmP59B RfvFBwYE/dJY3AUNMkDkbkMXTLavpp2cMwiWvjW81UXCQRCzFSnpgeDwkJ7FQKPQ B+whA2ROrS3T2fq2QVRhdEvNXLFcWiaMqFicoxKaPs38TiHpShNtZQVy/GzqD8wX wCgS65RhEFdIM67lhOlR6apShAgFKpYTcUC7Jgel2aUDS4FKIaa08EsukDoc59LY cNrdwBWSNYGq1pZvVprEA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrkeejgdekudcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeefgeefieeutdfggfetgefgheekjeehteeileeigfet ieekjedvieeviefgheevtdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 18 Mar 2024 11:50:12 -0400 (EDT) Message-ID: Date: Mon, 18 Mar 2024 08:50:11 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: operator is only a shell - Error Content-Language: en-US To: Rajesh S , Greg Sabino Mullane Cc: pgsql-general@lists.postgresql.org References: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com> <5884c2c6-d46b-4ead-b089-2572848b02a1@fincuro.com> From: Adrian Klaver In-Reply-To: <5884c2c6-d46b-4ead-b089-2572848b02a1@fincuro.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/18/24 00:05, Rajesh S wrote: > Thank you for your response.  Actually, I was trying to address the > following query. > > select LIEN_AC_NO from deposit_lien where deposit_no='0002114029832' and > deposit_sub_no='1' and unlien_dt is null and unlien_remarks is null; > > In the above query "deposit_sub_no" column is "numeric" type and passing > '1' (as varchar).  To address this I'd created the function and operator > as I'd mentioned in the earlier mail.  Even the following query throws > error after creating the function and operator. > > select * from deposit_lien where deposit_no='0002114029832'; > > ERROR: operator is only a shell: character varying = numeric LINE 1: > select * from deposit_lien where deposit_no='0002114029832' ^ SQL state: > 42883 Character: 44 > > In the above query "deposit_no" column is having "varchar" data type. > But before creating the function and operator it was working fine. > Tried dropping the same, even though the same error.  How to proceed now? Not clear to me what the problem is you are trying to solve? On a stock Postgres install: select 1::numeric = '1'; ?column? ---------- t select '0002114029832'::varchar = '0002114029832'; ?column? ---------- t > > > Thanks, > > Rajesh S > > On 15-03-2024 19:10, Greg Sabino Mullane wrote: >> On Fri, Mar 15, 2024 at 6:26 AM Rajesh S wrote: >> >> I wanted to implement a new "=" (equal) operator with LEFTARG as >> numeric and RIGHTARG as varchar.  But after creating the function >> and operator, psql shows the error "operator is only a shell: >> character varying = numeric >> >> Your operator has numeric on the left and varchar on the right. But >> your query is doing numeric on the RIGHT. Probably want to make a >> matching one to cover both cases. >> >> Cheers, >> Greg >> -- Adrian Klaver adrian.klaver@aklaver.com