Re: tsearch comments

> Oleg: Can you search your memory why txt2txtidx possibly should not be
> marked as "iscachable"?

Well I now see where the problem is, the function does not always have to
return the same if the arguments are the same (the data might have changed
and there may be more or less matching entries). Any hints how to overcome
this?

Regards,
Bjoern

On Wed, 22 Jan 2003, [iso-8859-1] BjЖrn Metzdorf wrote:

> > Oleg: Can you search your memory why txt2txtidx possibly should not be
> > marked as "iscachable"?
>
> Well I now see where the problem is, the function does not always have to
> return the same if the arguments are the same (the data might have changed
> and there may be more or less matching entries). Any hints how to overcome
> this?

No way, Bjoern. Think about stemming, for example.

>
> Regards,
> Bjoern
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

> > Well I now see where the problem is, the function does not always have
to
> > return the same if the arguments are the same (the data might have
changed
> > and there may be more or less matching entries). Any hints how to
overcome
> > this?
>
> No way, Bjoern. Think about stemming, for example.

Do index functions always have to be marked "iscachable" ?

Btw, the error message was confusing. It said that the function has to be
marked "isImmutable", but there is no such attribute, instead "iscachable"
had to be used. This seems to be a bug.

Regards,
Bjoern

> Do index functions always have to be marked "iscachable" ?
>
> Btw, the error message was confusing. It said that the function has to be
> marked "isImmutable", but there is no such attribute, instead "iscachable"
> had to be used. This seems to be a bug.

The manual says:

"All functions and operators used in an index definition must be immutable,
that is, their results must depend only on their input arguments and never
on any outside influence (such as the contents of another table or the
current time). This restriction ensures that the behavior of the index is
well-defined. To use a user-defined function in an index, remember to mark
the function immutable when you create it."

Well, in the tsearch case the results don't depend on any influence outside
of the function. The stemmer is integrated in the function and we don't use
the current time nor another table. So it should be safe to mark that
function "iscachable" or when it is fixed "isimmutable", am I right?

Regards,
Bjoern

Bjorn,

you'll get problem with your approach not to create additional columns
if index will not used ! In our approach sequential scan will use
txtidx column. I don't understand what error you're comment.
Probably I miss something. We are working on new version of tsearch
and alpha version will be available next week. Are you sure
'iscacheability' should be configureable somehow ? Teodor, what do
you think ?

Oleg

On Thu, 23 Jan 2003, Bjorn Metzdorf wrote:

> > Do index functions always have to be marked "iscachable" ?
> >
> > Btw, the error message was confusing. It said that the function has to be
> > marked "isImmutable", but there is no such attribute, instead "iscachable"
> > had to be used. This seems to be a bug.
>
> The manual says:
>
> "All functions and operators used in an index definition must be immutable,
> that is, their results must depend only on their input arguments and never
> on any outside influence (such as the contents of another table or the
> current time). This restriction ensures that the behavior of the index is
> well-defined. To use a user-defined function in an index, remember to mark
> the function immutable when you create it."
>
> Well, in the tsearch case the results don't depend on any influence outside
> of the function. The stemmer is integrated in the function and we don't use
> the current time nor another table. So it should be safe to mark that
> function "iscachable" or when it is fixed "isimmutable", am I right?
>
> Regards,
> Bjoern
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

> txtidx column. I don't understand what error you're comment.

I think "iscachable" and "isimmutable" are mixed up. "CREATE FUNTION" does
not accept "isimmutable".

> Probably I miss something. We are working on new version of tsearch
> and alpha version will be available next week. Are you sure

Great news! Any comments on the new version? Snowball support? txt2txtidx
without morphology?

Regards,
Bjoern

> Yes, I don't see that problem either. The locale and the configuration of
> dictionaries do not change while running the postmaster, do they?
I forgot parser too.

Parser and dictionaries will be changed after gmake install (contrib/tsearch)
:), without restarting postmaster.

