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
