Need a query that works in both cases when column type is either text or text[]-CodePudding

Following are my 2 tables

CREATE TABLE temptext (
  id text 
);

CREATE TABLE temptext2 (
  id _text
);

My insert queries are

INSERT INTO temptext
  (id)VALUES
  ('a');
  
  INSERT INTO temptext2
  (id)VALUES
  ('{c,d,e,a}');

I have 2 queries that run perfectly

SELECT *
FROM temptext2
WHERE (  ('edfdas' = ANY ("id")))

and

SELECT * 
FROM temptext
WHERE (  ("id" = ANY ('{"edfdas"}')))

If I replace temptext2 with temptext in either of 1 then it fails giving error -

ERROR: could not find array type for data type text[] or ANY/ALL (array) requires array on right side

I need a query that runs in both cases since I don't know the data type in the database whether it is text or _text. PS: There can be multiple values along with 'edfdas'

CodePudding user response：

One way to get around this is, is to make sure you always compare the value with an array by turning the text column into a text[]. This can be achieved by appending the column to an empty array. This works for text and text[] alike

SELECT *
FROM temptext
WHERE 'one' = any(array[]::text[]||id)

The query works for both tables. Concatenating a single value to an empty array yields an array with a single value. Concatenating and array to an empty array yields the original array.

However this will prevent the usage of any index. If you want an efficient solution (that can make use of possible indexes), you will have to use two different queries.

I find the fact that your application doesn't know how the tables are defined quite strange to be honest. Fixing that seems to be more efficient and less error prone in the long run.

CodePudding user response：

ANY operator can only be used with an array on the right side, but you are trying to use it with a text value.

Try one of the following -

Use the IN operator instead of ANY, and enclose the value in parentheses to create a set. For example:

SELECT * FROM temptext WHERE 'edfdas' IN ("id")

SELECT * FROM temptext2 WHERE 'edfdas' IN ("id")

This will work for both tables, because the IN operator can be used with a set on the right side, and the set will be implicitly converted to an array if the column type is an array type.

You could also use the = operator and wrap the value in quotes to create a text value, like this:

SELECT * FROM temptext WHERE "id" = 'edfdas'

SELECT * FROM temptext2 WHERE "id" = 'edfdas'

This will also work for both tables, because the = operator can be used with a text value on the right side, and the text value will be implicitly converted to an array if the column type is an array type.