Search Postgresql Archives

Re: SELECT CAST(123 AS char) -> 1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Richard Huxton wrote:

What I couldn't figure out was what type MySQL was using. I mean, what type is this?

mysql> SELECT cast(a as char) as achar FROM tt;
+-------+
| achar |
+-------+
| 1     |
| 10    |
+-------+
2 rows in set (0.00 sec)

Is it char(2)?

mysql> CREATE TEMPORARY TABLE ttchar0 AS SELECT cast(a as char) as achar FROM tt;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> describe ttchar0;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| achar | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


TA DAH! It looks like you are casting to varchar anyway Ken, it's just that MySQL isn't letting you spell it properly. So does an unconstrained "char" just map to varchar with MySQL then?



I think the issue here (subjective) is: is unconstrained CAST(n AS char), a DDL statement in the storage sense, or in the return type/function sense? Thats how PG and the other's CAST differ, anyway. While the spec doesn't seem to qualify that and it's safe to assume 'char' type should just behave the same even in the context of cast + number, both Ms and My's CAST treat numeric inputs as auto-size on select stmts.

Add to that, that Mysql does the auto-trim thing which might be affecting/confusing some operations (inserts for starters). I don't know. Surprisingly I've never had a user complain about that trim spec-deviation, nor case-insens compares (though I always teach/code case-folding for portability).

Richard, when you say "casting to varchar anyway", it's not possible by syntax (shortcoming in their current cast impl), so using char was a next best thing to try. Your test ultimately applies I think to a table DDL and not select.

Anyway, there are to many barriers (real or imagined) for my users to migrate to PG from My and Ms (AS-less labels, result set metadata, auto-generated keys, now stricter typing) so I've already resolved to check back and see how things look when 8.4 comes out. Maybe it'll be more spec compliant... a good thing, for one DB ..or another..

I'm patient and an old pro at this, I've been evaluating PG for about 9 years now, and despite the amazing number of features and advantages it's always had, customers come out in favor of the other DBs because they only need a small subset of them, and rate convenience (ala autocast) over specs which they know nothing of. Well, at least we have standard_conforming_strings now. I digress and am touching on another thread someone brought up.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux