Thread: Empty String Comparison Standard compliant?

Just a short question (PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1) 

SELECT ('' > 'GDMF') ; SELECT ('GDMF' > '');?column? 
----------f
(1 row)
?column? 
----------t
(1 row)

Are these results standard compliant? 
Looks at least pretty reasonable to me. 
It's just because I have a DBMS here, where the results are vice versa. 

TIA. 

Regards, Christoph 

Christoph Haller <ch@rodos.fzk.de> writes:
> [ '' is less than 'GDMF' ]

> Are these results standard compliant? 

I doubt that the SQL standard really says anything on the subject, but
ISTM any rational string-sorting algorithm would put '' before anything
else.

> It's just because I have a DBMS here, where the results are vice versa. 

Let me guess ... it's Oracle?  Last I heard Oracle had a problem
distinguishing empty strings from NULLs.  If it sorts NULLs at the
end then it wouldn't be surprising for empty strings to come out at
the end in an ORDER BY sort.  However, I'm not sure that explains
the exact example you cite of boolean comparison results.  If it
were taking the '' as a NULL then both comparisons ought to return
NULL.
        regards, tom lane

On Fri, 30 Jan 2004, Christoph Haller wrote:

> Just a short question (PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1)
>
> SELECT ('' > 'GDMF') ; SELECT ('GDMF' > '');
>  ?column?
> ----------
>  f
> (1 row)
>
>  ?column?
> ----------
>  t
> (1 row)
>
> Are these results standard compliant?

I think so in general.  If the two lengths are not equal, then
"effectively" the shorter string is replaced by an extended version.  If
it's NO PAD (varchar/text for us) the character used is a character that
sorts less than any string.  Otherwise (char) it's a space.  Then you use
the collating sequence to determine the value of >.  Most will put space
before any of GDMF, so the above seems reasonable.