Thread:
Hi @ all, I think thats a bug: SELECT '#' || '#' will work but SELECT '#' || CAST(NULL AS VARCHAR) will return only empty rows. My Query looks like this : SELECT field1 || field2 FROM ... If field2 ISNULL then everything is null. CAST does not help. thanks for your help Daniel ______________________________________________________________________________ Die Besten ihrer Klasse! WEB.DE FreeMail (1,7) und WEB.DE Club (1,9) - bei der Stiftung Warentest - ein Doppelsieg! http://f.web.de/?mc=021184
On Wednesday 01 October 2003 10:57, Daniel Schuchardt wrote: > Hi @ all, > > I think thats a bug: > > SELECT '#' || '#' > > will work but > > SELECT '#' || CAST(NULL AS VARCHAR) Nope - not a bug. > will return only empty rows. > My Query looks like this : SELECT field1 || field2 FROM ... > If field2 ISNULL then everything is null. CAST does not help. Broadly speaking VALUE op NULL = NULL You'll see similar issues with comparisons. You might find the article below useful: http://techdocs.postgresql.org/guides/BriefGuideToNulls -- Richard Huxton Archonet Ltd
But CAST(NULL AS VARCHAR) should make it a varchar so i should be able to || the both values. So perhaps a bug in CAST. The only way here would be to make a CASE WHEN - i think thats not a good behavoir. Daniel On Wednesday 01 October 2003 10:57, Daniel Schuchardt wrote: > Hi @ all, > > I think thats a bug: > > SELECT '#' || '#' > > will work but > > SELECT '#' || CAST(NULL AS VARCHAR) Nope - not a bug. > will return only empty rows. > My Query looks like this : SELECT field1 || field2 FROM ... If field2 > ISNULL then everything is null. CAST does not help. Broadly speaking VALUE op NULL = NULL You'll see similar issues with comparisons. You might find the article below useful: http://techdocs.postgresql.org/guides/BriefGuideToNulls
> But CAST(NULL AS VARCHAR) should make it a varchar so i should be able > to || the both values. So perhaps a bug in CAST. > > The only way here would be to make a CASE WHEN - i think thats not a > good behavoir. NULL is null even it have varchar type, it is not an empty string. Try to do the following: select field1 || case when field2 is null then '' else field2 end from my_big_table;
Read the docs... Hm looking towards the doks thats the right behavoir. Also from the logical point thats the right behavoir. But I don't like it. The CAST should return an empty string in that case (looking from the practical standpoint). Hm. -----Ursprüngliche Nachricht----- Von: dev@archonet.com [mailto:dev@archonet.com] Gesendet: Mittwoch, 1. Oktober 2003 12:08 An: Daniel Schuchardt; pgsql-general@postgresql.org Betreff: Re: [GENERAL] <no subject> On Wednesday 01 October 2003 10:57, Daniel Schuchardt wrote: > Hi @ all, > > I think thats a bug: > > SELECT '#' || '#' > > will work but > > SELECT '#' || CAST(NULL AS VARCHAR) Nope - not a bug. > will return only empty rows. > My Query looks like this : SELECT field1 || field2 FROM ... If field2 > ISNULL then everything is null. CAST does not help. Broadly speaking VALUE op NULL = NULL You'll see similar issues with comparisons. You might find the article below useful: http://techdocs.postgresql.org/guides/BriefGuideToNulls -- Richard Huxton Archonet Ltd
On Wed, 2003-10-01 at 04:57, Daniel Schuchardt wrote: > Hi @ all, > > I think thats a bug: > > SELECT '#' || '#' > > will work but > > SELECT '#' || CAST(NULL AS VARCHAR) > > will return only empty rows. > My Query looks like this : SELECT field1 || field2 FROM ... > If field2 ISNULL then everything is null. CAST does not help. You want to use COALESCE. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "Our computers and their computers are the same color. The conversion should be no problem!" Unknown
On Wed, 2003-10-01 at 11:19, Daniel Schuchardt wrote: > But CAST(NULL AS VARCHAR) should make it a varchar NULL is still null, whatever you cast it to. The column type is independent of whether one particular datum is null. Instead of casting, you want COALESCE(field2, '') to give you an empty string if the value is null. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Yet if any man suffer as a Christian, let him not be ashamed; but let him glorify God on this behalf." I Peter 4:16
On Wednesday 01 October 2003 11:32, Daniel Schuchardt wrote: > Read the docs... > > Hm looking towards the doks thats the right behavoir. Also from the > logical point thats the right behavoir. But I don't like it. The CAST > should return an empty string in that case (looking from the practical > standpoint). Many would (probably will) disagree. AFAIK in SQL NULLs are typed, so it's perfectly reasonable to have a null int4, null varchar etc. -- Richard Huxton Archonet Ltd
On Wed, 2003-10-01 at 11:32, Daniel Schuchardt wrote: > Read the docs... > > Hm looking towards the doks thats the right behavoir. Also from the > logical point thats the right behavoir. But I don't like it. The CAST > should return an empty string in that case (looking from the practical > standpoint). All CAST is doing is changing the type of the datum; it does not change its value except perhaps as a side effect. A null string is not the same as an empty string. I don't at all see why you should expect anything different, especially when the COALESCE() function is already provided to do exactly what you want. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Yet if any man suffer as a Christian, let him not be ashamed; but let him glorify God on this behalf." I Peter 4:16
Oh thanks, thats it. I don't know that function. Now I agree ;-) Thanks Daniel -----Ursprüngliche Nachricht----- All CAST is doing is changing the type of the datum; it does not change its value except perhaps as a side effect. A null string is not the same as an empty string. I don't at all see why you should expect anything different, especially when the COALESCE() function is already provided to do exactly what you want.