In common case, we can't set iscachable option to txt2txtidx, but if you know
what you do you can set it. May be
some comments must be included in README.

Björn Metzdorf wrote:
>>txtidx column. I don't understand what error you're comment.
>
>
> I think "iscachable" and "isimmutable" are mixed up. "CREATE FUNTION" does
> not accept "isimmutable".
>
>
>>Probably I miss something. We are working on new version of tsearch
>>and alpha version will be available next week. Are you sure
>
>
> Great news! Any comments on the new version? Snowball support? txt2txtidx
> without morphology?
Snowball, ISpell....
SQL-level for configure parsers and dictionaries.
SQL-level for configure map (which dictionary for each type of lexem).
Just wait :)

--
Teodor Sigaev
teodor(at)stack(dot)net

> > Yes, I don't see that problem either. The locale and the configuration
of
> > dictionaries do not change while running the postmaster, do they?
> I forgot parser too.
>
> Parser and dictionaries will be changed after gmake install
(contrib/tsearch)
> :), without restarting postmaster.
>
> In common case, we can't set iscachable option to txt2txtidx, but if you
know
> what you do you can set it. May be
> some comments must be included in README.

Ok, but then it should be sufficient to recreate the txt2txtidx function
and/or the index after a change of parser and dictionaries. So generally
spoken, the index function approach to tsearch works, if you take care of
that. I gladly recreate the function and/or index from time to time, if I
can do without that additional column, bloated dump and slow trigger.

This is very good news, as this is a very easy approach to having an easy to
use fulltextsearch in postgresql.

> > Great news! Any comments on the new version? Snowball support?
txt2txtidx
> > without morphology?
> Snowball, ISpell....
> SQL-level for configure parsers and dictionaries.
> SQL-level for configure map (which dictionary for each type of lexem).
> Just wait :)

Wow, sounds great!

Regards,
Bjoern

As someone who is just getting started with PostygreSQL from years working
with MySQL, it appears that the timestamp data type does not behave in the
way it did with MySQL. I got used to just defining a column as a timestamp
and letting the database throw the latest time stamp in there whenever a row
was updated. Is there anything simular in PosgreSQL? How can I accomplish
something simular inside the database, or am I stuck populating the field in
some manner as in the following example

update blah blah blah timestamp = NOW()

Thanks,

Alan

> As someone who is just getting started with PostygreSQL from years working
> with MySQL, it appears that the timestamp data type does not behave in the
> way it did with MySQL. I got used to just defining a column as a timestamp
> and letting the database throw the latest time stamp in there whenever a
row
> was updated. Is there anything simular in PosgreSQL? How can I accomplish
> something simular inside the database, or am I stuck populating the field
in
> some manner as in the following example

There is no such datatype in postgresql. If you just need the current time
inserted on INSERT and not on UPDATE, then you can declare that column with
"default now()". Else you will need to install a trigger, then you can have
exactly the same behaviour as with mysql.

Regards,
Bjoern

postgreSQL actually has a better implementation of timestamps.... In
mySQL -- You can have only 1 timestamp field...

In postgreSQL -- you can have as many "create_dt" timestamp default 'now()'
fields as you want and on an insert -- they ALL get stamped whereas in
mySQL --ONLY 1 COLUMN gets updated....

""Alan T. Miller"" <amiller(at)hollywood101(dot)com> wrote in message
news:003a01c2c2da$a90d10a0$6e01a8c0(at)webdev(dot)(dot)(dot)
> As someone who is just getting started with PostygreSQL from years working
> with MySQL, it appears that the timestamp data type does not behave in the
> way it did with MySQL. I got used to just defining a column as a timestamp
> and letting the database throw the latest time stamp in there whenever a
row
> was updated. Is there anything simular in PosgreSQL? How can I accomplish
> something simular inside the database, or am I stuck populating the field
in
> some manner as in the following example
>
> update blah blah blah timestamp = NOW()
>
> Thanks,
>
> Alan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

