*Memos:
An array has elements from
[1]
but not from[0]
so[0]
returnsNULL
.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 orDO
statement because the type may be different from your expectation and there is some case which you cannot create an array without type conversion. *My answer explains type conversion in detail.The doc explains a multi-dimensional array in detail.
My answer explains how to create and use the 1D(one-dimensional) array with
VARCHAR[]
in detail.My answer explains how to create and use the 1D array with
INT[]
in detail.My answer explains how to create an use an empty array in detail.
My post explains how to iterate a 1D and 2D array with a FOREACH and FOR statement.
You can create and use a 2D(two dimensional) array with these ways below:
DO $$
DECLARE
_2d_arr VARCHAR[] := ARRAY[
['a','b','c','d'],
['e','f','g','h'],
['i','J','k','l']
];
BEGIN
RAISE INFO '%', _2d_arr; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
RAISE INFO '%', _2d_arr[0][2]; -- NULL
RAISE INFO '%', _2d_arr[2]; -- NULL
RAISE INFO '%', _2d_arr[2][0]; -- NULL
RAISE INFO '%', _2d_arr[2][3]; -- g
RAISE INFO '%', _2d_arr[2:2]; -- {{e,f,g,h}}
RAISE INFO '%', _2d_arr[1:1][2:3]; -- {{b,c}}
RAISE INFO '%', _2d_arr[2:2][2:3]; -- {{f,g}}
RAISE INFO '%', _2d_arr[3:3][2:3]; -- {{J,k}}
RAISE INFO '%', _2d_arr[1:3][2:3]; -- {{b,c},{f,g},{J,k}}
RAISE INFO '%', _2d_arr[:][:]; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
RAISE INFO '%', _2d_arr[1][2:3]; -- {{b,c}} -- Tricky
RAISE INFO '%', _2d_arr[2][2:3]; -- {{b,c},{f,g}} -- Tricky
RAISE INFO '%', _2d_arr[3][2:3]; -- {{b,c},{f,g},{J,k}} -- Tricky
END
$$;
*Memos:
The type of the array above is
VARCHAR[]
(CHARACTER VARYING[]
).You can set
VARCHAR[][]
,VARCHAR[][][]
, etc to_2d_arr
, then the type of_2d_arr
is automatically converted toVARCHAR[]
(CHARACTER VARYING[]
) but you cannot setVARCHAR
to_2d_arr
otherwise there is the error.The last 3
RAISE INFO ...
are tricky.If the number of the elements in each 1D array in
_2d_arr
is different, there is error.Don't create the 2D array which has numbers and strings otherwise there is the error.
Or:
DO $$
DECLARE
_2d_arr VARCHAR[] := '{
{a,b,c,d},
{e,f,g,h},
{i,j,k,l}
}';
BEGIN
RAISE INFO '%', _2d_arr; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
RAISE INFO '%', _2d_arr[0][2]; -- NULL
RAISE INFO '%', _2d_arr[2]; -- NULL
RAISE INFO '%', _2d_arr[2][0]; -- NULL
RAISE INFO '%', _2d_arr[2][3]; -- g
RAISE INFO '%', _2d_arr[2:2]; -- {{e,f,g,h}}
RAISE INFO '%', _2d_arr[1:1][2:3]; -- {{b,c}}
RAISE INFO '%', _2d_arr[2:2][2:3]; -- {{f,g}}
RAISE INFO '%', _2d_arr[3:3][2:3]; -- {{J,k}}
RAISE INFO '%', _2d_arr[1:3][2:3]; -- {{b,c},{f,g},{J,k}}
RAISE INFO '%', _2d_arr[:][:]; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
RAISE INFO '%', _2d_arr[1][2:3]; -- {{b,c}} -- Tricky
RAISE INFO '%', _2d_arr[2][2:3]; -- {{b,c},{f,g}} -- Tricky
RAISE INFO '%', _2d_arr[3][2:3]; -- {{b,c},{f,g},{J,k}} -- Tricky
END
$$;
*Memos:
The type of the array above is
VARCHAR[]
(CHARACTER VARYING[]
).You can set
VARCHAR[][]
,VARCHAR[][][]
, etc to_2d_arr
, then the type of_2d_arr
is automatically converted toVARCHAR[]
(CHARACTER VARYING[]
) but you cannot setVARCHAR
to_2d_arr
otherwise there is the error.The last 3
RAISE INFO ...
are tricky.If the number of the elements in each 1D array in
_2d_arr
is different, there is error.
In addition, even if you set VARCHAR(2)[2]
to the array, the result is the same and the type of the 2D array is VARCHAR[]
(CHARACTER VARYING[]
) as shown below:
DO $$
DECLARE -- ↓ ↓ ↓ ↓ ↓ ↓
_2d_arr VARCHAR(2)[2] := ARRAY[
['a','b','c','d'],
['e','f','g','h'],
['i','J','k','l']
];
BEGIN
RAISE INFO '%', _2d_arr; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}},
RAISE INFO '%', pg_typeof(_2d_arr); -- character varying[]
END
$$;
And, even if you set ::TEXT
to 'a', the type of 'a' is VARCHAR
(CHARACTER VARYING
) rather than TEXT
as shown below because the type VARCHAR[]
set to _2d_arr
is prioritized. *You cannot set ::TEXT[]
to each 1D array otherwise there is error but you can set ::TEXT[]
to each 1D array if you set the keyword ARRAY
just before each 1D array but the type of each row is VARCHAR[]
(CHARACTER VARYING[]
) rather than TEXT[]
because the type VARCHAR[]
set to _2d_arr
is prioritized as well:
DO $$
DECLARE
_2d_arr VARCHAR[] := ARRAY[
['a'::TEXT,'b','c','d'],
['e','f','g','h'],
['i','J','k','l']
];
BEGIN
RAISE INFO '%', _2d_arr[1][1]; -- a
RAISE INFO '%', pg_typeof(_2d_arr[1][1]); -- character varying
END
$$;
Top comments (0)