Question 247052 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – operator does not exist: character varying[] = text

I have a postgreSQL function which takes a text array as one of the input parameters:


CREATE OR REPLACE FUNCTION report.fn_get_metrics(
    from_date timestamp without time zone,
    to_date timestamp without time zone,
    location_ids text[])
    RETURNS TABLE(demand_new_count bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN
    RETURN QUERY
    WITH 
    cte_pos AS (
    SELECT
        COUNT(DISTINCT job_id) AS demand_new_count,
    FROM report.pg_job_data pjd
    WHERE creation_date >= from_date AND creation_date <= to_date
    AND (location_ids IS NULL OR location_id IN (SELECT unnest(location_ids)))
)
    SELECT
        demand_new_count
    FROM cte_pos;
END;
$BODY$;

I am trying to query the function by passing an array of values to this parameter:

SELECT * FROM report.fn_get_metrics(
    '2023-01-01'::timestamp, -- from_date
    '2023-06-30'::timestamp, -- to_date
    ARRAY['638a4f2c-11c4-4e15-ae78-d6bf01ef2fad']
);

I tried to typecast the array of values to uuid as below:

SELECT * FROM report.fn_get_metrics(
    '2023-01-01'::timestamp, -- from_date
    '2023-06-30'::timestamp, -- to_date
    ARRAY['638a4f2c-11c4-4e15-ae78-d6bf01ef2fad']::UUID[]
);

But it doesn’t seem to work. We cannot have a varchar[] or uuid[] type for the input parameter in the function. Any guidance on how to resolve this would be much appreciated

Tags:

3

Answers


  1. 0

    You should to use operator = ANY instead IN, like

    bad:

    -- should not to work
SELECT * FROM foo WHERE col IN (ARRAY[1,2,3]);

    correct:

    SELECT * FROM foo WHERE col = ANY(ARRAY[1,2,3]);
    Login or Signup to reply.
  2. 0

    You should change the data type of your location_ids parameter in your function so it can accept UUID arrays instead of text array. Modify your function parameter this way:

    
CREATE OR REPLACE FUNCTION report.fn_get_metrics(
    from_date timestamp without time zone,
    to_date timestamp without time zone,
    location_ids uuid[])

    And then you’ll be able to call it this way:

    SELECT * FROM report.fn_get_metrics(
    '2023-01-01'::timestamp, -- from_date
    '2023-06-30'::timestamp, -- to_date
    ARRAY['638a4f2c-11c4-4e15-ae78-d6bf01ef2fad']::UUID[]
);
    Login or Signup to reply.
  3. 0

    A conditional answer.

    This operator does not exist: character varying[] = text is correct there is no operator that does a = comparison between an array and a text value.

    The only place I can conceivably see that happening is:

    location_id IN (SELECT unnest(location_ids))

    What is the data type for location_id?

    I’m betting character varying[] as in:

    select 
    array['one', 'two']::varchar[] in (unnest(array['one', 'two'])); 

ERROR:  operator does not exist: character varying[] = text

    A more complete answer awaits more information from you as to the type for location_id and whether you want to do an array to array comparison or a varchar to array comparison.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search