Mailinglist Archive: opensuse-programming (84 mails)

[Jan.Trippler@xxxxxxxxxxx (Jan Trippler)]

Am Mittwoch, 11. August 2004 15:13 schrieb Prabu Subroto:
> 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?

If you want to insert automatically the current system time in this 
field you can define a default value. By default there is default 
NULL for all columns you create (look at 
http://www.postgresql.org/docs/7.2/interactive/sql-createtable.html). 

Try this:
create table ...
...
created timestamp(0) not null default current_timestamp,
...

This does not affect updates. AFAIK you have to create an update 
trigger to automatically update the column.

Jan

-- 
Linux-Quickies: http://www.jan-trippler.de
PingoS: http://www.pingos.org