Thread: minor view creation weirdness
Is this a bug? (using CVS code from yesterday) nconway=# create view baz (a,b) as select 'hello', 'world'; WARNING: column "a" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "b" has type "unknown" DETAIL: Proceeding with relation creation anyway. CREATE VIEW nconway=# -Neil
Neil Conway <neilc@samurai.com> writes: > Is this a bug? > > (using CVS code from yesterday) > > nconway=# create view baz (a,b) as select 'hello', 'world'; > WARNING: column "a" has type "unknown" > DETAIL: Proceeding with relation creation anyway. > WARNING: column "b" has type "unknown" > DETAIL: Proceeding with relation creation anyway. > CREATE VIEW > nconway=# 7.3 does the same thing actually. I don't know what that means though. -- greg
On Thu, 2003-10-02 at 08:40, Greg Stark wrote: > Neil Conway <neilc@samurai.com> writes: > > > Is this a bug? > > > > (using CVS code from yesterday) > > > > nconway=# create view baz (a,b) as select 'hello', 'world'; > > WARNING: column "a" has type "unknown" > > DETAIL: Proceeding with relation creation anyway. > > WARNING: column "b" has type "unknown" > > DETAIL: Proceeding with relation creation anyway. > > CREATE VIEW > > nconway=# > > 7.3 does the same thing actually. I don't know what that means though. junk=# \d baz View "public.baz"Column | Type | Modifiers --------+-----------+-----------a | "unknown" |b | "unknown" | View definition:SELECT 'hello' AS a, 'world' AS b; There is no table behind the view, so there is no way for PostgreSQL to derive the column types of a and b. A quoted string (as supplied in the view definition) could be one of text, varchar, char, date, time, timestamp, cidr and so on. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Set your affection on things above, not on things on the earth." Colossians 3:2
Neil Conway <neilc@samurai.com> writes:
> Is this a bug?
> nconway=# create view baz (a,b) as select 'hello', 'world';
> WARNING: column "a" has type "unknown"
> DETAIL: Proceeding with relation creation anyway.
It's always done that, although the spelling of the notice has
varied over the years.
These days we tend to force "unknown" to become "text" if a specific
data type is really required, and I suppose a case could be made that
CREATE VIEW should do that too. But the consequences of guessing wrong
are probably worse here than elsewhere, since you can't really change
the view column type short of dropping and recreating the view.
I'd almost argue that we should change this message to an error:
ERROR: column "a" has type "unknown"HINT: Explicitly cast the string literal to some specific type.
regards, tom lane
On Thu, 2003-10-02 at 10:16, Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > > nconway=# create view baz (a,b) as select 'hello', 'world'; > > WARNING: column "a" has type "unknown" > > DETAIL: Proceeding with relation creation anyway. > > It's always done that, although the spelling of the notice has > varied over the years. > I'd almost argue that we should change this message to an error I agree. -Neil
Neil Conway <neilc@samurai.com> writes:
> On Thu, 2003-10-02 at 10:16, Tom Lane wrote:
>> Neil Conway <neilc@samurai.com> writes:
>>> nconway=# create view baz (a,b) as select 'hello', 'world';
>>> WARNING: column "a" has type "unknown"
>>> DETAIL: Proceeding with relation creation anyway.
>> I'd almost argue that we should change this message to an error
> I agree.
Motion proposed and seconded; any objections out there?
regards, tom lane
>>>nconway=# create view baz (a,b) as select 'hello', 'world'; >>>WARNING: column "a" has type "unknown" >>>DETAIL: Proceeding with relation creation anyway. >> >>It's always done that, although the spelling of the notice has >>varied over the years. > > >>I'd almost argue that we should change this message to an error > > > I agree. Except that it would totally break backwards-compatibility? Or will 'unknown' fields in views be dumped with explicit casts? Chris
Tom Lane <tgl@sss.pgh.pa.us> writes: > >> I'd almost argue that we should change this message to an error > > > I agree. > > Motion proposed and seconded; any objections out there? Uhm, doesn't the spec have anything to say about this? I mean, the view sure looks like standard SQL on its face. In any case, I would sure think there was something strange about a query working fine as a select but not working in a view: slo=> select 'foo' as a,'bar' as b; a | b -----+-----foo | bar (1 row) slo=> create view x as select 'foo' as a,'bar' as b; WARNING: Attribute "a" has an unknown typeProceeding with relation creation anyway WARNING: Attribute "b" has an unknown typeProceeding with relation creation anyway CREATE VIEW slo=> select * from x; a | b -----+-----foo | bar (1 row) -- greg
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>>> I'd almost argue that we should change this message to an error
>>
>> I agree.
> Except that it would totally break backwards-compatibility?
Well, that's a possible problem. How many such views do you think are
out there, given the existence of the warning?
regards, tom lane
On Fri, 2003-10-03 at 00:50, Greg Stark wrote:
>
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > >> I'd almost argue that we should change this message to an error
> >
> > > I agree.
> >
> > Motion proposed and seconded; any objections out there?
>
> Uhm, doesn't the spec have anything to say about this?
> I mean, the view sure looks like standard SQL on its face.
>
> In any case, I would sure think there was something strange about a query
> working fine as a select but not working in a view:
>
> slo=> select 'foo' as a,'bar' as b;
> a | b
> -----+-----
> foo | bar
> (1 row)
>
> slo=> create view x as select 'foo' as a,'bar' as b;
> WARNING: Attribute "a" has an unknown type
> Proceeding with relation creation anyway
> WARNING: Attribute "b" has an unknown type
> Proceeding with relation creation anyway
> CREATE VIEW
>
> slo=> select * from x;
> a | b
> -----+-----
> foo | bar
> (1 row)
>
or the create table case:
rms=# create table x as select 'foo' as a, 'bar' as b;
WARNING: 42P16: attribute "a" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
LOCATION: CheckAttributeType, heap.c:427
WARNING: 42P16: attribute "b" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
LOCATION: CheckAttributeType, heap.c:427
WARNING: attribute "a" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
WARNING: attribute "b" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
SELECT
rms=# select * from x; a | b
-----+-----foo | bar
(1 row)
rms=# \d x Table "public.x"Column | Type | Modifiers
--------+-----------+-----------a | "unknown" | b | "unknown" |
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Greg Stark <gsstark@mit.edu> writes:
> Uhm, doesn't the spec have anything to say about this?
> I mean, the view sure looks like standard SQL on its face.
Well, you might read the spec as requiring the view column to have
datatype CHAR(n) where n is the length of the unknown literal.
I see in SQL92:
9) The data type of a <character string literal> is fixed-length character string. The length of a
<characterstring literal> is the number of <character representation>s that it contains. ...
Note: <character string literal>s are allowed to be zero-length strings (i.e., to contain no characters)
eventhough it is not permitted to declare a <data type> that is CHARACTER with <length> zero.
The NOTE would appear to imply that
CREATE VIEW v AS SELECT '';
is illegal, which is not a conclusion I care to follow blindly. In any
case, in Postgres I would think we would want to take the type as "text"
not "CHAR(n)", spec or no spec.
regards, tom lane