<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

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.