How to deal with NULL values on dynamic queries?

Hi

Null + 1=null.
and
null + 'a' = null.

I expect that pVAL_COMENT is null (as you say).

===========================
create test environment.
===========================
create table nulltest (col1 varchar(10),col2 varchar(10));
insert into nulltest (col1,col2) values ('A','B');
insert into nulltest (col1) values ('A');
insert into nulltest (col2) values ('B');

select col1,col2 from nulltest;
select col1||col2 from nulltest;

postgres=# select col1,col2 from nulltest;
col1 | col2
------+------
A | B
A |
| B
(3 rows)

===========================
null make col1||col2 null
===========================
ex.
postgres=# select col1||col2 from nulltest;
?column?
----------
AB

(3 rows)

=============================================
even if we use quote_literal,we can not avoid tihs behavior.
=============================================
ex.
postgres=# select quote_literal(col1)||quote_literal(col2) from nulltest;
?column?
----------
'A''B'

(3 rows)

================================================
So we can use COALESCE() function to avoid this.
================================================
ex.
postgres=# select COALESCE(col1,'')||COALESCE(col2,'') from nulltest;
?column?
----------
AB
A
B
(3 rows)

ex2.
postgres=# select
quote_literal(COALESCE(col1,''))||quote_literal(COALESCE(col2,'')) from
nulltest;
?column?
----------
'A''B'
'A'''
'''B'
(3 rows)

Can you work around like this?
||quote_literal(COALESCE(pVAL_COMENT,''))

Thank you.

Hi,

I have a query that some values could be NULL, how can I deal with
this problem?

[code]
EXECUTE 'INSERT INTO '
|| quote_ident(pNOME_VIEW)
|| '('
|| quote_ident(pCHAVE_1)
|| ', DAT_INICIO, DAT_FIM, COMENT) values ('
|| quote_literal(pVAL_CHAVE_1)
|| ', '
|| quote_literal(pVAL_CHAVE_2)
|| ', '
|| quote_literal(pVAL_CAMPO1)
|| ', '
|| quote_literal(pVAL_COMENT)
|| ')';
[/code]

The variable pVAL_COMENT could be NULL or have a value. How can I deal
with this?

Sorry the bad english.

Best Regards,

--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================

Andre Lopes <lopes80andre@gmail.com> writes:

I have a query that some values could be NULL, how can I deal with this
problem?

PG 8.4 and up have a function quote_nullable() that would do what I
think you're looking for. On an older version, you could define such a
function for yourself.

regards, tom lane