Re: [SQL] psql variable interpolation from command line
Ugh, never mind. I finally saw the reason in the 'psql' documentation. Missed it the first time. (And the second, and third, ...) -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] psql variable interpolation from command line
I'm at a loss to explain this one: $ psql -v AUTHOR="'foo'" -c 'select :AUTHOR;' ERROR: syntax error at or near ":" at character 8 $ psql -v AUTHOR="'foo'" ... # select :AUTHOR; ?column? -- foo (1 row) In other words, why won't variable interpolation work when the "-c" flag is used? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?
On Mon, Sep 27, 2004 at 19:14:09 -0500,
"D. Duccini" <[EMAIL PROTECTED]> wrote:
>
> I'm trying to create a index from a timestamp+tz field and want the index
> to be date_trunc'd down to just the date
>
> when i try to do a
>
> create idxfoo on foo (date(footime));
>
> i get a
>
> ERROR: DefineIndex: index function must be marked IMMUTABLE
>
> and it chokes on when i try to use the date_trunc() function as well
>
> create idxfoo on foo (date_trunc('day',footime));
>
> ERROR: parser: parse error at or near "'day'" at character 53
>
> Any suggestions/workarounds (other than creating additional date-only
> columns in the schema and indexing those???)
The reason this doesn't work is that the timestamp to date conversion
depends on the time zone setting. In theory you should be able to avoid
this by specifying the time zone to check the date in. I tried something
like the following which I think should work, but doesn't:
create idxfoo on foo (date(timezone('UTC',footime)));
The conversion of the timestamp stored in footime should be immutable
and then taking the date should work. I did find that date of a timestamp
without time zone is treated as immutable.
I am not sure how to check if the supplied function for converting
a timestamp with time zone to a timestamp without timezone using a
specified time zone is immutable. I think this function should be
immutable, but that it probably isn't.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?
On Fri, Oct 01, 2004 at 13:28:30 -0500, Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > I am not sure how to check if the supplied function for converting > a timestamp with time zone to a timestamp without timezone using a > specified time zone is immutable. I think this function should be > immutable, but that it probably isn't. I found that most of the various timezone functions are marked as stable instead of immutable. I think at least a couple of these should be marked as immutable and I will try reporting this as a bug. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?
Bruno Wolff III <[EMAIL PROTECTED]> writes: > I am not sure how to check if the supplied function for converting > a timestamp with time zone to a timestamp without timezone using a > specified time zone is immutable. I think this function should be > immutable, but that it probably isn't. Yup. In 7.4: regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure; provolatile - s (1 row) regression=# This is a thinko that's already been corrected for 8.0: regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure; provolatile - i (1 row) regression=# If you wanted you could just UPDATE pg_proc to correct this mistake. Another possibility is to create a function that's an IMMUTABLE wrapper around the standard function. Looking at this, I realize that date_trunc() is mismarked: the timestamptz variant is strongly dependent on the timezone setting and so should be STABLE not IMMUTABLE. Ooops. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?
"D. Duccini" <[EMAIL PROTECTED]> writes: > I think we found a way around it! > CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS > 'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ; No, you just found a way to corrupt your index. Pretending that date(timestamptz) is immutable does not make it so. The above *will* break the first time someone uses the table with a different timezone setting. What you can do safely is date(footime AT TIME ZONE 'something'), since this nails down the zone in which the date is interpreted. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
