Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
On Sat, 16 Feb 2008, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 12 Feb 2008, Tom Lane wrote: > >> Also, section 6.10 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Ken Johanson <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 12 Feb 2008, Tom Lane wrote: >> Also, section 6.10 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
[Way behind on reading stuff - so I hope this wasn't covered later] On Tue, 12 Feb 2008, Tom Lane wrote: > Ken Johanson <[EMAIL PROTECTED]> 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 quoth > > ::= > CHARACTER [] > | CHAR [] > > ... > > 4) If is omitted, then a of 1 is implicit. > > Therefore, writing just "char" is defined as equivalent to "char(1)". > > Also, section 6.10 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- 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 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 in Subclause 5.3, "", 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 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 s. iv) Otherwise, an exception condition is raised: data exception- string data, right truncation. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Tom Lane wrote: Richard Huxton <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Richard Huxton <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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/
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT CAST(123 AS char) -> 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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'. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT CAST(123 AS char) -> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Ken Johanson <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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. 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.
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Gregory Stark wrote: "Ken Johanson" <[EMAIL PROTECTED]> writes: Tom Lane wrote: SQL92 section 6.1 quoth ::= CHARACTER [] | CHAR [] ... 4) If is omitted, then a 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
"Ken Johanson" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: > >> SQL92 section 6.1 quoth >> >> ::= >> CHARACTER [] >> | CHAR [] >> >> ... >> >> 4) If is omitted, then a 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
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 <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Tom Lane wrote: SQL92 section 6.1 quoth ::= CHARACTER [] | CHAR [] ... 4) If is omitted, then a 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 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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Ken Johanson <[EMAIL PROTECTED]> 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 quoth ::= CHARACTER [] | CHAR [] ... 4) If is omitted, then a of 1 is implicit. Therefore, writing just "char" is defined as equivalent to "char(1)". Also, section 6.10 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- 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 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
