MySQL has this nifty/annoying feature/bug of special data type “TIMESTAMP". It is like a DATETIME, but it gets automatically updated whenever you modify the row.
I'll try to add the same feature to PostgreSQL.
This is how it works in MySQL:
mysql> CREATE TABLE test (x VARCHAR(10), y TIMESTAMP); Query OK, 0 ROWS affected (0.03 sec) mysql> INSERT INTO test (x) VALUES ('a'); Query OK, 1 ROW affected (0.00 sec) mysql> INSERT INTO test (x, y) VALUES ('b', '2008-02-02 22:22:22'); Query OK, 1 ROW affected (0.00 sec)
I created simple table and inserted 2 very simple rows. This is how it looks:
mysql> SELECT * FROM test; +------+---------------------+ | x | y | +------+---------------------+ | a | 2008-05-08 16:22:45 | | b | 2008-02-02 22:22:22 | +------+---------------------+ 2 ROWS IN SET (0.00 sec)
Now, let's update one of the rows, with modification of x field:
mysql> UPDATE test SET x = 'c' WHERE x = 'a'; Query OK, 1 ROW affected (0.00 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM test; +------+---------------------+ | x | y | +------+---------------------+ | c | 2008-05-08 16:23:00 | | b | 2008-02-02 22:22:22 | +------+---------------------+ 2 ROWS IN SET (0.00 sec)
OK, so let's try to modify also y field (the timestamp field):
mysql> UPDATE test SET x = 'd', y = '2008-01-01 01:23:45' WHERE x = 'b'; Query OK, 1 ROW affected (0.00 sec) ROWS matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM test; +------+---------------------+ | x | y | +------+---------------------+ | c | 2008-05-08 16:23:00 | | d | 2008-01-01 01:23:45 | +------+---------------------+ 2 ROWS IN SET (0.00 sec)
Having done that we can pinpoint actual functionality of TIMESTAMP data type:
- When inserting new row, timestamp column is set to now() unless it had explicitly assigned value.
- When updating row, if I explicitly stated new value for timestamp field, it will be set to this value. Otherwise, it will be set to now().
Inserting part can be done by simply specifying “default now() not null". But what about second?
It's actually quite easy. All that's required is a simple trigger:
CREATE OR REPLACE FUNCTION trg_handle_timestamp() RETURNS TRIGGER AS $BODY$ BEGIN IF NEW.y = OLD.y THEN NEW.y := now(); END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER trg_handle_timestamp BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE trg_handle_timestamp();
This is how it works:
# CREATE TABLE test ( =# x VARCHAR(10), =# y TIMESTAMP NOT NULL DEFAULT now() =# ); CREATE TABLE # INSERT INTO test (x) VALUES ('a'); INSERT 0 1 # INSERT INTO test (x, y) VALUES ('b', '2008-02-02 22:22:22'); INSERT 0 1 # SELECT * FROM test; x | y ---+---------------------------- a | 2008-05-08 16:35:39.371974 b | 2008-02-02 22:22:22 (2 ROWS)
Base inserts are working OK, value for y field in first row was correctly set using default value.
Now, I add the trigger:
# CREATE OR REPLACE FUNCTION trg_handle_timestamp() RETURNS TRIGGER AS $BODY$ =# BEGIN =# IF NEW.y = OLD.y THEN NEW.y := now(); END IF; =# RETURN NEW; =# END; =# $BODY$ LANGUAGE 'plpgsql'; CREATE FUNCTION # CREATE TRIGGER trg_handle_timestamp BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE trg_handle_timestamp(); CREATE TRIGGER
And now for some updates:
# UPDATE test SET x = 'c' WHERE x = 'a'; UPDATE 1 # UPDATE test SET x = 'd', y = '2008-01-01 01:23:45' WHERE x = 'b'; UPDATE 1 # SELECT * FROM test; x | y ---+---------------------------- c | 2008-05-08 16:35:56.318366 d | 2008-01-01 01:23:45 (2 ROWS)
As You can see, it works well.
But there are couple of things I don't like:
- in MySQL, if I'll do UPDATE test set y=y; it will keep previous values of y. In PostgreSQL – they will be overwritten by new values because we can't actually “know" what fields were set explicitly in UPDATE, and I had to use (OLD = NEW) trick to check if the field was updated
- I have to remember to add “not null default now()"
- I have to remember to add trigger for the table
- If I have many tables with this “MySQL TIMESTAMP", they all have to be named the same, otherwise I will have to rewrite trigger procedure for each of them.
To be honest – I can't do anything about first and third issu – or at least I don't know how to do anything about them.
But I can fix the other two 🙂
To do it, I will create new data type (domain). Let's name it mytimestamp:
CREATE DOMAIN mytimestamp AS TIMESTAMP DEFAULT now() NOT NULL;
This will easily solve the issue about remembering to add “not null default now()".
And what about other gripe?
Well, our trigger has to be smarter. It has to know the data type of all columns in given table. And then work with them.
Writing this in pl/PgSQL would be a nightmare, so let's change the language to pl/Perl:
CREATE OR REPLACE FUNCTION trg_handle_timestamp() RETURNS TRIGGER AS $BODY$ unless ( $_SHARED{'trg_handle_timestamp_plan'} ) { $_SHARED{'trg_handle_timestamp_plan'} = spi_prepare( 'SELECT column_name FROM information_schema.columns where table_schema = $1 and table_name = $2 and domain_name = $3', 'TEXT', 'TEXT', 'TEXT' ); } my $returned = spi_exec_prepared( $_SHARED{'trg_handle_timestamp_plan'}, $_TD->{'table_schema'}, $_TD->{'table_name'}, 'mytimestamp' ); my $was_modified = 0; my @TIME = localtime(); FOR my $row (@{ $returned->{'rows'} }) { my $column_name = $row->{'column_name'}; IF ($_TD->{'new'}->{$column_name} eq $_TD->{'old'}->{$column_name}) { $was_modified = 1; $_TD->{'new'}->{$column_name} = sprintf("%04u-%02u-%02u %02u:%02u:%02u", 1900 + $time[5], 1 + $time[4], @TIME[3,2,1,0]); } } RETURN 'MODIFY' IF $was_modified; RETURN; $BODY$ LANGUAGE plperl; CREATE TRIGGER trg_handle_timestamp BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE trg_handle_timestamp();
Whoa, it looks much more complicated, but don't worry. It has to be written only once, and it works for all mytimestamp columns in all tables, regardless of how are they named.
So, with this new trigger, let's make new test table:
CREATE TABLE test ( x VARCHAR(10), y mytimestamp, z mytimestamp ); INSERT INTO test (x) VALUES ('a'); INSERT INTO test (x, y) VALUES ('b', '2008-02-02 22:22:22'); INSERT INTO test (x, z) VALUES ('c', '2008-03-03 03:33:33');
Content of the table:
SELECT * FROM test; x | y | z ---+----------------------------+---------------------------- a | 2008-05-08 17:12:28.830297 | 2008-05-08 17:12:28.830297 b | 2008-02-02 22:22:22 | 2008-05-08 17:12:28.831448 c | 2008-05-08 17:12:28.832128 | 2008-03-03 03:33:33 (3 ROWS)
Now, let's add the trigger and run some updates:
CREATE TRIGGER trg_handle_timestamp BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE trg_handle_timestamp(); UPDATE test SET x = 'd' WHERE x = 'a'; UPDATE test SET x = 'e', y = '2008-01-01 01:23:45' WHERE x = 'b'; UPDATE test SET x = 'f', y = '2008-01-01 01:23:45', z = '2007-12-31 23:59:59' WHERE x = 'c';
Afterward, the test table looks like this:
SELECT * FROM test; x | y | z ---+---------------------+--------------------- d | 2008-05-08 17:12:28 | 2008-05-08 17:12:28 e | 2008-01-01 01:23:45 | 2008-05-08 17:12:28 f | 2008-01-01 01:23:45 | 2007-12-31 23:59:59 (3 ROWS)
Not bad. Of course the pl/Perl trigger is more complex than MySQL approach, but hey – You already have it – You can copy it directly from here.
For me the above simply doesn’t sound right….
For any update/insert I think a developer needs to specify what he exactly want’s, not using some automated guessing system… if you know what I mean…
It’s the same as how MySQL handles auto increment, you could assign a NULL to the column and it get’s incremented!!! Not right…
Ries
I use some like this:
CREATE TABLE movimientos
(
idmovimientos serial NOT NULL,
desc character varying(250) NOT NULL,
modificacion timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT pk_movimientos PRIMARY KEY (idmovimientos)
);
===============================================================
CREATE TRIGGER updatemodificacion
AFTER UPDATE
ON movimientos
FOR EACH ROW
EXECUTE PROCEDURE updatemodificacion();
=============================================================
CREATE OR REPLACE FUNCTION updatemodificacion()
RETURNS “trigger” AS
$BODY$
BEGIN
IF TG_OP = ‘UPDATE’ THEN
NEW.modificacion := current_timestamp;
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
;
Example:
If exists a column (modificacion) in all tables of the DB, simply create the trigger in every table and the column (modificacion) will be updated with de value current_timestamp if you modify other column.
Hugo
I have something like this, to obtain similar result
==================================
CREATE TABLE movimientos
(
idmovimientos serial NOT NULL,
modificacion timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT pk_movimientos PRIMARY KEY (idmovimientos)
)
===================================
CREATE OR REPLACE FUNCTION updatemodificacion()
RETURNS “trigger” AS
$BODY$
BEGIN
IF TG_OP = ‘UPDATE’ THEN
NEW.modificacion := current_timestamp;
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
ALTER FUNCTION updatemodificacion() OWNER TO postgres;
COMMENT ON FUNCTION updatemodificacion() IS ‘Actualiza el campo modifcacion de cada tabla de la base de datos en el evento Update’;
==================================
CREATE TRIGGER updatemodificacion
AFTER UPDATE
ON movimientos
FOR EACH ROW
EXECUTE PROCEDURE updatemodificacion();
If you’re trying to model MySQl for compatibility reasons, you should keep in mind that the timestamp datatype has the auto-update behavior only for the first timestamp column of a table, unless the others contain ON UPDATE CURRENT_TIMESTAMP. Well it’s actually a little more convoluted than that.
I find having an “edited_on” column with a modification date that’s always updated by the system and can’t be altered by users to be more useful. I also often use an “edited_by” column to automatically capture a user_id of who created or updated a tuple.
SQL Server has TIMESTAMP type for similar use but this is surprisingly not a datetime.