Postgresql: [42883] ERROR: operator does not exist: text || integer[]

Question

I getting error like below: [42883] ERROR: operator does not exist: text || integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. I cannot fix this problem and tried so many times.

My Code:

DECLARE

arr_operators       integer[1,2];

BEGIN
query1 := 'SELECT * FROM dist.' || _rec1.table_name || ' WHERE operator_id = ANY (''' || arr_operators || ''');';

FOR _rec IN EXECUTE query1 LOOP

END LOOP;

I think problem is happening when I am makin query string.But when I use this statement in query directly lik below is working well:

FOR _rec1 IN (SELECT * FROM dist.sirdarya WHERE id = any (arr_operators)) LOOP

         INSERT INTO dist.justt(column1,column2) VALUES (_rec1.id,_rec1.msisdn);

END LOOP;

Any help is appreciated.

Cast it to text: query1 := 'SELECT * FROM dist.' || _rec1.table_name || ' WHERE operator_id = ANY (''' || arr_operators::TEXT || ''');'; — 404
– 404, Commented Dec 22, 2019 at 9:54
but operator_id and arr_operators are integer, does it work? — Abdusoli
– Abdusoli, Commented Dec 22, 2019 at 10:18
Yes it works fine, the cast is only for creating the query string, you'll end up with ... = ANY('{1,2,3}') which should be implicitly cast to an integer array when the query is executed. — 404
– 404, Commented Dec 22, 2019 at 10:36

Lukasz Szozda · Accepted Answer · 2019-12-22 10:03:55Z

2

I suggest unnesting an array instead of concatenating string which could lead to SQL Injection:

SELECT * 
FROM table_name
WHERE operator_id IN (SELECT * FROM unnest(arr_operators));

db<>fiddle demo

This part is particulary dangerous:

query1 := 'SELECT * FROM dist.' || _rec1.table_name

What if table name is let's say: ;DROP DATABASE ...;--?

It could be rewritten as:

query1 := FORMAT('SELECT * FROM dist.%I ...', _rec1.table_name);

edited Dec 22, 2019 at 10:03

answered Dec 22, 2019 at 9:57

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

Abdusoli Over a year ago

but table_name should be changed

Lukasz Szozda Over a year ago

@AbdusoliErgashev FORMAT(' %I') is doing it

Collectives™ on Stack Overflow

Postgresql: [42883] ERROR: operator does not exist: text || integer[]

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