Re: operator is only a shell - Error
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? 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
ldap fdw
Hello, I need to find a fdw to access, and modify (insert and update, and delete) ldap data. (postgres v15 on x86 redhat 8.5) the main fdw page, in postgres doc shows 2. 1 via github seems to be old (postgres 9.2) and not maintained. 2 the second is a multicorn thing. Or try to build something from scratch ?!? what s your advice ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com
Re: operator is only a shell - Error
The order of the arguments matter. You need an operator that expects a varchar on the left hand side of the operator, and numeric on the right side. For example: create database cast_test; \c cast_test You are now connected to database "cast_test" as user "greg". cast_test=# create table deposit_lien(deposit_no varchar); CREATE TABLE cast_test=# select * from deposit_lien where deposit_no='0002114029832'::numeric; ERROR: operator does not exist: character varying = numeric cast_test=# create function varchar_eq_numeric(varchar,numeric) cast_test-# returns bool language sql immutable as $$ select $1::numeric=$2::numeric $$; CREATE FUNCTION cast_test=# create operator public.= (function = varchar_eq_numeric,leftarg = varchar,rightarg = numeric); CREATE OPERATOR cast_test=# select * from deposit_lien where deposit_no='0002114029832'::numeric; deposit_no (0 rows) But before creating the function and operator it was working fine Was it? It helps to show us the exact things ran and the exact output, rather than just "it was working fine" :) Cheers, Greg
Re: operator is only a shell - Error
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.kla...@aklaver.com
Single-User Mode oid assignment
I have following script, which initialize two clusters: echo CREATE ROLE new_user WITH LOGIN;>init_cluster.sql echo CREATE DATABASE new_database WITH OWNER = new_user;>>init_cluster.sql initdb -D case1 -U postgres pg_ctl start -D case1 -w psql "host=localhost dbname=postgres user=postgres"OIDs assigned during normal database operation are constrained to be 16384 or >higher. This ensures that the range 1—16383 is free for OIDs assigned >automatically by genbki.pl or during initdb. Is such difference in oid assignment in Single-User Mode expected?
Re: Single-User Mode oid assignment
On Sat, 2024-03-16 at 14:14 +0300, PetSerAl wrote: > I have following script, which initialize two clusters: > > echo CREATE ROLE new_user WITH LOGIN;>init_cluster.sql > echo CREATE DATABASE new_database WITH OWNER = new_user;>>init_cluster.sql > initdb -D case1 -U postgres > pg_ctl start -D case1 -w > psql "host=localhost dbname=postgres user=postgres" pg_ctl stop -D case1 -m smart -w > initdb -D case2 -U postgres > postgres --single -D case2 postgres > In case1 I have following oid assigned: > new_user: 16384 > new_database: 16385 > > In case2: > new_user: 15062 > new_database: 15063 > And pgAdmin does not show new_database in the server tree. > Documentation says following: > > OIDs assigned during normal database operation are constrained to be 16384 > > or higher. This ensures that the range 1—16383 is free for OIDs > > assigned automatically by genbki.pl or during initdb. > > Is such difference in oid assignment in Single-User Mode expected? Yes; see the comment in GetNewObjectId(): /* * Check for wraparound of the OID counter. We *must* not return 0 * (InvalidOid), and in normal operation we mustn't return anything below * FirstNormalObjectId since that range is reserved for initdb (see * IsCatalogRelationOid()). Note we are relying on unsigned comparison. * * During initdb, we start the OID generator at FirstGenbkiObjectId, so we * only wrap if before that point when in bootstrap or standalone mode. * The first time through this routine after normal postmaster start, the * counter will be forced up to FirstNormalObjectId. This mechanism * leaves the OIDs between FirstGenbkiObjectId and FirstNormalObjectId * available for automatic assignment during initdb, while ensuring they * will never conflict with user-assigned OIDs. */16384 Object IDs are forced to be 16384 or above "after normal postmaster start". Yours, Laurenz Albe
