How to create an array from variables in postgresql?

February 5, 2024

I want to create an array from two variables p, q.
Here is my attempt:

DO
  $code$
    DECLARE
      p text := 'Hello';
      q text := 'world!';
      ta text[];             --text array
    BEGIN
      ta := {p, q};
      RAISE INFO '% %', ta[1], ta[2];
    END
  $code$;

but it doesn’t work

postgres=# DO
postgres-#   $code$
postgres$#     DECLARE
postgres$#       p text := 'Hello';
postgres$#       q text := 'world!';
postgres$#       ta text[];             --text array
postgres$#     BEGIN
postgres$#       ta := {p, q};
postgres$#       RAISE INFO '% %', ta[1], ta[2];
postgres$#     END
postgres$#   $code$;
ERROR:  syntax error at or near "{"
LINE 8:       ta := {p, q};
                    ^

Best I can get, that does work is this:

DO
  $code$
    DECLARE
      p text := 'Hello';
      q text := 'world!';
      ta text[];             --text array
    BEGIN
      RAISE INFO 'p=[%] q=[%]', p, q;
      ta := array_append(array_append(ta, p), q);
      RAISE INFO '% %', ta[1], ta[2];
    END
  $code$;

How should I do this in pl/pgsql?

>Solution :

Arrays are documented on this page.

For an array of constants, your syntax would almost have worked except it must be written as a string: '{p, q}'. However in your case, p and q are variable and this expression is evaluated as the string 'p' and the string 'q' in an array.

To include variables and as long as p and q have the same type, which is the case in your question: ARRAY[p, q] (ARRAY constructor syntax) is how you create an array in postgres.
IMHO, it is more convenient than the other syntax, overall.

Your whole corrected code:

DO
  $code$
    DECLARE
      p text := 'Hello';
      q text := 'world!';
      ta text[];             --text array
    BEGIN
      ta := ARRAY[p, q];
      RAISE INFO '% %', ta[1], ta[2];
    END
  $code$;