Oh! Great answer! Thats what i want to know!!! Thank you Pavel about explanation!!!
Alex Ignatov Postgres Professional: http://www.postgrespro.com Russian Postgres Company > On 04 Mar 2016, at 20:45, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > Hi > > 2016-03-04 18:29 GMT+01:00 Melvin Davidson <melvin6...@gmail.com>: >> Probably because pg_typeof() returns the OID of a COLUMN in a table def.. >> Strings literals do not have oid's. > > no this is not a reason. > > String literal has fictive "unknown" type. Real type is derivated from > context - operators, function parameters. pg_typeof has parameter of type > "any", and then no conversions from "unknown" is possible. > > I don't known why "text" type is not default for string literal, but I see > some logical relations. If we cast "unknown" to "text" early, then we will be > limited by "text" type available conversions. Now, it is possible, but years > ago, the cast between "text" type and others was disallowed. Still we require > explicit cast, and I see it as benefit. Hidden cast (implicit cast) are query > performance killers. So "unknown" type requires less explicit casting, > because there are implicit casts from this type to any type. > > I am not sure if comparation with numbers is valid. Numbers are much more > consistent class than string literals - more implicit casts over this class > is there. > > Probably this design can be enhanced, and more consistent - "text" type can > be used as fallback type. > > Regards > > Pavel > >> >> http://www.postgresql.org/docs/9.4/interactive/functions-info.html >> >> "pg_typeof returns the OID of the data type of the value that is passed to >> it. This can be helpful for troubleshooting or dynamically constructing SQL >> queries. The function is declared as returning regtype, which is an OID >> alias type (see Section 8.18); this means that it is the same as an OID for >> comparison purposes but displays as a type name. For example:" >> >> Please in the future, ALWAYS specify your PostgreSQL version and O/S, >> regardless of whether or not you thinnk it is pertinent. >> >>> On Fri, Mar 4, 2016 at 12:17 PM, Alex Ignatov <a.igna...@postgrespro.ru> >>> wrote: >>> Hello! >>> Why string literal like 'Hello world!' doesnt automagicaly cast to text >>> type? >>> >>> postgres=# select pg_typeof('Hello world'); >>> pg_typeof >>> ----------- >>> unknown >>> (1 row) >>> >>> But for example literal like 1.1 automagically cast to numeric( not float8, >>> float4, whatever) >>> postgres=# select pg_typeof(1.1); >>> pg_typeof >>> ----------- >>> numeric >>> (1 row) >>> >>> That why we cant do the following without explicit type casting: >>> postgres=# select t.c||' world' from (select 'Hello' as c) as t; >>> ERROR: failed to find conversion function from unknown to text >>> >>> but that ok: >>> postgres=# select t.c||' world' from (select 'Hello'::text as c) as t; >>> ?column? >>> ------------- >>> Hello world >>> (1 row) >>> >>> or this is ok too: >>> postgres=# select t.c::text||' world' from (select 'Hello' as c) as t; >>> ?column? >>> ------------- >>> Hello world >>> (1 row) >>> >>> Sure we can create our cast: >>> postgres=# create cast (unknown as text) with inout as implicit; >>> CREATE CAST >>> and after that we have: >>> postgres=# select t.c||' world' from (select 'Hello' as c) as t; >>> ?column? >>> ------------- >>> Hello world >>> (1 row) >>> >>> But why we don't have this type cast by default in Postgres? Is there any >>> fundamental restriction on that or there is some reasons for that? >>> >>> >>> -- >>> Alex Ignatov >>> Postgres Professional: http://www.postgrespro.com >>> The Russian Postgres Company >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> >> -- >> Melvin Davidson >> I reserve the right to fantasize. Whether or not you >> wish to share my fantasy is entirely up to you. >
