Postgresql – How to the concatenatation of a text and a NULL value return a non-null result

concatnullpostgresql

Assert 1

The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the manual:

However, the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type

How to concatenate columns in a Postgres SELECT?

Assert 2

Concatenating one or more NULL values makes the result NULL.

test=# SELECT (text 'foo' || NULL) IS NULL
test-#      , (text 'bar' || char '1' || NULL ) IS NULL
test-#      , (NULL::bigint || text 'baz') IS NULL;
 ?column? | ?column? | ?column? 
----------+----------+----------
 t        | t        | t

Question

Is it possible to concatenate a text and a NULL value and get a non-null result?

In other words, how is this possible?

test=# SELECT col IS NULL AS col_is_null
test-#     , (text 'foo' || col) IS NULL AS result_is_null
test-# FROM   tbl;
 col_is_null | result_is_null 
-------------+----------------
 t           | f

Applies to any Postgres version.
A client of mine stumbled over this, relying on the result to be NULL, and I found it intriguing enough to share.
It's a bit of a trick question as I know the answer.

_{Note: CASE or COALESCE catching NULL values are typically good style, but that's not what this question is about. It's about concatenation with an actual NULL value, with the concatenation operator || and still getting a non-null result.}

Best Answer

That's because the system of CASTing array types in PostgreSQL is a bit weird (on a first sight) :-)

text || text[] makes both sides coerced to arrays.

CREATE TABLE tbl (col text ARRAY);

INSERT INTO tbl SELECT NULL;

SELECT col IS NULL AS col_is_null,
  (text 'foo' || col) IS NULL AS result_is_null
  FROM tbl;

 col_is_null | result_is_null 
-------------+----------------
 t           | f
(1 row)

Another example which may shed more light:

create temp table x as select 'foo' test, null::text[] col; 
SELECT test, col, test || col from x;
 test | col  | ?column? 
------+------+----------
 foo  | NULL | {foo}
(1 row)

Related Solutions

Mysql – No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

One or more of those character/text fields MAY have 0x00 for its content.

Try the following:

SELECT * FROM rt3 where some_text_field = 0x00 LIMIT 1;

If this returns any single row then try updating those character/text fields with:

UPDATE rt3 SET some_text_field = '' WHERE some_text_field = 0x00;

Afterwards, try another MYSQLDUMP ... ( and PostgreSQL import method ).

Postgresql – Concatenation of setof type or setof record

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).