Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

--array cast to text will get curly braces around it by default
select 
  ('{"orderid","product","address"}'::VARCHAR(100)[])::text as array_as_text1,
  ARRAY['orderid', 'product', 'address']::text as array_as_text2;

array_as_text1
array_as_text2

{orderid,product,address}
{orderid,product,address}

SELECT 1

--valid array literal
select unnest('{"orderid","product","address"}'::VARCHAR(100)[]);

unnest

orderid

product

address

SELECT 3

--valid array literal, but it doesn't get cast automatically
select unnest('{"orderid","product","address"}');

ERROR:  function unnest(unknown) is not unique
LINE 2: select unnest('{"orderid","product","address"}');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

--invalid array literal
select unnest(['orderid', 'product', 'address']::VARCHAR(100)[]);

ERROR:  syntax error at or near "["
LINE 2: select unnest(['orderid', 'product', 'address']::VARCHAR(100...
                      ^

--invalid array literal
select unnest(['orderid', 'product', 'address']);

ERROR:  syntax error at or near "["
LINE 2: select unnest(['orderid', 'product', 'address']);
                      ^