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
================================================
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
