Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re^2: "undef" is not NULL and what to do about it

by morgon (Deacon)
on Feb 20, 2013 at 23:15 UTC ( #1019861=note: print w/ replies, xml ) Need Help??


in reply to Re: "undef" is not NULL and what to do about it
in thread "undef" is not NULL and what to do about it

All fields should have (default) values.
So how do you represent unknown values? By using a default "UNKNOWN"?

Then I might just as well use a NULL...

If I insert an empty string into a varchar field in Oracle, it helpfully inserts a NULL instead.
VARCHAR-fields do in theory exist in Oracle but nobody uses them.

You are talking about VARCHAR2-fields and for them an empty string IS a NULL. There is no difference at all. If you use that data-type you must be aware of this.


Comment on Re^2: "undef" is not NULL and what to do about it
Re^3: "undef" is not NULL and what to do about it
by runrig (Abbot) on Feb 21, 2013 at 00:06 UTC
    an empty string IS a NULL. There is no difference at all.

    I think the point is you can not select the data using, e.g.WHERE colname = ''.

    So how do you represent unknown values? By using a default "UNKNOWN"?

    Sometimes you know what the value is, and it's blank. There's just too many ways to look at it.

    .
Re^3: "undef" is not NULL and what to do about it
by Tux (Monsignor) on Feb 21, 2013 at 06:58 UTC

    "unknown" values are represented - in our case - by a value that falls outside the legal range. e.g. a for numeric values defined to be 0 or greater, we use -1 to be the "undefined" value and -2 to be the "known to be wrong or invalid" value. For strings that decision is taken on each case, some already have a value for "known to be unknown": . (not our choice, it is a law in that database) others might be a single space.

    varchar2 is Oracle-only, yet another reason to loathe Oracle. Indeed we use varchar2 instead of varchar, but they suck nevertheless.

    I just don't understand how you can say that an empty string equals to NULL. That is only true in the rotten Oracle world.

    FWIW I use a lot of database types (Oracle, Unify, MySQL, PostgreSQL, MariaDB, SQLite, CSV, TSV, MonetDB) and NONE is perfect. Oracle is not the only database with drawbacks.

    The is more btw, how to represent (in a database)

    • Known, valid ("normal" state in databases)
    • Known to be unknown (I checked it, but the value cannot be set)
    • Known to be absent/unavailable
    • Unknown ("NULL" in databases)
    • Known to be illegal (cannot use the illegal value, like date of birth 23770245)

    Enjoy, Have FUN! H.Merijn
    pmsig

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1019861]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (10)
As of 2014-09-02 09:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (21 votes), past polls