pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedempty view, replace view, column type change?
8+ messages / 4 participants
[nested] [flat]
* empty view, replace view, column type change?
@ 2005-06-23 20:03 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 8+ messages in thread
From: Markus Bertheau @ 2005-06-23 20:03 UTC (permalink / raw)
To: pgsql-sql
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>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-23 20:19 Rod Taylor <pg@rbt.ca>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 8+ messages in thread
From: Rod Taylor @ 2005-06-23 20:19 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
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;
--
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-23 20:21 Markus Bertheau <twanger@bluetwanger.de>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 8+ messages in thread
From: Markus Bertheau @ 2005-06-23 20:21 UTC (permalink / raw)
To: pgsql-sql
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>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-23 20:26 Rod Taylor <pg@rbt.ca>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 8+ messages in thread
From: Rod Taylor @ 2005-06-23 20:26 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
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.
--
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-23 20:27 Markus Bertheau <twanger@bluetwanger.de>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Markus Bertheau @ 2005-06-23 20:27 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-sql
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>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-23 22:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-06-23 22:21 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
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 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?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-24 04:32 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2005-06-24 04:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <pg@rbt.ca>; Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: empty view, replace view, column type change?
@ 2005-06-24 14:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2005-06-24 14:32 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Rod Taylor <pg@rbt.ca>; Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
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
intend
CREATE TABLE foo (ts timestamp);
to mean
CREATE 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-06-24 14:32 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 20:03 empty view, replace view, column type change? Markus Bertheau <twanger@bluetwanger.de>
2005-06-23 20:19 ` Rod Taylor <pg@rbt.ca>
2005-06-23 20:27 ` Markus Bertheau <twanger@bluetwanger.de>
2005-06-23 20:26 ` Rod Taylor <pg@rbt.ca>
2005-06-23 22:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-24 04:32 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-06-24 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-23 20:21 ` Markus Bertheau <twanger@bluetwanger.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox