Author: Paul McNett
Posted: 2011-11-17 18:26:48 Link
I'm using PostgreSQL for the first time, after long-time use of MySQL and SQLite. So
far so good, although I noticed a pretty key difference in character fields. For a
column definition of 'last_name char(30)', the following code:
biz.Record.last_name
Will result in the following on PostgreSQL:
"McNett "
Which surprised me greatly, because it results in the following output on MySQL and
SQLite:
"McNett"
I must be overlooking something exceedingly obvious, or I'd have seen this issue
discussed before. Right?
Paul
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4EC59838.4090806@ulmcnett.com
Author: Paul McNett
Posted: 2011-11-17 18:37:26 Link
On 11/17/11 3:26 PM, Paul McNett wrote:
> I must be overlooking something exceedingly obvious, or I'd have seen this issue
> discussed before. Right?
I guess I need to convert those char(n) to varchar(n) fields and it'll be okay. I
guess postgres probably made the correct decision to not implicitly strip those
characters.
Paul
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4EC59AB6.2060301@ulmcnett.com
Author: Dennis Lee Bieber
Posted: 2011-11-17 23:34:22 Link
On Thu, 17 Nov 2011 15:37:26 -0800, Paul McNett <p@ulmcnett.com>
declaimed the following in gmane.comp.python.dabo.user:
> On 11/17/11 3:26 PM, Paul McNett wrote:
> > I must be overlooking something exceedingly obvious, or I'd have seen this issue
> > discussed before. Right?
>
> I guess I need to convert those char(n) to varchar(n) fields and it'll be okay. I
> guess postgres probably made the correct decision to not implicitly strip those
> characters.
>
One could argue both ways -- MySQL implicitly strips trailing spaces
on retrieval, just as it implicitly pads CHAR types with trailing spaces
to fill the field on store. PostgreSQL is implicitly padding CHAR on
input, and not stripping on retrieval. I'd probably prefer MySQL's
behavior -- If I put one character into a CHAR(30) field, it is nice to
know I'll only see one character on retrieval.
http://dev.mysql.com/doc/refman/5.0/en/char.html
If trailing spaces are significant, VARCHAR is the proper type (but
it appears older MySQL stripped on input to them <G>)
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/ja4n8b$dou$1@dough.gmane.org
Author: Uwe Grauer
Posted: 2011-11-18 11:28:32 Link
On 11/18/2011 12:26 AM, Paul McNett wrote:
> I'm using PostgreSQL for the first time, after long-time use of MySQL and SQLite. So
> far so good, although I noticed a pretty key difference in character fields. For a
> column definition of 'last_name char(30)', the following code:
>
> biz.Record.last_name
>
> Will result in the following on PostgreSQL:
>
> "McNett"
>
> Which surprised me greatly, because it results in the following output on MySQL and
> SQLite:
>
> "McNett"
>
> I must be overlooking something exceedingly obvious, or I'd have seen this issue
> discussed before. Right?
>
> Paul
Most databases use trailing spaces in char fields which are shorter than
the defined lenght.
Maybe MySql uses this representation for convenience.
How does "abc " and "abc" compare for char fields in Mysql.
Are those equal??
See:
http://troels.arvin.dk/db/rdbms/#data_types-char
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/b_char.htm#i1596
Uwe
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4EC687B0.9090602@googlemail.com
Author: Richard Kaye
Posted: 2011-11-18 12:00:36 Link
Sounds like DBF style behavior to me. :)
--
rk
-----Original Message-----
From: dabo-users-bounces@leafe.com [mailto:dabo-users-bounces@leafe.com] On Behalf Of Paul McNett
Sent: Thursday, November 17, 2011 6:27 PM
To: Dabo Users list
Subject: [dabo-users] PostgreSQL char fields are rpadded
I'm using PostgreSQL for the first time, after long-time use of MySQL and SQLite. So far so good, although I noticed a pretty key difference in character fields. For a column definition of 'last_name char(30)', the following code:
biz.Record.last_name
Will result in the following on PostgreSQL:
"McNett "
Which surprised me greatly, because it results in the following output on MySQL and
SQLite:
"McNett"
I must be overlooking something exceedingly obvious, or I'd have seen this issue discussed before. Right?
Paul
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4EC59838.4090806@ulmcnett.com
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/DF1EEF11E586A64FB54A97F22A8BD0441D44FAF0CC@ACKBWDDQH1.artfact.local
Author: Adrian Klaver
Posted: 2011-11-18 12:10:01 Link
On Friday, November 18, 2011 9:00:36 am Richard Kaye wrote:
> Sounds like DBF style behavior to me. :)
>
No its SQL standard behavior:
http://www.postgresql.org/docs/9.0/interactive/datatype-character.html
"SQL defines two primary character types: character varying(n) and character(n),
where n is a positive integer. Both of these types can store strings up to n
characters (not bytes) in length. An attempt to store a longer string into a
column of these types will result in an error, unless the excess characters are
all spaces, in which case the string will be truncated to the maximum length.
(This somewhat bizarre exception is required by the SQL standard.) If the string
to be stored is shorter than the declared length, values of type character will
be space-padded; values of type character varying will simply store the shorter
string.
If one explicitly casts a value to character varying(n) or character(n), then an
over-length value will be truncated to n characters without raising an error.
(This too is required by the SQL standard.) " ...
..."Values of type character are physically padded with spaces to the specified
width n, and are stored and displayed that way. However, the padding spaces are
treated as semantically insignificant. Trailing spaces are disregarded when
comparing two values of type character, and they will be removed when converting
a character value to one of the other string types. Note that trailing spaces
are semantically significant in character varying and text values. "
--
Adrian Klaver
adrian.klaver@gmail.com
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/201111180910.02007.adrian.klaver@gmail.com
Author: John Fabiani
Posted: 2011-11-18 13:15:35 Link
On Friday, November 18, 2011 09:10:01 AM Adrian Klaver wrote:
> No its SQL standard behavior:
> http://www.postgresql.org/docs/9.0/interactive/datatype-character.html
+1
Johnf
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/1365231.JvZTViMotR@linux-12
Author: Paul McNett
Posted: 2011-11-18 13:18:36 Link
On 11/18/11 9:10 AM, Adrian Klaver wrote:
> Trailing spaces are disregarded when
> comparing two values of type character,
Ooh, here's an inconsistency, since in Python "abc " != "abc". Since the database
does the padding/unpadding, the char values should come to the Dabo layer unpadded.
Paul
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4EC6A17C.6090003@ulmcnett.com
Author: Ed Leafe
Posted: 2011-11-18 13:27:40 Link
On Nov 18, 2011, at 12:18 PM, Paul McNett wrote:
> Ooh, here's an inconsistency, since in Python "abc " != "abc". Since the database
> does the padding/unpadding, the char values should come to the Dabo layer unpadded.
If you want that behavior, make it varchar, not char.
What if you stored "abc " in a char(20) field? What would be a consistent value to receive back?
-- Ed Leafe
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/37944427-E7C5-46FA-AE2A-389A17CEC42A@leafe.com
Author: Paul McNett
Posted: 2011-11-18 13:46:22 Link
On 11/18/11 10:27 AM, Ed Leafe wrote:
> On Nov 18, 2011, at 12:18 PM, Paul McNett wrote:
>
>> Ooh, here's an inconsistency, since in Python "abc " != "abc". Since the database
>> does the padding/unpadding, the char values should come to the Dabo layer unpadded.
>
> If you want that behavior, make it varchar, not char.
Yes, that's what I'm doing now.
> What if you stored "abc " in a char(20) field? What would be a consistent value to receive back?
The inconconsistency that I'm presently talking about is that when comparing fields
in postgres, as with "where <field_name> = <other_field_name>", the padded characters
are insignificant. However, when comparing the strings in Python the padded chars are
significant.
Since postgres turns "abc" into "abc " when updating, and since postgres strips
the padded spaces in comparisons, it seems to follow that when psycopg2 fetches the
data from postgres, it should strip out the trailing spaces in char fields so that
python comparisons of the data don't take into account the padded spaces.
Paul
_______________________________________________
Post Messages to: Dabo-users@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/4EC6A7FE.10903@ulmcnett.com