6 ms·
This is missing my favorite oracle quirk: Empty strings are equivalent to null. Trying to insert an empty string into a NOT NULL column will fail, which took me
by jwitthuhn 5y ago
This is missing my favorite oracle quirk: Empty strings are equivalent to null. Trying to insert an empty string into a NOT NULL column will fail, which took me a while to understand the first time I saw it happen.
- vbezhenar 5y agoOne of oracle quirks for me is varchar2. What happened to varchar1? Nobody knows.
- tgv 5y agoTIMESTAMP WITH LOCAL TIMEZONE. Never again.
- beamatronic 5y agoLet me guess, did you get hit with more than one 3:00am?
- tmd83 5y agoLet's not forget that Oracle DB uses the system timezone as the datatype storage (not sure what other database do to be honest) so unless you were running your DB machine as UTC by default their timestamp storage would be ambiguous for DST shift and that's their default. Why wouldn't they store everything in UTC and use the system timezone for parse/formatting I have no clue.
- deleted 5y ago[deleted]
- tracker1 5y agoI've been pushing to run ALL servers as UTC for a few decades now... always store/transmit date+time as UTC as well. If it's mapped to a locale, pair it with that locale... let the client translate to/from local.
- SketchySeaBeast 5y agoOh sorry, are you looking to insert 4001 characters in your varchar2? Buckle up kids, things are gonna get rough.
- kubanczyk 5y agoOr maybe 3999 chars, of which a couple are two-byte. More fun.
- gvenzl 5y agoOracle supports up to 32k VARCHARs since 12.1 released back in 2013. It just needs to be enabled: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-093...
- icedchai 5y agovarchar1 was varchar. I should say "is", since varchar is still supported, they just recommend varchar2 since varchar "might change" (probably not...)
- marcosdumay 5y agoThat one actually changed already. If you follow the new recommendations, varchar is as good as varchar2.
- icedchai 5y agoGood to know! I haven't had to touch an Oracle database in almost 15 years, but one never knows.
- 5e92cb50239222b 5y agoSo how do you insert an empty string into a NOT NULL column? Please don't tell me that it's not possible.
- zzzeek 5y agoits... .... sorry not possible (can't store empty string in a NULL col either) for reals
- robertlagrant 5y agoJust store the string "empty" and hope there are no nihilists who've changed their names to match. Simple!
- tracker1 5y agoUse vertical tab, or another whitespace or field separator character, like EOF etc. Generally something a modern keyboard won't be entering, and won't generally be transmitted over the wire.
- jhgb 5y agoOr just prefix everything so that your string domain doesn't shrink? Then you don't have to worry what your users enter.
- munk-a 5y agoReal programmers encode emptiness as ZZZ... (I once worked maintaining a MUD that used internal memory management and marked block terminals (which were unnecessary since it stored the length of blocks it had allocated) with ZZZ)
- robertlagrant 5y agoZZZ? EEK!
- 5y ago
- marcosdumay 5y ago> Empty strings are equivalent to null. No, they are not. Empty strings are completely different from null, and if you go returning them or testing for equality, everything will break by random some single-digit percent of the time. The same for concatenating, taking the length or iterating. I imagine there's some deterministic procedure to decide what leads to an empty string and what leads to null. The one thing I know is that if you insert it on a table, you will always get null.
- tux3 5y agoFascinating. Of course it would be too easy if they were strictly the same! It seems like reading the tale of a greek programmer cursed by the gods to work with madness itself.
- GordonS 5y agoIt was years ago I first came across this, after I'd already spent a lot of time with MySQL and MSSQL, and some time with PostgreSQL and Firebase. But I still remember having a massive WTF moment!