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

Use where condition with array in postgresql doesn't work

I’m trying to use an array that was declared earlier in a postgres script, but I keep getting the following error :

op ANY/ALL (array) requires array on right side

This is my code :

DO $$ 
DECLARE
   id_cie VARCHAR(50) := ARRAY(select id from main_cies);
BEGIN
   RAISE NOTICE '%%%', id_cie;
   drop table if exists network;
   create table network as
   select
       req_real_estate_qc_shareholders.id as id,
       req_real_estate_qc_shareholders.name as name,
       'Main owners' as relation,
       req_real_estate_qc_shareholders.address_num as num,
       req_real_estate_qc_shareholders.street_name as street,
       req_real_estate_qc_shareholders.suite as suite,
       req_real_estate_qc_shareholders.zip as zip
   from req_real_estate_qc_shareholders 
   where req_real_estate_qc_shareholders.id = ANY(id_cie);
END $$;

I don’t understand the error since when I print the value I get the following array :

{1173569659,1173569659}
Tags:

2

Answers


  1. 0

    This:
    id_cie VARCHAR(50) := ARRAY(select id from main_cies);
    is converting the ARRAY into a string('{1173569659,1173569659}').

    As in:

    select pg_typeof(array[1,2]::varchar);
     pg_typeof     
-------------------
 character varying

    You are looking for VARCHAR[]:

    select pg_typeof(array[1,2]::varchar[]);
      pg_typeof      
---------------------
 character varying[]

    So:

    id_cie VARCHAR[] := ARRAY(select id from main_cies);

    Though if the ids are actually integers I would choose:

    id_cie INTEGER[] := ARRAY(select id from main_cies);

    Login or Signup to reply.
  2. 0

    You could write the same functionality without the usage of a variable. This avoids the issue that the declaration of the variable is wrong: it’s not an array.

    This could work:

    DO
$$
    BEGIN
        DROP TABLE IF EXISTS network;
        CREATE TABLE network AS
        SELECT req_real_estate_qc_shareholders.id          AS id
             , req_real_estate_qc_shareholders.name        AS name
             , 'Main owners'                               AS relation
             , req_real_estate_qc_shareholders.address_num AS num
             , req_real_estate_qc_shareholders.street_name AS street
             , req_real_estate_qc_shareholders.suite       AS suite
             , req_real_estate_qc_shareholders.zip         AS zip
        FROM req_real_estate_qc_shareholders
                 JOIN main_cies ON req_real_estate_qc_shareholders.id = main_cies.id;
    END
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search