the behaviour of timestamp on postgres.

[Prabu Subroto]

Dear my friends...

I created some tables with field timestamp (datatype
also timestamp). I mean, I want to have the data when
each record inserted or modified in the tables.

on MysQL, I just need to define the column (field)
with datatype "timestamp" and that's all. each time
new record inserted than the timestamp value will be
inserted automaticall. also for the data modification,
each time the data in the record modified than the
value of timestamp column will be modified
automatically.

How is the behaviour of the timestamp on postgres? I
have define the datatype of the column with
"timestamp" but each time I inserted a new record into
the table than the timestamp column (with datatype
"timestamp") stays empty.

How can I make the postgres complete the value of the
timestamp field automatically?

Please let me know.

Thank you very much in advance.

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Doug McNaught]

Prabu Subroto <pr***********@yahoo.com> writes:

How can I make the postgres complete the value of the
timestamp field automatically?

Add a DEFAULT NOW() clause to the column definition.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Sebastian Böck]

Prabu Subroto wrote:

Dear my friends...

I created some tables with field timestamp (datatype
also timestamp). I mean, I want to have the data when
each record inserted or modified in the tables.

on MysQL, I just need to define the column (field)
with datatype "timestamp" and that's all. each time
new record inserted than the timestamp value will be
inserted automaticall. also for the data modification,
each time the data in the record modified than the
value of timestamp column will be modified
automatically.

You can use triggers for that.

Try something like:

CREATE FUNCTION set_timestamp() RETURNS TRIGGER AS '
BEGIN
NEW.timestamp := now();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER insert_timestamp BEFORE INSERT ON table
FOR EACH ROW EXECUTE PROCEDURE set_timestamp();
CREATE TRIGGER update_timestamp BEFORE UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE set_timestamp();

HTH

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Prabu Subroto]

It's solved.

Thank you very much, Doug.

Thanks.
--- Doug McNaught <do**@mcnaught.org> wrote:

Prabu Subroto <pr***********@yahoo.com> writes:

How can I make the postgres complete the value of

the

timestamp field automatically?

Add a DEFAULT NOW() clause to the column definition.
-Doug
--
Let us cross over the river, and rest under the
shade of the trees.
--T. J. Jackson, 1863

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Csaba Nagy]

Prabu,

Be aware that this will only work for inserts, and updating the row will
not automatically update the timestamp column. You will have to do it
yourself if you need that, either by using the right query (which is
actually a preferred solution).
Automatically you could do it through a rule - but I have never used the
Postgres rule system, so I don't know how to do that.
The update trigger posted in another reply will also work.

HTH,
Csaba.

On Wed, 2004-08-11 at 16:47, Prabu Subroto wrote:

It's solved.

Thank you very much, Doug.

Thanks.
--- Doug McNaught <do**@mcnaught.org> wrote:

Prabu Subroto <pr***********@yahoo.com> writes:

How can I make the postgres complete the value of

the

timestamp field automatically?

Add a DEFAULT NOW() clause to the column definition.
-Doug
--
Let us cross over the river, and rest under the
shade of the trees.
--T. J. Jackson, 1863

__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephan Szabo]

On Wed, 11 Aug 2004, Prabu Subroto wrote:

How is the behaviour of the timestamp on postgres? I
It's pretty much just a plain datatype.
have define the datatype of the column with
"timestamp" but each time I inserted a new record into
the table than the timestamp column (with datatype
"timestamp") stays empty.

How can I make the postgres complete the value of the
timestamp field automatically?

If you want insert time setting, you can use a default
clause on the column.

If you want update time modification, you can write a
before trigger that updates the column.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html