> Ok, but then it should be sufficient to recreate the txt2txtidx function
> and/or the index after a change of parser and dictionaries. So generally
> spoken, the index function approach to tsearch works, if you take care of
> that. I gladly recreate the function and/or index from time to time, if I
> can do without that additional column, bloated dump and slow trigger.
>
> This is very good news, as this is a very easy approach to having an easy
to
> use fulltextsearch in postgresql.

I talked a bit more with Oleg and Teodor about this index function approach
and we came to the conclusion that it is safe to use (if you take care of
the above), but it might be a bit slower than the original column based
approach. That it because the used operators are defined with RECHECK, and
with the index function approach the RECHECK is against an (expensive)
function instead of "raw" data in a column.

Btw. the "iscachable" has another advantage, it seems to really speed up the
search. I have done tests again 250000 entries, mostly nicknames based on
fantasy (so the english stemmer has not much to do), and the first search
for a name takes about 0.5 - 2.5 seconds whereas all subsequent searches for
the same name (even with fresh inserted data) take less than 0.1 seconds. Or
does this have nothing to do with "iscachable"?

Regards,
Bjoern

"Bjorn Metzdorf" <bm(at)turtle-entertainment(dot)de> writes:
> Btw, the error message was confusing. It said that the function has to be
> marked "isImmutable", but there is no such attribute, instead "iscachable"
> had to be used. This seems to be a bug.

Actually the preferred syntax in 7.3 is

CREATE FUNCTION ... LANGUAGE foo IMMUTABLE

For awhile during 7.3 development you had to write WITH (isImmutable)
but we changed the syntax to be more SQL-spec-compatible. This error
message seems not to have gotten fixed --- thanks for pointing it out.

regards, tom lane

> As someone who is just getting started with PostygreSQL from years working
> with MySQL, it appears that the timestamp data type does not behave in the
> way it did with MySQL.

Much as I like MySQL, it can sometimes be a little *too* helpful.

> I got used to just defining a column as a timestamp
> and letting the database throw the latest time stamp in there whenever a
> row
> was updated. Is there anything simular in PosgreSQL?

When you create the table do something like:

CREATE TABLE foo (
bar timestamp DEFAULT now(),
...
);

You can of course do this with any column-type and value. See the
SQL-reference for details.

- Richard Huxton

On Thu, Jan 23, 2003 at 04:26:31AM -0800, Alan T. Miller wrote:
> As someone who is just getting started with PostygreSQL from
> years working with MySQL, it appears that the timestamp data
> type does not behave in the way it did with MySQL. I got used
> to just defining a column as a timestamp and letting the
> database throw the latest time stamp in there whenever a row
> was updated. Is there anything simular in PosgreSQL? How can I
> accomplish something simular inside the database, or am I stuck
> populating the field in some manner as in the following example
>
> update blah blah blah timestamp = NOW()

triggers or rules can do that with any timestamp (or timestamp(0)
-- no partial-seconds) field. here's a "rules" approach:

create table _something (
id serial,
dat text,
freshened timestamp(0),
primary key ( id )
);

create view something as
select
id,
dat,
freshened
from
_something;

create rule something_add as
on insert to something
do instead (
insert into _something (
--id,
dat,
freshened
) values (
--let id take care of itself,
NEW.dat,
current_timestamp
);
);

create rule something_edit as
on update to something
do instead (
update _something set
--id = leave it alone,
dat = NEW.dat,
freshened = current_timestamp
where
id = NEW.id
;
);

then you can just

insert into something (dat) values ('yada yada');
update something set dat = 'here we go' where id = 23978;

and "freshened" takes care of itself.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

Of course the proposed solution solves the INSERTs....you can also set a
trigger that
on UPDATE set the value to 'now()'

dev(at)archonet(dot)com wrote:

