Type of NULL is important

The following SQL query shows how different can be the result of concatenating arrays with NULL values:

select '{e1,e2}'::text[] || 't'::text     as normal_array_concatenation,
       NULL::text[] || 't'::text         as appending_element_to_existing_NULL_value_array, 
       NULL || 't'::text                 as appending_element_to_NULL_value,
       '{e1,e2}'::text[] || NULL::text   as appending_typed_NULL,
       '{e1,e2}'::text[] || NULL::text[] as appending_typed_NULL_array;

The result of the execution (on 9.0) is:

─[ RECORD 1 ]──────────────────────────────────┬─────────────
normal_array_concatenation                     │ {e1,e2,t}
appending_element_to_existing_null_value_array │ {t}
appending_element_to_null_value                │ 
appending_typed_null                           │ {e1,e2,NULL}
appending_typed_null_array                     │ {e1,e2}

That explains why one can simply initialize a new array variable in PL/pgSQL and then immediately start concatenating it with values, not pre-initializing it with an empty array (that you have to do when you want to populate a text string... you cannot just take a NULL::text variable and start concatenating strings to it, you have to first pre-inizialize it with en empty string, but with arrays you can).

Another very important issue, that is related to that beheviour of arrays, is that when creating dynamic SQL queries for EXECUTE command in PL/pgSQL you SHOULD always put explicit type casts to the variables that you quote using quote_literal() or quote_nullable() when building dynamic queries:

  r text[];
  t text   := NULL;
  a text[] := NULL;

  SELECT '{e1,e2}'::text[] || a INTO r;
  RAISE INFO 'array concatenate: r is %', r;

  EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(a) || $$::text[] $$ INTO r;
  RAISE INFO 'array concatenate from EXECUTE: r is %', r;

  SELECT '{e1,e2}'::text[] || t INTO r;
  RAISE INFO 'array append: r is %', r;

  EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(t) || $$::text $$ INTO r;
  RAISE INFO 'array append from EXECUTE: r is %', r;

  /* These 2 examples will fail on the runtime 
     throwing exception (operator is not unique: text[] || unknown)
  --EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(a) INTO r;
  --RAISE INFO 'r is %', r;
  --EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(t) INTO r;
  --RAISE INFO 'r is %', r;



Anonymous said...

Online casino | Get 200% bonus - KDAM News
online casino | deccasino Get 200% bonus on new งานออนไลน์ member registration in the latest 온카지노 kdam app

klaus said...

Private APN SIM cards simplify administration, which is a major benefit. Companies may establish specific routing rules and manage which devices are allowed access to the network. In addition to improving network security, this degree of command also makes administration much easier. It's a huge help for IT departments responsible for keeping sensitive information secure and accessible at all times. To maintain an edge in today's increasingly digital marketplace, Private APN SIM Cards are a potent instrument.