Alvaro Herrera wrote:
Ken Johanson wrote:
Alvaro Herrera wrote:
If you are arguing that the spec's definition of the CHARACTER type is
not really very useful, I think you are going to find a lot of
supporters. You can send your complaints to the SQL committee; but
then, it is unlikely that this is going to change anytime soon because
of the fear of breaking backwards compatibility.
Agreed. There may be allot of users who in the case of CAST, need to
extract the leftmost digit, and for those database and their users that
do this by default, it would cause harm.
Right. And those that don't need that are certainly using a cast to
CHARACTER VARYING.
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?
mysql> CREATE TEMPORARY TABLE ttx (c char);
Query OK, 0 rows affected (0.00 sec)
mysql> describe ttx;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> INSERT INTO ttx VALUES (123);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SELECT * FROM ttx;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Hmm - looks like a cast to char doesn't produce values that fit into a
column defined as char. I'll say this for MySQL - always something new
to learn!
Looks like you have to choose between convenience and sanity Ken.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
