pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Rajesh S <rajesh.s@fincuro.com>
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: operator is only a shell - Error
Date: Mon, 18 Mar 2024 08:50:11 -0700
Message-ID: <c8dcf741-2c11-4f61-a246-9452007541f5@aklaver.com> (raw)
In-Reply-To: <5884c2c6-d46b-4ead-b089-2572848b02a1@fincuro.com>
References: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com>
	<f0d571bc-4631-4214-b1be-73d15cbb4310@fincuro.com>
	<CAKAnmmKe-0KV4vkEjaGvG5KYW+hsJboHt0OmkhZq7FjWhKtyWg@mail.gmail.com>
	<5884c2c6-d46b-4ead-b089-2572848b02a1@fincuro.com>

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 <rajesh.s@fincuro.com> 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






view thread (7+ messages)  latest in thread

Message-ID: <c8dcf741-2c11-4f61-a246-9452007541f5@aklaver.com>
Permalink:  ../c8dcf741-2c11-4f61-a246-9452007541f5@aklaver.com/
Also on:    postgresql.org/message-id/c8dcf741-2c11-4f61-a246-9452007541f5@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, rajesh.s@fincuro.com, htamfids@gmail.com
  Subject: Re: operator is only a shell - Error
  In-Reply-To: <c8dcf741-2c11-4f61-a246-9452007541f5@aklaver.com>
  References: <7f97243d-db62-199a-8412-5637963d117e@fincuro.com> <f0d571bc-4631-4214-b1be-73d15cbb4310@fincuro.com> <CAKAnmmKe-0KV4vkEjaGvG5KYW+hsJboHt0OmkhZq7FjWhKtyWg@mail.gmail.com> <5884c2c6-d46b-4ead-b089-2572848b02a1@fincuro.com> <c8dcf741-2c11-4f61-a246-9452007541f5@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox