Thread: empty view, replace view, column type change?
Hi, it seems to me that the following should work but it fails: CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; CREATE TABLE link (ov TIMESTAMP); CREATE OR REPLACE VIEW co AS SELECT ov FROM link; Markus -- Markus Bertheau <twanger@bluetwanger.de>
Dnia 23-06-2005, czw o godzinie 22:03 +0200, Markus Bertheau napisał(a): > Hi, > > it seems to me that the following should work but it fails: > > CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; > CREATE TABLE link (ov TIMESTAMP); > CREATE OR REPLACE VIEW co AS SELECT ov FROM link; Erm, the error message is something to the effect of ERROR: can't change type of view column "ov" -- Markus Bertheau <twanger@bluetwanger.de>
On Thu, 2005-06-23 at 22:03 +0200, Markus Bertheau wrote: > Hi, > > it seems to me that the following should work but it fails: > > CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; > CREATE TABLE link (ov TIMESTAMP); > CREATE OR REPLACE VIEW co AS SELECT ov FROM link; The data types are different, as one has the timestamp to (6) decimal places after seconds. development=# CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; CREATE VIEW development=# CREATE TABLE link (ov TIMESTAMP); CREATE TABLE development=# CREATE OR REPLACE VIEW cod AS SELECT ov FROM link; CREATE VIEW development=# \d co View "rbt.co"Column | Type | Modifiers --------+--------------------------------+-----------ov | timestamp(6) without time zone | View definition:SELECT 'now'::text::timestamp(6) without time zone AS ov WHERE false; development=# \d cod View "rbt.cod"Column | Type | Modifiers --------+-----------------------------+-----------ov | timestamp without time zone | View definition:SELECT link.ov FROM link; --
Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisał(a): > The data types are different, as one has the timestamp to (6) decimal > places after seconds. That's strange. I explicitly specified ::TIMESTAMP on both the view and the table. Is that not unambiguous? Markus -- Markus Bertheau <twanger@bluetwanger.de>
On Thu, 2005-06-23 at 22:27 +0200, Markus Bertheau wrote: > Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisał(a): > > > The data types are different, as one has the timestamp to (6) decimal > > places after seconds. > > That's strange. I explicitly specified ::TIMESTAMP on both the view and > the table. Is that not unambiguous? LOCALTIMESTAMP is probably more specific, so it folds the length in. If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). Cast them both to timestamp(N) and what you're trying to do should work. --
Rod Taylor <pg@rbt.ca> writes:
> On Thu, 2005-06-23 at 22:27 +0200, Markus Bertheau wrote:
>> Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisa�(a):
>>> The data types are different, as one has the timestamp to (6) decimal
>>> places after seconds.
>>
>> That's strange. I explicitly specified ::TIMESTAMP on both the view and
>> the table. Is that not unambiguous?
> LOCALTIMESTAMP is probably more specific, so it folds the length in.
> If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6).
Yeah, this is exactly what's happening --- if you look at gram.y,
LOCALTIMESTAMP is expanded to "'now'::text::timestamp(6)". I am
unconvinced that the (6) is a very good idea though. The code says
/* SQL99 mandates a default precision of 6 for timestamp. * Also, that is about
asprecise as we will get since * we are using a microsecond time interface. * -
thomas2001-12-07 */ d->typmod = 6;
but it doesn't seem to me to follow from what the spec says that we need
to explicitly cast the result of now() to six places. As long as it's
coming from gettimeofday it can't have more than 6 places anyway, and so
we might as well save the extra coercion step. (The parser *will* tack
on a separate coercion function call when presented with this parse
tree.)
In short, I'm inclined to remove the above-quoted lines, and similarly
for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts?
regards, tom lane
Tom Lane wrote: > > If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). > > Yeah, this is exactly what's happening --- if you look at gram.y, > LOCALTIMESTAMP is expanded to "'now'::text::timestamp(6)". I am > unconvinced that the (6) is a very good idea though. The code says > > /* SQL99 mandates a default precision of 6 for timestamp. > * Also, that is about as precise as we will get since > * we are using a microsecond time interface. > * - thomas 2001-12-07 > */ > d->typmod = 6; > > but it doesn't seem to me to follow from what the spec says that we need > to explicitly cast the result of now() to six places. As long as it's > coming from gettimeofday it can't have more than 6 places anyway, and so > we might as well save the extra coercion step. (The parser *will* tack > on a separate coercion function call when presented with this parse > tree.) > > In short, I'm inclined to remove the above-quoted lines, and similarly > for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? Agreed. That "6" bothered me too when I was cleaning up the timestamp code a while back. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> but it doesn't seem to me to follow from what the spec says that we need
>> to explicitly cast the result of now() to six places. As long as it's
>> coming from gettimeofday it can't have more than 6 places anyway, and so
>> we might as well save the extra coercion step. (The parser *will* tack
>> on a separate coercion function call when presented with this parse
>> tree.)
>>
>> In short, I'm inclined to remove the above-quoted lines, and similarly
>> for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts?
> Agreed. That "6" bothered me too when I was cleaning up the timestamp
> code a while back.
Done. On re-reading the spec, I see that the default of six places is
intended to apply to ALL occurrences of timestamp --- that is, they
intendCREATE TABLE foo (ts timestamp);
to meanCREATE TABLE foo (ts timestamp(6));
We have deliberately decided not to do that, on a fairly consistent
basis --- numeric and varchar don't have any fixed precision limit
either if you don't specify. So these functions were out of step
with the rest of the system, and removing their explicit precision
limit is indeed a sane way of improving consistency.
regards, tom lane