Обсуждение: chr(3) and 3::text

Hi,

Why is chr(3) is different than 3::text ?

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 28, 2020

OK,

And what is 3::text ?

 

From: gmail Vladimir Koković <vladimir.kokovic@gmail.com>
Sent: Saturday, March 28, 2020 6:22 AM
To: pgsql-novice@postgresql.org
Subject: chr(3) and 3::text

 

Hi,

Why is chr(3) is different than 3::text ?

 

[Ken Benson] Chr(3) – means return the character that has the ASCII code of “3” – which is a control character.

https://w3resource.com/PostgreSQL/chr-function.php

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 28, 2020

 

 

Ken Benson | ken @ infowerks-dot-com

 

 

On Sat, 28 Mar 2020 14:39:09 +0000
Ken Benson <Ken@infowerks.com> wrote:

From my console, simply "3".

Jean-Yves

> And what is 3::text ?

David,

Sorry for copy/paste error:replace(Filler,3::text,'') WRONG !

Character 0x03 is valid in ASCII and UTF-8 character sets.

Main question is, why is 3::text == '3' ?

On Sat, Mar 28, 2020 at 6:21 AM gmail Vladimir Koković <vladimir.kokovic@gmail.com> wrote:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

"Wrong" is not a helpful term unless you describe what you are expecting and how the result you got is different.  In particular just reading what you've typed the "wrong" one fails because you forgot the comma between "Fillter" and "3::text".  But I suspect you had a different unsaid expectation here.

In the future you should consider writing self-contained expressions, ideally ones that don't use physical tables if the problem permits.

David J.

The convention here is to bottom or inline post, trimming unnecessary content.

Its that way because :: is defined to make it that way. 

 

3 has a type of integer

'3' has a type of text

::<type> is the PostgreSQL type casting operator that casts its left to to <type> (see the documentation I linked to)

So:

integer::text == text

cast(integer as text) == text

is the SQL Standard equivalent expression

David J.

integer::text == text, but which character 0x03 or 0x33 ?

Text should contain characters.

Characters is from character set.

Character 0x03 is valid in ASCII and UTF-8 character sets.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Mar 28, 2020 at 8:09 AM gmail Vladimir Koković <
> vladimir.kokovic@gmail.com> wrote:
>> Main question is, why is 3::text == '3' ?

> Its that way because :: is defined to make it that way.

More concretely: for most types, casting to text is defined to produce the
textual representation of the value.  An unadorned literal 3 starts life
as type integer, so what you've got here is an integer-to-text cast,
and that's going to convert the integer the same way it would be output.

            regards, tom lane