>>As someone who is just getting started with PostygreSQL from years working
>>with MySQL, it appears that the timestamp data type does not behave in the
>>way it did with MySQL.
>>
>>
>
>Much as I like MySQL, it can sometimes be a little *too* helpful.
>
>
>
>>I got used to just defining a column as a timestamp
>>and letting the database throw the latest time stamp in there whenever a
>>row
>>was updated. Is there anything simular in PosgreSQL?
>>
>>
>
>When you create the table do something like:
>
>CREATE TABLE foo (
> bar timestamp DEFAULT now(),
> ...
>);
>
>You can of course do this with any column-type and value. See the
>SQL-reference for details.
>
>- Richard Huxton
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

One word of caution, I *think* you want quotes around the 'now()'
statement in your table definition. Otherwise your default value will be
the instant the *table* was created, not the instant your insert
happened.

I too had an adjustment period when switching from MySQL to postgres.
However, I think you'll find that if you use a RULE to implement this
feature you will soon become addicted to PostgreSQL's advanced feature
set.

You can learn more about rules by going to
http://www.ca.postgresql.org/docs/aw_pgsql_book/node124.html where you
can find a brief description and example, or for more detail, you can go
to
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/rules-insert
.html

I attained all of my database knowledge through hands on experience;
much of it on MySQL. Switching to PostgreSQL was somewhat traumatic
because it has a lot more power and a lot more features. Once I got a
handle on things like Views, Triggers and Rules, I have become somewhat
dependant on them and I haven't been able to use MySQL for anything more
than the most basic of applications. I guess that's just a warning...
There may be no turning back.

--
Matthew Nuzum
www.bearfruit.org
cobalt(at)bearfruit(dot)org

> -----Original Message-----
> From: Björn Metzdorf [mailto:bm(at)turtle-entertainment(dot)de]
> Sent: Thursday, January 23, 2003 7:32 AM
> To: Alan T. Miller; pgsql-general(at)postgresql(dot)org
> Subject: Re: I was spoiled by the MySQL timestamp field
>
> > As someone who is just getting started with PostygreSQL from years
> working
> > with MySQL, it appears that the timestamp data type does not behave
in
> the
> > way it did with MySQL. I got used to just defining a column as a
> timestamp
> > and letting the database throw the latest time stamp in there
whenever a
> row
> > was updated. Is there anything simular in PosgreSQL? How can I
> accomplish
> > something simular inside the database, or am I stuck populating the
> field
> in
> > some manner as in the following example
>
> There is no such datatype in postgresql. If you just need the current
time
> inserted on INSERT and not on UPDATE, then you can declare that column
> with
> "default now()". Else you will need to install a trigger, then you can
> have
> exactly the same behaviour as with mysql.
>
> Regards,
> Bjoern

Why not just use a DEFAULT NOW()?
ie:
CREATE TABLE blah (
time TIMESTAMP NOT NULL DEFAULT NOW()
);

I know it works for DATETIME types, don't know about TIMESTAMP but I assume
it would be the same.

Luke.

