Re: RDBMS comparison tool

On 2013.01.04 4:17 PM, Lyle wrote:
> On 01/01/2013 03:12, Greg Sabino Mullane wrote:
>> Lyle wrote:
>>> Similar situation for PostgreSQL character.
>> Yep.
>>> Reviewing the PostgreSQL documentation on CHARACTER it mentions things
>>> like short, long and very long character strings, but lacks detail so
>>> I've emailed them about it.
>> Those are internal details about how they are stored and won't affect
>> anything as far as an application is concerned.
>
> I thought it might be useful to know strings below a certain length are stored
> uncompressed and so a little faster. Likewise very long strings have a different
> storage mechanism one might want to avoid. Although I've only just had a reply
> to my post asking for specifics, and haven't had chance to look into it further.

My understanding about Postgres either compressing strings or using "toast" 
segments for longer ones is that this is just an internal implementation detail 
and that user-facing concepts like data types should be ignoring these 
differences.  Character data is just "text", a sequence of characters of 
arbitrary length, and that's all there is to it.

>>> MySQL's FLOAT and DOUBLE are linked to several ODBC types, perhaps
>>> PostgreSQL could do the same? Or is that bad practice on the
>>> MySQL drivers part?
>> Hard to say, can you be more specific? Keeping in mind that I don't
>> actually know a whole lot about the SQL/ODBC specs, and the differences
>> therein. :)
>
>>> For SQL_DECIMAL PostgreSQL and MySQL seem to disagree on whether FLOAT
>>> or DOUBLE should be used.
>> Well, a postgres float with no precision is really the same as a
>> double precision (as you hint at below). The whole thing is quite
>> confusing when you start pulling in ODBC, SQL standard, IEEE, decimal
>> vs. binary precision, etc. Have a good link to a doc that explains
>> exactly what SQL_DECIMAL is meant to be?
>
> I've got a copy of the standard, but I'm pretty sure I'd be breaking some law if
> I copied and pasted bits. DECIMAL is supposed to be an "exact numeric". Whereas
> FLOAT, REAL, DOUBLE PRECISION are "approximate numeric".
>
> So I guess DOUBLE is a better fit as it's supposed to be more accurate. But
> neither are a good match really.

Here's the thing.  The most important difference is "exact numeric" versus 
"approximate numeric".  Your type list should clearly and explicitly separate 
these into separate rows from each other, and never cross types between them.

Things like integers or rationals or DECIMAL etc are exact numeric.

Things like FLOAT and DOUBLE are analogous to IEEE floats which are inexact and 
are defined by using a certain number of bits to store every value of their type.

I don't recall which of the above REAL goes in.

If different DBMSs use the same FOO to be exact in one or approximate in 
another, still keep them in different rows.

Since the SQL standard, as well as some other programming languages, define 
"decimal" as being exact numeric, then it is absolutely wrong to map them to 
either FLOAT or DOUBLE.  In fact, in Perl 5 the only native type that can map to 
a DECIMAL is a character string of numeric digits.  Don't shoehorn this.  There 
is no good reason to map DECIMAL to a floating point type.

Likewise, under no circumstance, map integers to floating types.

>>> It also has SQL_VARCHAR assoicated with TEXT instead of VARCHAR.
>> Not sure about this one either - if there was a reason for that,
>> I don't remember it offhand.
>>
>> Thanks for doing this work, it's good to see someone poking at
>> this. +1 for getting the DBDs somewhat consistent, although it
>> may be tricky as some (looking at you, MySQL!) play more fast and
>> loose with their data type definitions than others. :)
>
> Yes, the hard part is certainly trying to find consistency, or a way of making
> them act/emulate some consistency.

Since in Postgres a VARCHAR is just a restricted TEXT, map length-restricted 
character types to VARCHAR and unrestricted ones like Perl strings to TEXT, that 
would carry the best semantics.

-- Darren Duncan