Thread: How to deal with NULL values on dynamic queries?
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,
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,
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