String literal doesn't autocast to text type

Probably because pg_typeof() returns the OID of a COLUMN in a table def..
Strings literals do not have oid's.

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
<http://www.postgresql.org/docs/9.4/interactive/datatype-oid.html&gt;); 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.ignatov@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.

Hi

2016-03-04 18:29 GMT+01:00 Melvin Davidson <melvin6925@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

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

It would be nice if you'd follow the list convention of bottom-posting.
Not doing that is much more annoying than the omission of version and o/s -
especially the later since PostgreSQL purports to be generally o/s agnostic.

On Fri, Mar 4, 2016 at 12:17 PM, Alex Ignatov <a.ignatov@postgrespro.ru>

wrote:

Hello!
Why string literal like 'Hello world!' doesnt automagicaly cast to text
type?

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?

On Friday, March 4, 2016, Melvin Davidson <melvin6925@gmail.com> wrote:

Probably because pg_typeof() returns the OID of a COLUMN in a table def..
Strings literals do not have oid's.

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
<http://www.postgresql.org/docs/9.4/interactive/datatype-oid.html&gt;); this
means that it is the same as an OID for comparison purposes but displays as
a type name. For example:"

While true this doesn't actually answer the question - that being what's
the motiviation for not implicitly casting away from unknown. I suspect
that the main reason is that in an extensible system like PostgreSQL it is
deemed risky to have too many implicit casts. Having one from unknown
required the system to make decisions that could result in unexpected
results that would be hard to catch without careful review of queries and
results. It is an issue of some contention in the community but so far no
one has convinced the committees to change how this works.

Please in the future, ALWAYS specify your PostgreSQL version and O/S,
regardless of whether or not you thinnk it is pertinent.

But don't feel too bad if you forget...

David J.

Alex Ignatov <a.ignatov@postgrespro.ru> writes:

Why string literal like 'Hello world!' doesnt automagicaly cast to text
type?

Because it's not necessarily a string. It might be meant to be point,
or json, or any number of other types.

Sure we can create our cast:
postgres=# create cast (unknown as text) with inout as implicit;
CREATE CAST

That's a seriously bad idea; it will have all sorts of corner-case
effects that you aren't expecting.

There has been some talk of forcing unknown to text in the output
columns of a sub-SELECT, which would fix the example you show with
a lot less risk of side-effects elsewhere. But it's not exactly
trivial because of interactions with INSERT ... SELECT.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04 Mar 2016, at 20:59, David G. Johnston <david.g.johnston@gmail.com> wrote:

It would be nice if you'd follow the list convention of bottom-posting. Not doing that is much more annoying than the omission of version and o/s - especially the later since PostgreSQL purports to be generally o/s agnostic.

On Fri, Mar 4, 2016 at 12:17 PM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
Hello!
Why string literal like 'Hello world!' doesnt automagicaly cast to text type?

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?

On Friday, March 4, 2016, Melvin Davidson <melvin6925@gmail.com> wrote:
Probably because pg_typeof() returns the OID of a COLUMN in a table def.. Strings literals do not have oid's.

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:"

While true this doesn't actually answer the question - that being what's the motiviation for not implicitly casting away from unknown. I suspect that the main reason is that in an extensible system like PostgreSQL it is deemed risky to have too many implicit casts. Having one from unknown required the system to make decisions that could result in unexpected results that would be hard to catch without careful review of queries and results. It is an issue of some contention in the community but so far no one has convinced the committees to change how this works.

Please in the future, ALWAYS specify your PostgreSQL version and O/S, regardless of whether or not you thinnk it is pertinent.

But don't feel too bad if you forget...

David J.

Oops! Sorry about top posting. My phone from 21th century doesnt handle this good but I got you about it!
Deeply sorry once again!

On 04 Mar 2016, at 21:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alex Ignatov <a.ignatov@postgrespro.ru> writes:

Why string literal like 'Hello world!' doesnt automagicaly cast to text
type?

Because it's not necessarily a string. It might be meant to be point,
or json, or any number of other types.

Sure we can create our cast:
postgres=# create cast (unknown as text) with inout as implicit;
CREATE CAST

That's a seriously bad idea; it will have all sorts of corner-case
effects that you aren't expecting.

There has been some talk of forcing unknown to text in the output
columns of a sub-SELECT, which would fix the example you show with
a lot less risk of side-effects elsewhere. But it's not exactly
trivial because of interactions with INSERT ... SELECT.

regards, tom lane

Oh! Thank you , Tom!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general