postgres function error on Insert operation for object identifier types : SQL Error [42725]: ERROR: more than one operator named +

Question

Postgresql Insert statement on Objectidentifier datatype 'regoper' returns error 'SQL Error [42725]: ERROR: more than one operator named +' even though a single operator value is given

Hi, I have a created a table for all Object Identifier data types of Postgresql. The DDL statement for the same is as follows :

CREATE TABLE public.objectidentifiers (
    pk int4 NOT NULL,
    col_oid oid NULL,
    col_regclass regclass NULL,
    col_regcollation regcollation NULL,
    col_regconfig regconfig NULL,
    col_regdictionary regdictionary NULL,
    col_regnamespace regnamespace NULL,
    col_regoper regoper NULL,
    col_regoperator regoperator NULL,
    col_regproc regproc NULL,
    col_regprocedure regprocedure NULL,
    col_regrole regrole NULL,
    col_regtype regtype NULL,
    CONSTRAINT objectidentifiers PRIMARY KEY (pk)
);

where public is the schema name and objectidentifiers is the table.

I am trying to execute this Insert Operation :

INSERT INTO public.objectidentifiers VALUES
(1,
564182,
'pg_type',
'"POSIX"',
'english',
'simple',
'pg_catalog',
'+',
'*(integer,integer)',
'"SRC"."area_equal_procedure"',
'sum(int4)',
'postgres',
'integer'
);

But this is giving the below error :

Error occurred during SQL query execution

Reason:
SQL Error [42725]: ERROR: more than one operator named +

Position: 114"

Can someone tell what is wrong in the Insert statement, even though I am using the right value for the data type

LEMON · Accepted Answer · 2023-02-27 07:43:28Z

0

the issue is probably because regoper in postgres expects an operator object identifier instead of the operator itself so I think '+' is triggering the issue. Instead you can use pg_operator system catalog table to find the operator id for +.

INSERT INTO public.objectidentifiers VALUES
(1,
564182,
'pg_type',
'"POSIX"',
'english',
'simple',
'pg_catalog',
(SELECT oid FROM pg_operator WHERE oprname = '+'),
'*(integer,integer)',
'"SRC"."area_equal_procedure"',
'sum(int4)',
'postgres',
'integer'
);

answered Feb 27, 2023 at 7:43

LEMON

8804 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jarlh Over a year ago

I get ERROR: more than one row returned by a subquery used as an expression here.

LEMON Over a year ago

Try to specify the left operand type and right operand type of the operator in the subquery to narrow down the result to a single operator OID.

(SELECT oid FROM pg_operator WHERE oprname = '+' AND oprleft = 'integer'::regtype AND oprright = 'integer'::regtype LIMIT 1)

oprleft and oprright columns are used to specify that we are using integer data type. The LIMIT 1 clause ensures that only one operator OID is returned by the subquery. Alternatively, instead of querying the pg_operator table, I think OID of the + operator is 426 you can use it directly.

Collectives™ on Stack Overflow

postgres function error on Insert operation for object identifier types : SQL Error [42725]: ERROR: more than one operator named +

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related