CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Ram
Ram

Reputation: 632

Type mismatch in postgres INTERSECT

I am trying to do an intersect of a select statement which retrieves a text[] and do an intersect of that with a text[] that I compute dynamically.

When I run this script, I get the error

ERROR: INTERSECT types text[] and text cannot be matched

How do I fix this error?

do $$
declare
  p json;
  total_time float;
  t float;
  arr text[];
  query1 text;
    arr := '{}';
    for j in 80..120 loop
      arr := array_append(arr, j::text);
      query1 := 'select sub_id from table where main_id=1 INTERSECT select unnest($1)';
      execute 'explain (analyse, format json) ' || query1 using arr into p;
      t := (p->0->>'Planning Time')::float + (p->0->>'Execution Time')::float;
      total_time := total_time + t;
    end loop;

Schema of my table:

db=# \d+ table
                          Table "table"
    Column     |  Type   | Modifiers | Storage  | Stats target | Description 
---------------+---------+-----------+----------+--------------+-------------
 main_id       | integer |           | plain    |              | 
 sub_id        | text[]  |           | extended |              |

Upvotes: 0

Views: 218

Answers (1)

404
404

Reputation: 8572

sub_id is an array. arr is an array, but you're unnesting it - so now it's sequence of (text) values. You're attempting to do an INTERSECT of an array and text values. To fix it, either unnest both, or unnest neither. (But choose the right one, as the result of each is different - you're either intersecting on the arrays themselves or on the values contained in the arrays.)

SELECT UNNEST(sub_id) FROM table WHERE main_id = 1
INTERSECT
SELECT UNNEST(arr)

Upvotes: 2

Related Questions