Declare an array in PostgreSQL

Question

I'm trying to do 3 actions (2 delete and 1 update) on an array of ent_id. This array is build in my script in the select.

CREATE OR REPLACE FUNCTION run_script()
  RETURNS VOID
AS $$
DECLARE
  all_ent bigint[];
BEGIN
  SELECT ent_id INTO all_ent FROM entretiens ent WHERE ent.ent_statut = 'T';

  RAISE INFO 'Start';

  DELETE FROM documents WHERE ent_id IN (all_ent);

  DELETE FROM comite WHERE ent_id IN (all_ent);

  DELETE entretiens ent SET ent_statut = 'N' WHERE ent_id IN (all_ent);

  RAISE INFO 'End';
END;
$$ LANGUAGE plpgsql;

SELECT run_script();

When I run the script I have this error:

ERROR: malformed array literal: "535030"
Détail : Array value must start with "{" or dimension information.

Any suggestion?

You looking for array_agg

S.Visser
– S.Visser

2022-03-09 09:27:02 +00:00
Commented Mar 9, 2022 at 9:27 — S.Visser
– S.Visser, Commented Mar 9, 2022 at 9:27

Ramin Faracov · Accepted Answer · 2022-03-12 07:05:43Z

1

Set to array selected field values:

select ARRAY(
    select ent_id
    from entretiens ent
    where ent.ent_statut = 'T'
) into all_ent;

Use array on queries:

delete
from documents
where ent_id in (select aaa from unnest(all_ent) tb(aaa));

answered Mar 12, 2022 at 7:05

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

Comments

blackgreen · Accepted Answer · 2024-02-19 11:27:25Z

*Memos:

An array has values from [1] but not from [0] so [0] returns NULL.
Basically, you should use type conversion to create an array except when you declare a non-empty array in a DECLARE clause in a function, procedure or DO statement because the type may be different from your expectation and there is some case which you cannot create an array without type conversion.
The doc explains arrays in detail.

You can create and use an array with these ways below:

SELECT ARRAY['a','b','c','d','e']::VARCHAR[]; -- {a,b,c,d,e}
SELECT (ARRAY['a','b','c','d','e']::VARCHAR[])[0]; -- NULL
SELECT (ARRAY['a','b','c','d','e']::VARCHAR[])[2]; -- b
SELECT (ARRAY['a','b','c','d','e']::VARCHAR[])[2:4]; -- {b,c,d}
SELECT (ARRAY['a','b','c','d','e']::VARCHAR[])[:4]; -- {a,b,c,d}
SELECT (ARRAY['a','b','c','d','e']::VARCHAR[])[2:]; -- {b,c,d,e}
SELECT (ARRAY['a','b','c','d','e']::VARCHAR[])[:]; -- {a,b,c,d,e}

*Memos:

The type of the array above is VARCHAR[](CHARACTER VARYING[]).
If you omit ::VARCHAR[], the type of the array above is TEXT[].

Or:

SELECT '{a,b,c,d,e}'::VARCHAR[]; -- {a,b,c,d,e}
SELECT ('{a,b,c,d,e}'::VARCHAR[])[0]; -- NULL
SELECT ('{a,b,c,d,e}'::VARCHAR[])[2]; -- b
SELECT ('{a,b,c,d,e}'::VARCHAR[])[2:4]; -- {b,c,d}
SELECT ('{a,b,c,d,e}'::VARCHAR[])[:4]; -- {a,b,c,d}
SELECT ('{a,b,c,d,e}'::VARCHAR[])[2:]; -- {b,c,d,e}
SELECT ('{a,b,c,d,e}'::VARCHAR[])[:]; -- {a,b,c,d,e}

*Memos:

The type of the array above is VARCHAR[](CHARACTER VARYING[]).
If you omit ::VARCHAR[], the value above is not an array and the type of the value above is unknown.

Or:

SELECT string_to_array('a,b,c,d,e', ',')::VARCHAR[]; -- {a,b,c,d,e}
SELECT (string_to_array('a,b,c,d,e', ',')::VARCHAR[])[0]; -- NULL

SELECT (string_to_array('a,b,c,d,e', ',')::VARCHAR[])[2]; -- b
SELECT (string_to_array('a,b,c,d,e', ',')::VARCHAR[])[2:4]; -- {b,c,d}
SELECT (string_to_array('a,b,c,d,e', ',')::VARCHAR[])[:4]; --  {a,b,c,d}
SELECT (string_to_array('a,b,c,d,e', ',')::VARCHAR[])[2:]; -- {b,c,d,e}
SELECT (string_to_array('a,b,c,d,e', ',')::VARCHAR[])[:]; -- {a,b,c,d,e}

*Memos:

The type of the array above is VARCHAR[](CHARACTER VARYING[]).
If you omit ::VARCHAR[], the type of the array above is TEXT[].
Don't put any spaces in the 1st argument of string_to_array() otherwise the values separated by , have spaces.

In addition, even if you set VARCHAR(2)[2] to the array, the result is the same as shown below:

postgres=# SELECT ARRAY['a','b','c','d','e']::VARCHAR(2)[2];
    array
-------------
 {a,b,c,d,e}
(1 row)

And, the type of the array is VARCHAR[](CHARACTER VARYING[]) rather than VARCHAR(2)[2](CHARACTER VARYING(2)[2]) as shown below. *You can use pg_typeof() to check the type of a value:

postgres=# SELECT pg_typeof(ARRAY['a','b','c','d','e']::VARCHAR(2)[2]);
      pg_typeof
---------------------
 character varying[]
(1 row)

And, even if you set ::TEXT to 'a', the type of 'a' is VARCHAR(CHARACTER VARYING) rather than TEXT as shown below because the outer type conversion VARCHAR[] is prioritized:

postgres=# SELECT (ARRAY['a'::TEXT,'b','c','d','e']::VARCHAR[])[1];
 array
-------
 a
(1 row)

postgres=# SELECT pg_typeof((ARRAY['a'::TEXT,'b','c','d','e']::VARCHAR[])[1]);
     pg_typeof
-------------------
 character varying
(1 row)

Collectives™ on Stack Overflow

Declare an array in PostgreSQL

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related