Sunday, November 27, 2011

Selecting random row on various databases

Here's the link, and below is my peanut gallery commentary:
MySQL and Postgres: Simple and straightforward
SQL Server: Simple but odd function name to have to remember to call
DB2: Um, awkward. Can you not just do "order by RAND()" in DB2?
Oracle: So...I HAVE to make a subquery and use rownum? Oy vey. Oracle has a knack for being beyond powerful, but the simplest things can be way too clumsy. Take sqlplus for example.

Tuesday, November 22, 2011

History/Up Arrow in sqlplus

Having used PostgreSQL's psql for a few years, returning to sqlplus feels almost painful. Here's a way to at least add an up-arrow history to sqlplus -

Unfortunately it requires installing rlwrap from - but it does work well.

Monday, November 07, 2011

A modest proposal?

From a chat with Buddy.

(2:19:29 PM) Me: sometimes i wish sql, or at least individual RDBMSs, had a built-in way to "disable" a row, simply cause i run into this pattern so much :)

(2:19:46 PM) Buddy: disable a row?

(2:25:04 PM) Me: yeah, here's a sample use case, not necessarily fully thought out :)... I have to add (well I'm debating whether it's worth the effort) the ability to delete a product... but burndown_metric references product, and transaction references burndown_metric...I absolutely can't delete rows in transaction. So I have to add an enabled flag to product, and that flag will have to be checked in almost all queries (either that or i'll make something like product_v that filters out enabled=false) what if I could avoid all this and, instead of "DELETE FROM product WHERE ..." I could do "DISABLE FROM product WHERE ..." - which makes it so for all queries, by default, the row is considered deleted and gone just about automatically, but foreign keys aren't broken...and for those occasions where you want to see the disabled data, maybe you could do something special like "SELECT FROM product WHERE ... INCLUDE DISABLED"

(2:31:35 PM) Buddy: ah word, just aboput every single one of my tables I have a deleted flag (I store a datetime rather then boolean, the time it was deleted) so many of my queries have "where deleted is null"

(2:32:09 PM) Me: yeah

(2:32:29 PM) Me: just such a common pattern it seems, and i hate having to go through this time and time again :)

(2:37:08 PM) Me: in reality even if a database added the feature today, most people would be ignorant of it, and when it's used it would baffle everyone...getting results they didn't expect and not knowing about INCLUDE DISABLED

(2:38:44 PM) Me: Table A has a foreign key to Table B...yet you join the two and get no results