Thread: SELECT CAST(123 AS char) -> 1
For sake of interoperability (and using an API that requires String-type hashtable keys), I'm trying to find a single CAST (int -> var/char) syntax that works between the most databases. Only char seems to be a candidate, but in 8.3 casting from an integer outputs only the first char... Is this a bug, or would someone like to horrify me by stating something like "spec says this is correct". :-) I noticed this is also occurring on date/time types though that's not my need/concern.
Ken Johanson <pg-user@kensystem.com> writes:
> For sake of interoperability (and using an API that requires String-type
> hashtable keys), I'm trying to find a single CAST (int -> var/char)
> syntax that works between the most databases. Only char seems to be a
> candidate, but in 8.3 casting from an integer outputs only the first char...
> Is this a bug, or would someone like to horrify me by stating something
> like "spec says this is correct". :-)
Okay: the spec says this is correct.
SQL92 section 6.1 <data type> quoth
<character string type> ::=
CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]
...
4) If <length> is omitted, then a <length> of 1 is implicit.
Therefore, writing just "char" is defined as equivalent to "char(1)".
Also, section 6.10 <cast specification> defines an explicit cast to
a fixed-length string type as truncating or padding to the target
length (LTD):
Case:
i) If the length in characters of SV is equal to LTD, then TV
is SV.
ii) If the length in characters of SV is larger than LTD, then
TV is the first LTD characters of SV. If any of the re-
maining characters of SV are non-<space> characters, then a
completion condition is raised: warning-string data, right
truncation.
iii) If the length in characters M of SV is smaller than LTD,
then TV is SV extended on the right by LTD-M <space>s.
We don't report a "completion condition" for lack of any infrastructure
for that, but the result of the expression is per spec.
Possibly you could get what you want by casting to char(10) or so.
regards, tom lane
Tom Lane wrote: > SQL92 section 6.1 <data type> quoth > > <character string type> ::= > CHARACTER [ <left paren> <length> <right paren> ] > | CHAR [ <left paren> <length> <right paren> ] > > ... > > 4) If <length> is omitted, then a <length> of 1 is implicit. > > Therefore, writing just "char" is defined as equivalent to "char(1)". However when length is not defined I think it will generally be safe(r) to auto-size. In the grand scheme auto-size creates much more sensible output than a 1-char wide one (even if right-padded to max char-length of the type). > > Also, section 6.10 <cast specification> defines an explicit cast to > a fixed-length string type as truncating or padding to the target > length (LTD): > And PG does this, perfectly. It even right-pads, the other databases (tried My and Ms) do not... > > > Possibly you could get what you want by casting to char(10) or so. > Alas the behavior is different. The right padding exists (in PG). So I cannot get uniform behavior (the other DB's fault I agree for not supporting cast as varchar). Unless PG can start throwing an exception in this version when it truncates to implicit-1, I think it should be forgiving and auto-size.. Is it possible to override this built-in cast function with a create-cast?
On Mon, Feb 11, 2008 at 10:36:49PM -0700, Ken Johanson wrote: > For sake of interoperability (and using an API that requires String-type > hashtable keys), I'm trying to find a single CAST (int -> var/char) > syntax that works between the most databases. Only char seems to be a > candidate, but in 8.3 casting from an integer outputs only the first char... Does it have to be a cast? I would've thought to_char() might work more reliably across databases. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
"Ken Johanson" <pg-user@kensystem.com> writes: > Tom Lane wrote: > >> SQL92 section 6.1 <data type> quoth >> >> <character string type> ::= >> CHARACTER [ <left paren> <length> <right paren> ] >> | CHAR [ <left paren> <length> <right paren> ] >> >> ... >> >> 4) If <length> is omitted, then a <length> of 1 is implicit. >> >> Therefore, writing just "char" is defined as equivalent to "char(1)". > > However when length is not defined I think it will generally be safe(r) to > auto-size. In the grand scheme auto-size creates much more sensible output than > a 1-char wide one (even if right-padded to max char-length of the type). Sure, but you're a prime candidate for understanding the value of following the spec if you're trying to write software that works with multiple databases. It's a bit crazy to be using CHAR and then complaining about padding... That's what CHAR is for. If the other database doesn't support varchar it's so far from the SQL spec that writing something portable between it and something else is probably hopeless. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
Gregory Stark wrote: > "Ken Johanson" <pg-user@kensystem.com> writes: > >> Tom Lane wrote: >> >>> SQL92 section 6.1 <data type> quoth >>> >>> <character string type> ::= >>> CHARACTER [ <left paren> <length> <right paren> ] >>> | CHAR [ <left paren> <length> <right paren> ] >>> >>> ... >>> >>> 4) If <length> is omitted, then a <length> of 1 is implicit. >>> >>> Therefore, writing just "char" is defined as equivalent to "char(1)". >> However when length is not defined I think it will generally be safe(r) to >> auto-size. In the grand scheme auto-size creates much more sensible output than >> a 1-char wide one (even if right-padded to max char-length of the type). > > Sure, but you're a prime candidate for understanding the value of following > the spec if you're trying to write software that works with multiple > databases. The spec has diminished in this (CAST without length) context: a) following it produces an output which has no usefulness whatsoever (123 != 1) b) all the other databases chose to not follow the spec in the context of cast and char with implicit length. When the length is unqualified, a cast to char should one of: 1) failfast 2) auto-size to char-count (de facto) 3) pad to the max-length > > It's a bit crazy to be using CHAR and then complaining about padding... I did say earlier that I could at least accept padding to the max-char length, even though in my use-case it wont work.
On 2008-02-12 07:30, Ken Johanson wrote:
I doubt that:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm
http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx
Your specific example is covered here:
http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_cast
and here:
http://answers.yahoo.com/question/index?qid=20071017084134AA4mCJC
What is wrong with using VARCHAR for your purpose???????????????????????????? If you want the string auto-sized, that is what VARCHAR is for.
CHAR is, BY DEFINITION, a DECLARED fixed length.
I OFTEN use a cast of CHAR to get just the first character.
Sure, but you're a prime candidate for understanding the value of following the spec if you're trying to write software that works with multiple databases.
The spec has diminished in this (CAST without length) context:
a) following it produces an output which has no usefulness whatsoever (123 != 1)
b) all the other databases chose to not follow the spec in the context of cast and char with implicit length.
I doubt that:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm
http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx
Your specific example is covered here:
http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_cast
and here:
http://answers.yahoo.com/question/index?qid=20071017084134AA4mCJC
When the length is unqualified, a cast to char should one of:
1) failfast
2) auto-size to char-count (de facto)
3) pad to the max-length
What is wrong with using VARCHAR for your purpose???????????????????????????? If you want the string auto-sized, that is what VARCHAR is for.
CHAR is, BY DEFINITION, a DECLARED fixed length.
Dean Gibson (DB Administrator) wrote: > On 2008-02-12 07:30, Ken Johanson wrote: >>> >>> Sure, but you're a prime candidate for understanding the value of >>> following the spec if you're trying to write software that works with >>> multiple databases. >> >> The spec has diminished in this (CAST without length) context: >> a) following it produces an output which has no usefulness whatsoever >> (123 != 1) > I *OFTEN* use a cast of CHAR to get just the first character. > I'm guessing you declare an explicit length of 1 (for portability), or do you "CAST (x as char)"? And one might ask in what context we'd need CHAR(1) on a numeric type, or else if substr/ing or left() make the code more readable for other data types.. >> b) all the other databases chose to not follow the spec in the context >> of cast and char with implicit length. > > I doubt that: > > http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm > http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx > The actual behavior is to autosize on MS and My. I do not have DB2 but would be curious to know how it behaves. > >> >> When the length is unqualified, a cast to char should one of: >> >> 1) failfast >> 2) auto-size to char-count (de facto) >> 3) pad to the max-length > > What is wrong with using VARCHAR for your > purpose???????????????????????????? Simply that a commonly used database (my) does not support it. I HAVE to support that one (too widely in use), better/worse, its not my choice. Even at the expense of its standards deviation, and for doubting the vendor(s) will the change behavior (and break the app).
Ken Johanson <pg-user@kensystem.com> writes:
>>> What is wrong with using VARCHAR for your
>>> purpose????????????????????????????
> Simply that a commonly used database (my) does not support it.
They do support char(n) in this context, which would have the advantage
of being standards compliant as well as de-facto portable.
> Even at the expense of its standards deviation, and for doubting the
> vendor(s) will the change behavior (and break the app).
You say that with a straight face while arguing for us to diverge from
the spec? It seems unlikely to me that other DBs would change their
historical behavior except to make it comply to spec.
regards, tom lane
Tom Lane wrote: > >> Simply that a commonly used database (my) does not support it. > > They do support char(n) in this context, which would have the advantage > of being standards compliant as well as de-facto portable. > Hmm, interesting. Mysql actual returns: select cast(123 AS char(10)) -> '123' (agreed wrong since length is explicit) And PG (Ms also) gives: select cast(123 AS char(10)) -> '123 ' (tested via jdbc driver since hard to visualize the space in psql) and PG: select '='||cast(123 AS char(10))||'=' -> '=123=' (test via psql)
On 2008-02-12 16:17, Ken Johanson wrote: > Dean Gibson (DB Administrator) wrote: > ... > > I'm guessing you declare an explicit length of 1 (for portability), or > do you "CAST (x as char)"? And one might ask in what context we'd need > CHAR(1) on a numeric type, or else if substr/ing or left() make the > code more readable for other data types.. > Actually, I just write "CHAR" for a length of 1. > > What is wrong with using VARCHAR for your > purpose???????????????????????????? > > Simply that a commonly used database (my) does not support it. By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then while it's your business decision (and/or that of your customers), the availability of decent, free databases should make a compelling case for anyone using anything else, to migrate (and never look back) to something full-featured. It's like requiring portable C code to use the old, pre-ANSI style of function declarations, because some old C compilers might not support the ANSI style. I think Richard Stallman of the FSF takes that position, but I don't know of anyone else (although I'm sure there are exceptions). -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Dean Gibson (DB Administrator) wrote: > On 2008-02-12 16:17, Ken Johanson wrote: >> Dean Gibson (DB Administrator) wrote: >> ... >> >> I'm guessing you declare an explicit length of 1 (for portability), or >> do you "CAST (x as char)"? And one might ask in what context we'd need >> CHAR(1) on a numeric type, or else if substr/ing or left() make the >> code more readable for other data types.. >> > > Actually, I just write "CHAR" for a length of 1. On a numeric type?.. That's the quintessential part to me... > >> > What is wrong with using VARCHAR for your >> purpose???????????????????????????? >> >> Simply that a commonly used database (my) does not support it. > > By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then > while it's your business decision (and/or that of your customers), the > availability of decent, free databases should make a compelling case for > anyone using anything else, to migrate (and never look back) to > something full-featured. Yes, Mysql, and yes, it's customer driven. > > It's like requiring portable C code to use the old, pre-ANSI style of > function declarations, because some old C compilers might not support > the ANSI style. I think Richard Stallman of the FSF takes that > position, but I don't know of anyone else (although I'm sure there are > exceptions). > Point taken. This is really just a rock and hard place because I'm stuck between 3rd party products (customer API and database x^n). I'm trying to convey here that changing the behavior to a (numb AS varchar) one, practically speaking, is more benign/useful (vs now), even if that is only a non-spec workaround, and "everyone else does it" is an invalid arg. I'm much more concerned about the AS in column labels issue and some driver todos. The pre standard_conforming_strings behavior used to be the full show stopper for PG, and now I only hear smaller compatibility and ease of migration concerns (whether spec or defacto). Things are improving.
On 2008-02-12 19:39, Ken Johanson wrote: > Dean Gibson (DB Administrator) wrote: >> On 2008-02-12 16:17, Ken Johanson wrote: >>> Dean Gibson (DB Administrator) wrote: >>> ... >>> >>> I'm guessing you declare an explicit length of 1 (for portability), >>> or do you "CAST (x as char)"? And one might ask in what context we'd >>> need CHAR(1) on a numeric type, or else if substr/ing or left() make >>> the code more readable for other data types.. >>> >> >> Actually, I just write "CHAR" for a length of 1. > > On a numeric type?.. That's the quintessential part to me... No, not on a numeric type. The database stores a single byte code from a gov't DB. In a VIEW, I do a table lookup on the code and suffix an English explanation of the code. However, some of the users of the VIEW (eg, php) would like to do a SELECT based on the original value, and I use CAST( ... AS CHAR ) to get just the original code back. I use the CAST as a shorthand for SUBSTRING. I don't know if that is easier for the planner to flatten than a function call, but it's easier (for me) to read (especially if I use the PostgreSQL "::" cast extension). >>> > What is wrong with using VARCHAR for your >>> purpose???????????????????????????? >>> >>> Simply that a commonly used database (my) does not support it. >> >> By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then >> while it's your business decision (and/or that of your customers), >> the availability of decent, free databases should make a compelling >> case for anyone using anything else, to migrate (and never look back) >> to something full-featured. > Yes, Mysql, and yes, it's customer driven. > Then I don't understand. While I've never used MySQL, the MySQL web pages apparently indicate that VARCHAR has been supported since version 3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Dean Gibson (DB Administrator) wrote: >> > Then I don't understand. While I've never used MySQL, the MySQL web > pages apparently indicate that VARCHAR has been supported since version > 3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html > > Only in DDL and not the cast function, apparently. Mysql 5.1 says: select cast(123 AS varchar) or select cast(123 AS varchar(10)) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)' at line 1.
On Tue, Feb 12, 2008 at 08:39:05PM -0700, Ken Johanson wrote: > between 3rd party products (customer API and database x^n). I'm trying > to convey here that changing the behavior to a (numb AS varchar) one, > practically speaking, is more benign/useful (vs now), even if that is > only a non-spec workaround, and "everyone else does it" is an invalid > arg. No, you're trying to convey that it is more benign/useful _to you_. Others are arguing that they want to write conformant code, and don't much care what MyOccasionallyReadTheSpec does. It's a pity that SQL conformance is not better across systems, but surely the way to improve that is to reduce the number of products that are being careless, not to increase them? A
Andrew Sullivan wrote: > > No, you're trying to convey that it is more benign/useful _to you_. Others > are arguing that they want to write conformant code, and don't much care > what MyOccasionallyReadTheSpec does. It's a pity that SQL conformance is > not better across systems, but surely the way to improve that is to reduce > the number of products that are being careless, not to increase them? > Let's leave it as-is then. We'll conform to the spec, and this good since someone may want to extract the leftmost char from a base-10 number representation. Henceforth SELECT CAST(123 AS char) will and should undisputedly return '1'.
Ken Johanson wrote: > Henceforth SELECT CAST(123 AS char) will and should undisputedly return '1'. 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. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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.
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. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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
Richard Huxton <dev@archonet.com> writes:
> [ proof that cast(123 as char) actually produces varchar in mysql ]
Egad. I wonder if they think this is a feature?
regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> [ proof that cast(123 as char) actually produces varchar in mysql ] > > Egad. I wonder if they think this is a feature? Well, presumably its what all the other "convenient" (for Ken's particular problem) databases do. The only alternative I could see would be to use a varchar while casting values and then check lengths at the end before re-casting to e.g. char(6). Interestingly, it must have some lookup table mapping numeric types to lengths of varchar because a standard int gives you varchar(11) whereas an expression that pushes you beyond 32 bits gives varchar(32) and a tinyint gives you varchar(4). Clearly some thought has gone into this. -- Richard Huxton Archonet Ltd
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.
[Way behind on reading stuff - so I hope this wasn't covered later]
On Tue, 12 Feb 2008, Tom Lane wrote:
> Ken Johanson <pg-user@kensystem.com> writes:
> > For sake of interoperability (and using an API that requires String-type
> > hashtable keys), I'm trying to find a single CAST (int -> var/char)
> > syntax that works between the most databases. Only char seems to be a
> > candidate, but in 8.3 casting from an integer outputs only the first char...
>
> > Is this a bug, or would someone like to horrify me by stating something
> > like "spec says this is correct". :-)
>
> Okay: the spec says this is correct.
>
> SQL92 section 6.1 <data type> quoth
>
> <character string type> ::=
> CHARACTER [ <left paren> <length> <right paren> ]
> | CHAR [ <left paren> <length> <right paren> ]
>
> ...
>
> 4) If <length> is omitted, then a <length> of 1 is implicit.
>
> Therefore, writing just "char" is defined as equivalent to "char(1)".
>
> Also, section 6.10 <cast specification> defines an explicit cast to
> a fixed-length string type as truncating or padding to the target
> length (LTD):
>
> Case:
>
> i) If the length in characters of SV is equal to LTD, then TV
> is SV.
>
> ii) If the length in characters of SV is larger than LTD, then
> TV is the first LTD characters of SV. If any of the re-
> maining characters of SV are non-<space> characters, then a
> completion condition is raised: warning-string data, right
> truncation.
>
> iii) If the length in characters M of SV is smaller than LTD,
> then TV is SV extended on the right by LTD-M <space>s.
Are you sure that's the correct section to be using? Isn't that 6.10
General Rules 5c which is if the source type is a fixed or variable
length character string? Wouldn't the correct place for an int->char
conversion be 5a or am I misreading it?
5) If TD is fixed-length character string, then let LTD be the
length in characters of TD.
Case:
a) If SD is exact numeric, then let YP be the shortest character
string that conforms to the definition of <exact numeric
literal> in Subclause 5.3, "<literal>", whose scale is the
same as the scale of SD and whose interpreted value is the
absolute value of SV.
If SV is less than 0, then let Y be the result of
'-' | YP
Otherwise, let Y be YP.
Case:
i) If Y contains any <SQL language character> that is not
in the repertoire of TD, then an exception condition is
raised: data exception-invalid character value for cast.
ii) If the length in characters LY of Y is equal to LTD, then
TV is Y.
iii) If the length in characters LY of Y is less than LTD, then
TV is Y extended on the right by LTD-LY <space>s.
iv) Otherwise, an exception condition is raised: data exception-
string data, right truncation.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Tue, 12 Feb 2008, Tom Lane wrote:
>> Also, section 6.10 <cast specification> defines an explicit cast to
>> a fixed-length string type as truncating or padding to the target
>> length (LTD):
> Are you sure that's the correct section to be using? Isn't that 6.10
> General Rules 5c which is if the source type is a fixed or variable
> length character string? Wouldn't the correct place for an int->char
> conversion be 5a or am I misreading it?
Hm, good point, so really we ought to have a separate casting path for
numeric types to char(n). However, this section still doesn't offer
any support for the OP's desire to auto-size the result; it says
that you get an error if the result doesn't fit in the declared
length:
> iv) Otherwise, an exception condition is raised: data exception-
> string data, right truncation.
regards, tom lane
Tom Lane wrote: > Hm, good point, so really we ought to have a separate casting path for > numeric types to char(n). However, this section still doesn't offer > any support for the OP's desire to auto-size the result; it says > that you get an error if the result doesn't fit in the declared > length: > >> iv) Otherwise, an exception condition is raised: data exception- >> string data, right truncation. > I don't believe the size is being declared in the OP's (subject line) example: SELECT CAST(123 AS char)
Ken Johanson <pg-user@kensystem.com> writes:
> Tom Lane wrote:
>> Hm, good point, so really we ought to have a separate casting path for
>> numeric types to char(n). However, this section still doesn't offer
>> any support for the OP's desire to auto-size the result; it says
>> that you get an error if the result doesn't fit in the declared
>> length:
>>
>>> iv) Otherwise, an exception condition is raised: data exception-
>>> string data, right truncation.
> I don't believe the size is being declared in the OP's (subject line)
> example: SELECT CAST(123 AS char)
No, because section 6.1 still defines what "char" means, and it says
that means "char(1)".
regards, tom lane
On Sat, 16 Feb 2008, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > On Tue, 12 Feb 2008, Tom Lane wrote: > >> Also, section 6.10 <cast specification> defines an explicit cast to > >> a fixed-length string type as truncating or padding to the target > >> length (LTD): > > > Are you sure that's the correct section to be using? Isn't that 6.10 > > General Rules 5c which is if the source type is a fixed or variable > > length character string? Wouldn't the correct place for an int->char > > conversion be 5a or am I misreading it? > > Hm, good point, so really we ought to have a separate casting path for > numeric types to char(n). However, this section still doesn't offer > any support for the OP's desire to auto-size the result; it says > that you get an error if the result doesn't fit in the declared > length: Yeah. Although, IIRC, it was one of the options he mentioned as being better than getting the first character but not what he really wanted.
On Sat, 16 Feb 2008, Ken Johanson wrote: > Tom Lane wrote: > > > Hm, good point, so really we ought to have a separate casting path for > > numeric types to char(n). However, this section still doesn't offer > > any support for the OP's desire to auto-size the result; it says > > that you get an error if the result doesn't fit in the declared > > length: > > > >> iv) Otherwise, an exception condition is raised: data exception- > >> string data, right truncation. > > > > I don't believe the size is being declared in the OP's (subject line) > example: SELECT CAST(123 AS char) The other part of Tom's quotes still apply: If length is omitted then a length of 1 is implicit.