<no subject>

[Daniel Schuchardt]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Daniel Schuchardt]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alexander Litvinov]

> 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;

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Daniel Schuchardt]

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: de*@archonet.com [mailto:de*@archonet.com]
Gesendet: Mittwoch, 1. Oktober 2003 12:08
An: Daniel Schuchardt; pg***********@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ron Johnson]

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. ro***********@cox.net
Jefferson, LA USA

"Our computers and their computers are the same color. The
conversion should be no problem!"
Unknown
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Oliver Elphick]

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 Ol************@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Oliver Elphick]

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 Ol************@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Daniel Schuchardt]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org