Thread:

From
"Daniel Schuchardt"
Date:
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


Re:

From
Richard Huxton
Date:
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

Re:

From
"Daniel Schuchardt"
Date:
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



Re:

From
Alexander Litvinov
Date:
> 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;

Re:

From
"Daniel Schuchardt"
Date:
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



Re:

From
Ron Johnson
Date:
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


Re:

From
Oliver Elphick
Date:
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


Re:

From
Richard Huxton
Date:
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

Re:

From
Oliver Elphick
Date:
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


Re:

From
"Daniel Schuchardt"
Date:
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.