----- Original Message -----
From: "will trillich" <will(at)serensoft(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, January 24, 2003 7:13 AM
Subject: Re: [GENERAL] I was spoiled by the MySQL timestamp field

> On Thu, Jan 23, 2003 at 04:26:31AM -0800, Alan T. Miller wrote:
> > As someone who is just getting started with PostygreSQL from
> > years working with MySQL, it appears that the timestamp data
> > type does not behave in the way it did with MySQL. I got used
> > to just defining a column as a timestamp and letting the
> > database throw the latest time stamp in there whenever a row
> > was updated. Is there anything simular in PosgreSQL? How can I
> > accomplish something simular inside the database, or am I stuck
> > populating the field in some manner as in the following example
> >
> > update blah blah blah timestamp = NOW()
>
> triggers or rules can do that with any timestamp (or timestamp(0)
> -- no partial-seconds) field. here's a "rules" approach:
>
> create table _something (
> id serial,
> dat text,
> freshened timestamp(0),
> primary key ( id )
> );
>
> create view something as
> select
> id,
> dat,
> freshened
> from
> _something;
>
> create rule something_add as
> on insert to something
> do instead (
> insert into _something (
> --id,
> dat,
> freshened
> ) values (
> --let id take care of itself,
> NEW.dat,
> current_timestamp
> );
> );
>
> create rule something_edit as
> on update to something
> do instead (
> update _something set
> --id = leave it alone,
> dat = NEW.dat,
> freshened = current_timestamp
> where
> id = NEW.id
> ;
> );
>
> then you can just
>
> insert into something (dat) values ('yada yada');
> update something set dat = 'here we go' where id = 23978;
>
> and "freshened" takes care of itself.
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will(at)serensoft(dot)com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

See http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=10

Matthew Nuzum wrote:
> One word of caution, I *think* you want quotes around the 'now()'
> statement in your table definition. Otherwise your default value will be
> the instant the *table* was created, not the instant your insert
> happened.
>
> I too had an adjustment period when switching from MySQL to postgres.
> However, I think you'll find that if you use a RULE to implement this
> feature you will soon become addicted to PostgreSQL's advanced feature
> set.
>
> You can learn more about rules by going to
> http://www.ca.postgresql.org/docs/aw_pgsql_book/node124.html where you
> can find a brief description and example, or for more detail, you can go
> to
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/rules-insert
> .html
>
> I attained all of my database knowledge through hands on experience;
> much of it on MySQL. Switching to PostgreSQL was somewhat traumatic
> because it has a lot more power and a lot more features. Once I got a
> handle on things like Views, Triggers and Rules, I have become somewhat
> dependant on them and I haven't been able to use MySQL for anything more
> than the most basic of applications. I guess that's just a warning...
> There may be no turning back.
>
> --
> Matthew Nuzum
> www.bearfruit.org
> cobalt(at)bearfruit(dot)org
> =20
>
>
>>-----Original Message-----
>>From: Bj=F6rn Metzdorf [mailto:bm(at)turtle-entertainment(dot)de]
>>Sent: Thursday, January 23, 2003 7:32 AM
>>To: Alan T. Miller; pgsql-general(at)postgresql(dot)org
>>Subject: Re: I was spoiled by the MySQL timestamp field
>>=20
>>
>>>As someone who is just getting started with PostygreSQL from years
>>
>>working
>>
>>>with MySQL, it appears that the timestamp data type does not behave
>
> in
>
>>the
>>
>>>way it did with MySQL. I got used to just defining a column as a
>>
>>timestamp
>>
>>>and letting the database throw the latest time stamp in there
>
> whenever a
>
>>row
>>
>>>was updated. Is there anything simular in PosgreSQL? How can I
>>
>>accomplish
>>
>>>something simular inside the database, or am I stuck populating the
>>
>>field
>>in
>>
>>>some manner as in the following example
>>
>>=20
>>There is no such datatype in postgresql. If you just need the current
>
> time
>
>>inserted on INSERT and not on UPDATE, then you can declare that column
>>with
>>"default now()". Else you will need to install a trigger, then you can
>>have
>>exactly the same behaviour as with mysql.
>>=20
>>Regards,
>>Bjoern
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Fri, Jan 24, 2003 at 09:57:04AM +1300, Luke Pascoe wrote:
> Why not just use a DEFAULT NOW()?
> ie:
> CREATE TABLE blah (
> time TIMESTAMP NOT NULL DEFAULT NOW()
> );
>
> I know it works for DATETIME types, don't know about TIMESTAMP
> but I assume it would be the same.

great for INSERT, but rather inert on UPDATE.

create table _gribnif (
created timestamp default current_timestamp,
modified timestamp,
other text,
);

create view gribnif ...

create rule gribnif_edit as
on update to gribnif
do instead (
update _gribnif set
MODIFIED = CURRENT_TIMESTAMP,
other = NEW.other
where
id = NEW.id
;
);

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

At 03:02 PM 1/23/03 -0500, Matthew Nuzum wrote:

>One word of caution, I *think* you want quotes around the 'now()'
>statement in your table definition. Otherwise your default value will be
>the instant the *table* was created, not the instant your insert
>happened.

Would plain: default 'now' work? If it does (it seems to), what is the
difference?

Link.

What's the difference between 'now()' and 'current_timestamp' ?

will trillich wrote:
>
> On Fri, Jan 24, 2003 at 09:57:04AM +1300, Luke Pascoe wrote:
> > Why not just use a DEFAULT NOW()?
> > ie:
> > CREATE TABLE blah (
> > time TIMESTAMP NOT NULL DEFAULT NOW()
> > );
> >
> > I know it works for DATETIME types, don't know about TIMESTAMP
> > but I assume it would be the same.
>
> great for INSERT, but rather inert on UPDATE.
>
> create table _gribnif (
> created timestamp default current_timestamp,
> modified timestamp,
> other text,
> );
>
> create view gribnif ...
>
> create rule gribnif_edit as
> on update to gribnif
> do instead (
> update _gribnif set
> MODIFIED = CURRENT_TIMESTAMP,
> other = NEW.other
> where
> id = NEW.id
> ;
> );
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will(at)serensoft(dot)com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon

Dennis Gearon <gearond(at)cvc(dot)net> writes:
> What's the difference between 'now()' and 'current_timestamp' ?

None AFAIK. now() is historical Postgres usage, CURRENT_TIMESTAMP is
the SQL-spec-mandated spelling of the same functionality.

CURRENT_TIMESTAMP with an argument does something a bit different,
though.

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

regards, tom lane

On Fri, Jan 24, 2003 at 10:55:26AM -0500, Tom Lane wrote:
> Dennis Gearon <gearond(at)cvc(dot)net> writes:
> > What's the difference between 'now()' and 'current_timestamp' ?
>
> None AFAIK. now() is historical Postgres usage, CURRENT_TIMESTAMP is
> the SQL-spec-mandated spelling of the same functionality.
>
> CURRENT_TIMESTAMP with an argument does something a bit different,
> though.
>
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

the one caveat is outlined at the bottom of that very page:

All the date/time data types also accept the special literal
value now to specify the current date and time. Thus, the
following three all return the same result:

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

Note: You do not want to use the third form when specifying
a DEFAULT clause while creating a table. The system will
convert now to a timestamp as soon as the constant is
parsed, so that when the default value is needed, the ##TIME
OF THE TABLE CREATION## would be used! The first two forms
will not be evaluated until the default value is used,
because they are function calls. Thus they will give the
desired behavior of defaulting to the time of row insertion.

[emphasis mine]

so careful using "now" when you mean to use "now()".
or better yet, "current_timestamp" for consistency.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

The difference is when now() gets interpreted into a date. Someone
please correct me if I'm wrong...

If you create a table at 15:00 Jan 26, 2003 and you use an unquoted
now() as the default value for a field, then each record will have it's
default value as 15:00 Jan 26, 2003.

If you quote the now(), then the default value for each newly created
record will be now() which is interpreted at the moment the record is
created.

I've used different rdbms so please forgive me if I'm documenting the
characteristics of a different system.

--
Matthew Nuzum
www.bearfruit.org
cobalt(at)bearfruit(dot)org

> -----Original Message-----
> From: Lincoln Yeoh [mailto:lyeoh(at)pop(dot)jaring(dot)my]
> Sent: Friday, January 24, 2003 4:47 AM
> To: Matthew Nuzum; 'Björn Metzdorf'; 'Alan T. Miller'; pgsql-
> general(at)postgresql(dot)org
> Subject: Re: [GENERAL] I was spoiled by the MySQL timestamp field
>
> At 03:02 PM 1/23/03 -0500, Matthew Nuzum wrote:
>
> >One word of caution, I *think* you want quotes around the 'now()'
> >statement in your table definition. Otherwise your default value will
be
> >the instant the *table* was created, not the instant your insert
> >happened.
>
> Would plain: default 'now' work? If it does (it seems to), what is the
> difference?
>
> Link.

"Matthew Nuzum" <cobalt(at)bearfruit(dot)org> writes:
> The difference is when now() gets interpreted into a date. Someone
> please correct me if I'm wrong...

> If you create a table at 15:00 Jan 26, 2003 and you use an unquoted
> now() as the default value for a field, then each record will have it's
> default value as 15:00 Jan 26, 2003.

You're wrong...

now() is a function call and will not be folded into a constant.

AFAIR, the only case that does get folded to a constant is

... mycol timestamp default timestamp 'now'

since "timestamp 'now'" is Postgres' notation for a literal constant of
a specific datatype (timestamp in this case). The string 'now' is
immediately fed to the timestamp datatype's input converter, and behold
it produces the current time, which is then stored as a timestamp
constant.

The notation 'now()' that various people have suggested is in fact
invalid, being not a function call but a literal --- but it seems that
the timestamp input converter is sloppy about detecting trailing garbage
in its input string. You should get a "Bad timestamp external
representation" error from it, but at the moment you don't.

You can easily check the behavior for yourself rather than relying on
other people's assertions. For example:

regression=# create table t1 (f1 timestamp default now(),
regression(# f2 timestamp default 'now',
regression(# f3 timestamp default timestamp 'now');
CREATE TABLE
regression=# \d t1
Table "public.t1"
Column | Type | Modifiers

--------+-----------------------------+-------------------------------------------------------------------
f1 | timestamp without time zone | default now()
f2 | timestamp without time zone | default 'now'
f3 | timestamp without time zone | default '2003-01-26 16:51:11.246954'::timestamp without time zone

and even more directly:

regression=# insert into t1 default values;
INSERT 1003028 1
regression=# insert into t1 default values;
INSERT 1003029 1
regression=# select * from t1;
f1 | f2 | f3
----------------------------+----------------------------+----------------------------
2003-01-26 16:58:13.173561 | 2003-01-26 16:58:13.173561 | 2003-01-26 16:51:11.246954
2003-01-26 16:58:14.323162 | 2003-01-26 16:58:14.323162 | 2003-01-26 16:51:11.246954
(2 rows)

(BTW, the reason 'now' without "timestamp" in front works is that this
is not a timestamp literal but a text literal, which will be coerced
to timestamp at runtime.)

regards, tom lane

At 05:00 PM 1/26/03 -0500, Tom Lane wrote:
>(BTW, the reason 'now' without "timestamp" in front works is that this
>is not a timestamp literal but a text literal, which will be coerced
>to timestamp at runtime.)

Is it known at the moment which of those methods the Postgresql team are
aiming to continue supporting for the near/medium future?

e.g. current_timestamp is guaranteed. now() for the forseeable future.
'now' for the next few versions.

Thanks,
Link.

Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> At 05:00 PM 1/26/03 -0500, Tom Lane wrote:
>> (BTW, the reason 'now' without "timestamp" in front works is that this
>> is not a timestamp literal but a text literal, which will be coerced
>> to timestamp at runtime.)

> Is it known at the moment which of those methods the Postgresql team are
> aiming to continue supporting for the near/medium future?

AFAIK there are no plans to break any of them; though certainly
CURRENT_TIMESTAMP is the most future-proof, being spec-mandated.

regards, tom lane

On Sun, Jan 26, 2003 at 05:00:36PM -0500, Tom Lane wrote:
> now() is a function call and will not be folded into a constant.

> You can easily check the behavior for yourself rather than relying on
> other people's assertions.

note -- this is ALWAYS a good idea. especially when it's *me*
giving the assertion :)

reminds me of a high-school prof i had. he'd stop in the middle
of a lesson and ask if anybody knew the meaning of a word he'd
just used ("mote", for example); if not, he'd supply a
definition (small fly) and go on.

later we'd have a vocabulary test; any answers that parroted
what he'd said were always marked wrong, as he'd just made them
up off-the-cuff. (mote is a particle, as in gritty dust.)

double-check for yourself. don't take anybody's word for it.
KNOW, don't trust.

the best way to find out, is to FIND OUT.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

At 11:59 PM 1/26/03 -0600, will trillich wrote:

>On Sun, Jan 26, 2003 at 05:00:36PM -0500, Tom Lane wrote:
> > now() is a function call and will not be folded into a constant.
>
> > You can easily check the behavior for yourself rather than relying on
> > other people's assertions.
>
>note -- this is ALWAYS a good idea. especially when it's *me*
>giving the assertion :)

It's still good hear from the developers what they think the behaviour
should be, and would be.

If the devs, docs and software agree then it'll be fine to use the feature.
Otherwise if possible, it might be a better idea to use a different feature
to achieve what I want. Don't want to use a behaviour will soon be
deprecated/changed.

Of course, it's not good to pester you guys for everything either. But
current_timestamp vs now() vs 'now' would probably be a popular feature
enough to clarify.

Hope this doesn't waste too much bandwidth ;).
Link.

FYI, I usually prefer triggers in cases where you want to modify/check
the row as it is being processed, and rules for modifying other
rows/tables as part of row processing, but both do work.

---------------------------------------------------------------------------

will trillich wrote:
> On Thu, Jan 23, 2003 at 04:26:31AM -0800, Alan T. Miller wrote:
> > As someone who is just getting started with PostygreSQL from
> > years working with MySQL, it appears that the timestamp data
> > type does not behave in the way it did with MySQL. I got used
> > to just defining a column as a timestamp and letting the
> > database throw the latest time stamp in there whenever a row
> > was updated. Is there anything simular in PosgreSQL? How can I
> > accomplish something simular inside the database, or am I stuck
> > populating the field in some manner as in the following example
> >
> > update blah blah blah timestamp = NOW()
>
> triggers or rules can do that with any timestamp (or timestamp(0)
> -- no partial-seconds) field. here's a "rules" approach:
>
> create table _something (
> id serial,
> dat text,
> freshened timestamp(0),
> primary key ( id )
> );
>
> create view something as
> select
> id,
> dat,
> freshened
> from
> _something;
>
> create rule something_add as
> on insert to something
> do instead (
> insert into _something (
> --id,
> dat,
> freshened
> ) values (
> --let id take care of itself,
> NEW.dat,
> current_timestamp
> );
> );
>
> create rule something_edit as
> on update to something
> do instead (
> update _something set
> --id = leave it alone,
> dat = NEW.dat,
> freshened = current_timestamp
> where
> id = NEW.id
> ;
> );
>
> then you can just
>
> insert into something (dat) values ('yada yada');
> update something set dat = 'here we go' where id = 23978;
>
> and "freshened" takes care of itself.
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will(at)serensoft(dot)com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sun, Jan 26, 2003 at 05:00:36PM -0500, Tom Lane wrote:
> "Matthew Nuzum" <cobalt(at)bearfruit(dot)org> writes:
> > The difference is when now() gets interpreted into a date. Someone
> > please correct me if I'm wrong...

> now() is a function call and will not be folded into a constant.

I know it's not really related, but I think it might be worth noting
here, also, that now() is (was? I don't have anything later than 7.2
handy for testing at the moment) resolves to the beginning of the
transaction. If you need time to move forward during your
transaction, you need timeofday().

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110