datatype of constant is not propagated into aggregate query

Pavel Stehule wrote:

create table t1(d date, n integer);

postgres=# insert into t1 select '2001-01-01', 1 from
generate_series(1,3);
INSERT 0 3

but

postgres=# insert into t1 select distinct '2001-01-01', 1 from
generate_series(1,3);
ERROR: column "d" is of type date but expression is of type text

This has been discussed many times before. If you use a date
literal, you are fine. For example, this does work:

insert into t1 select distinct date '2001-01-01', 1
from generate_series(1,3);

HINT: You will need to rewrite or cast the expression.

The hint is even on point.

In PostgreSQL a quoted literal is taken as type "unknown" and it can
often be coerced to the right type based on its usage. The reason
the first example works is that the literal of unknown type is being
assigned to a date column in the insert. In the second example it is
being used for DISTINCT, and we don't look deeper to see what is
later done with that later. Type matters for DISTINCT, because
(depending locale) you might want '2011-12-31' and '12/31/2011' to be
taken as identical values. In the absence of clues as to what type
to use, PostgreSQL defaults to text, and you can't assign a text
value to the date column (without a cast).

Arguably this could be improved, but so far nobody has figured out
anything better. This is working as intended.

-Kevin

Pavel Stehule <pavel.stehule@gmail.com> writes:

postgres=# insert into t1 select distinct '2001-01-01', 1 from
generate_series(1,3);
ERROR: column "d" is of type date but expression is of type text
LINE 1: insert into t1 select distinct '2001-01-01', 1 from generate...
^
HINT: You will need to rewrite or cast the expression.

The DISTINCT forces the sub-SELECT to decide on a type for the constant
(so that it can pick a semantics for DISTINCT-ing). And it chooses text
by default. This is maybe not terribly convenient, but I'm not sure it
would be a good idea at all for a surrounding INSERT to change the
semantics of a SELECT. Even discounting the implementation
difficulties, I don't think that'd satisfy the POLA.

(We do have a hack for adjusting the type if what the sub-select returns
is still of type UNKNOWN; that's safe because it implies that the
sub-select does not care about the type of the result column.)

regards, tom lane

Hello

2012/3/11 Kevin Grittner <Kevin.Grittner@wicourts.gov>:

Pavel Stehule  wrote:

create table t1(d date, n integer);

postgres=# insert into t1 select '2001-01-01', 1 from
generate_series(1,3);
INSERT 0 3

but

postgres=# insert into t1 select distinct '2001-01-01', 1 from
generate_series(1,3);
ERROR: column "d" is of type date but expression is of type text

This has been discussed many times before.  If you use a date
literal, you are fine.  For example, this does work:

insert into t1 select distinct date '2001-01-01', 1
 from generate_series(1,3);

HINT: You will need to rewrite or cast the expression.

The hint is even on point.

In PostgreSQL a quoted literal is taken as type "unknown" and it can
often be coerced to the right type based on its usage.  The reason
the first example works is that the literal of unknown type is being
assigned to a date column in the insert.  In the second example it is
being used for DISTINCT, and we don't look deeper to see what is
later done with that later.  Type matters for DISTINCT, because
(depending locale) you might want '2011-12-31' and '12/31/2011' to be
taken as identical values.  In the absence of clues as to what type
to use, PostgreSQL defaults to text, and you can't assign a text
value to the date column (without a cast).

it doesn't work with enums where must not be any dependency on locale

postgres=# create type e as enum('A','B');
CREATE TYPE
postgres=# create table hh (_e e);
CREATE TABLE
postgres=# insert into hh select 'A';
INSERT 0 1
postgres=# insert into hh select distinct 'A';
ERROR: column "_e" is of type e but expression is of type text
LINE 1: insert into hh select distinct 'A';
^
HINT: You will need to rewrite or cast the expression.

Arguably this could be improved, but so far nobody has figured out
anything better.  This is working as intended.

ook

Regards

Pavel

2012/3/11 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

postgres=# insert into t1 select distinct '2001-01-01', 1 from
generate_series(1,3);
ERROR:  column "d" is of type date but expression is of type text
LINE 1: insert into t1 select distinct '2001-01-01', 1 from generate...
                                       ^
HINT:  You will need to rewrite or cast the expression.

The DISTINCT forces the sub-SELECT to decide on a type for the constant
(so that it can pick a semantics for DISTINCT-ing).  And it chooses text
by default.  This is maybe not terribly convenient, but I'm not sure it
would be a good idea at all for a surrounding INSERT to change the
semantics of a SELECT.  Even discounting the implementation
difficulties, I don't think that'd satisfy the POLA.

(We do have a hack for adjusting the type if what the sub-select returns
is still of type UNKNOWN; that's safe because it implies that the
sub-select does not care about the type of the result column.)

in this case the constant is forwarded to result without any change,
so it can be UNKNOWN.

Pavel