Обсуждение: [BUGS] BUG #14800: substring produces different results with similar types
[BUGS] BUG #14800: substring produces different results with similar types
От
kostin.artem@gmail.com
Дата:
The following bug has been logged on the website: Bug reference: 14800 Logged by: Артём Костин Email address: kostin.artem@gmail.com PostgreSQL version: 9.4.11 Operating system: Win 10 Description: These two commands produce different results with similar types select substring(cast(' 2345 ' as character(16)), 1, 7) || '?',substring(cast(' 2345 ' as varchar(16)),1, 7) || '?'; " 2345?";" 2345 ?" Should it work in this way or not? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14800: substring produces different results withsimilar types
От
Francisco Olarte
Дата:
Kostin: On Wed, Sep 6, 2017 at 3:21 PM, <kostin.artem@gmail.com> wrote: > These two commands produce different results with similar types > select substring(cast(' 2345 ' as character(16)), 1, 7) || '?', > substring(cast(' 2345 ' as varchar(16)), 1, 7) || '?'; > > " 2345?";" 2345 ?" > > Should it work in this way or not? If you are not sure whether something is a bug, you should try asking in the general list, to avoid noise. Anyway, you may notice char() discards trailing blanks, varchar does not: n=> select cast(' 2345 ' as character(16)) || '?';?column? ---------- 2345? (1 row) Plain char is STORED space padded and TRUNCATED if too long ( standard required IIRC ), and trailing spaces are disregarded in many places, such as when concatenating. Francisco Olarte. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Francisco Olarte <folarte@peoplecall.com> writes: > Anyway, you may notice char() discards trailing blanks, varchar does not: More precisely, converting from char(n) to varchar or text discards trailing blanks. Since both substring() and the || operator take text argument types, an implicit coercion to text is happening in these examples ... and that's where the blanks went. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
But documentation says different. So this behaviour is unxpected.
6 сент. 2017 г. 5:51 PM пользователь "Tom Lane" <tgl@sss.pgh.pa.us> написал:
Francisco Olarte <folarte@peoplecall.com> writes:
> Anyway, you may notice char() discards trailing blanks, varchar does not:
More precisely, converting from char(n) to varchar or text discards
trailing blanks. Since both substring() and the || operator take
text argument types, an implicit coercion to text is happening in
these examples ... and that's where the blanks went.
regards, tom lane
Артём Костин <kostin.artem@gmail.com> writes: > But documentation says different. So this behaviour is unxpected. No it doesn't. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14800: substring produces different results withsimilar types
От
Francisco Olarte
Дата:
Tom: On Wed, Sep 6, 2017 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Francisco Olarte <folarte@peoplecall.com> writes: >> Anyway, you may notice char() discards trailing blanks, varchar does not: > More precisely, converting from char(n) to varchar or text discards > trailing blanks. Since both substring() and the || operator take > text argument types, an implicit coercion to text is happening in > these examples ... and that's where the blanks went. I was suspecting that, but https://www.postgresql.org/docs/9.6/static/functions-string.html documents them as "string || string", returns text, and "substring(string {several variants})", returns text and trying to look where the conversion to text happened ( i.e., in the arguments, or after aplying overloaded variants ) seemed a bit extreme. Not surprising, anyway, of the space chopping, having worked with punched cards I'm used to it. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14800: substring produces different results withsimilar types
От
Francisco Olarte
Дата:
Please, do not top-quote, it is hard to read. On Wed, Sep 6, 2017 at 5:22 PM, Артём Костин <kostin.artem@gmail.com> wrote: > But documentation says different. So this behaviour is unxpected. If you think so you may post the relevant part of the documentation, so we can see if it really does or try to tell you where we think you are reading it wrong. This hand waving approach, "docs says otherwise", won't help. Francisco Olarte. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
I'm sorry for this approach.
The main point is that behaviour are not applied to all character types described in docs. You can see it in my example.
For the second point, please, check chapter 8.3 for this
"Trailing spaces are 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, and when using pattern matching, that is LIKE and regular expressions."
According to the last sentence I expect spaces after characters. It would be strange first convert character to string and when string to text type, don't you think so?
6 сент. 2017 г. 7:46 PM пользователь "Francisco Olarte" <folarte@peoplecall.com> написал:
Please, do not top-quote, it is hard to read.
On Wed, Sep 6, 2017 at 5:22 PM, Артём Костин <kostin.artem@gmail.com> wrote:
> But documentation says different. So this behaviour is unxpected.
If you think so you may post the relevant part of the documentation,
so we can see if it really does or try to tell you where we think you
are reading it wrong. This hand waving approach, "docs says
otherwise", won't help.
Francisco Olarte.
Re: [BUGS] BUG #14800: substring produces different results withsimilar types
От
"David G. Johnston"
Дата:
I'm sorry for this approach.The main point is that behaviour are not applied to all character types described in docs. You can see it in my example.
There is only one "character type" in PostgreSQL, it is spelled "character" (char is a valid alias).
For the second point, please, check chapter 8.3 for this"Trailing spaces are 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, and when using pattern matching, that is LIKE and regular expressions."
IOW, The "character" data type is one of three "string-class" types in the PostgreSQL system. "character" has no concept of "semantically significant trailing whitespace". Therefore when converting from "character" to one of the other two "string-class" types (varchar, text) you will never end up with a resultant value with trailing whitespace.
David J.
Re: [BUGS] BUG #14800: substring produces different results withsimilar types
От
Francisco Olarte
Дата:
On Wed, Sep 6, 2017 at 7:10 PM, Артём Костин <kostin.artem@gmail.com> wrote: > I'm sorry for this approach. If it is for the top-quoting approach, just correct it. > The main point is that behaviour are not applied to all character types > described in docs. You can see it in my example. It may, but that's not what we were discussing. Or may be. As I said, your quoting style makes it extremely difficult to know what we are replying to. > For the second point, please, check chapter 8.3 for this > "Trailing spaces are 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, and when using pattern matching, that > is LIKE and regular expressions." > According to the last sentence I expect spaces after characters. It would be > strange first convert character to string and when string to text type, > don't you think so? It may, but as TL said the functions ( and operators, which are just syntatic sugar for a function call ) work on text. You may interpreted the sentence as "substring and concatenation do not convert as they are not enumerated there". But, OTOH, char(n) fields work as people caoming from punched cards and fixed lentght records are used to. In this time you had to space-pad ( among other things space was no-punch, no print, so you saved ink and increased structural integrity by space-padding instead of usaing any other char ) and every operation right-trimmed before aplying, as you had no var-length fields. This is why, when we got access to var-length fields, used them everywhere to avoid this kind of surprises. I've been avoiding fixed char fields since I can remember. FOS -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs