Re: RDBMS comparison tool

On 05/01/2013 10:41, Darren Duncan wrote:
> 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.

It does indeed:
http://www.postgresql.org/docs/9.2/static/storage-toast.html
So the PostgreSQL idea of char, varchar, text, they can all store up to 
around 1gb.
I'm currently trying to find some sort of equivalence between the RDBMS 
types. For PostgreSQL, the distinction between what they might call char 
and clob are hidden from the user, but they do kind of exist internally.

>> 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.

Yes, there is good argument for this. I guess it would help to know 
exactly what the DBD type_info is used for. I'm not sure if it has some 
internal use, or whether it's just something to give to users who are 
asking for particular types.

> 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.

REAL is approximate, inexact.

> 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.

I'm not overly familiar with Perl's internal handling of number. I guess 
if you have DECIMAL from a character string Perl will switch it out to 
an approximate the moment you do a calculation on it. Furthermore if the 
DBI (or the DBDs, I'm not sure where the distinction lies) is already 
putting it into a Perl decimal which is floating point, then the battle 
has already been lost before it gets to me.

> 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.

PostgreSQL do recommend using VARCHAR instead of CHAR. Due to the way 
they are implemented, VARCHAR is usually more efficient.


Lyle