Updating column on row update

this is how I do it if this helps:

column_name timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) without time zone

-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Thom Brown
Sent: Sun 11/22/2009 2:50 PM
To: PGSQL Mailing List
Subject: [GENERAL] Updating column on row update

Hi,

This should be simple, but for some reason I'm not quite sure what the
solution is. I want to be able to update the value of a column for rows
that have been updated. More specifically, if a row is updated, I want it's
modified_date column to be populated with the current time stamp. I've
looked at triggers and rules, and it looks like I'd need to create a
function just to achieve this which seems incredibly clumsy and unnecessary.
Could someone enlighten me?

Thanks

Thom

2009/11/22 Aaron Burnett <aburnett@bzzagent.com>

this is how I do it if this helps:

column_name timestamp without time zone NOT NULL DEFAULT
('now'::text)::timestamp(6) without time zone

Hi Aaron. Thanks for the reply, but that would only insert the current

date upon insertion into the table, not when the row is updated.

For example

CREATE TABLE timetest(
id SERIAL NOT NULL,
stuff text,
stamp timestamp NOT NULL DEFAULT now()
);

INSERT INTO timetest (stuff) VALUES ('meow');

The table would contain:

id | stuff | stamp
----+-------+----------------------------
1 | meow | 2009-11-22 20:04:51.261739

But then I'd execute:

UPDATE timetest SET stuff = 'bark' WHERE id = 1;

id | stuff | stamp
----+-------+----------------------------
1 | bark | 2009-11-22 20:04:51.261739

You can see the time hasn't changed. But I'd want that stamp column to
update to the current time without referring to that column in the update
statement.

Thanks

Thom

On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown@gmail.com> wrote:

Hi,
This should be simple, but for some reason I'm not quite sure what the
solution is.  I want to be able to update the value of a column for rows
that have been updated.  More specifically, if a row is updated, I want it's
modified_date column to be populated with the current time stamp.  I've
looked at triggers and rules, and it looks like I'd need to create a
function just to achieve this which seems incredibly clumsy and unnecessary.
 Could someone enlighten me?

Well, you DO have to create a function, but it's not all that clumsy
really. Also it's quite flexible so you can do lots of complex stuff
and hide it away in a trigger function.

Example:

-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';

-- TABLE --

CREATE TABLE dtest (
id int primary key,
fluff text,
lm timestamp without time zone
);

--TRIGGER --

CREATE TRIGGER dtest
BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
modtime(lm);

-- SQL TESTS --

INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
SELECT * FROM dtest;
1 | this is a test | 2003-04-02 10:33:12.577089
2 | this is another test | 2003-04-02 10:33:18.591148
UPDATE dtest SET id=3 WHERE id=1;
3 | this is a test | 2003-04-02 10:34:52.219963 [1]The timestamp has changed for this record when we changed the id field.
UPDATE dtest SET fluff='now is the time' WHERE id=2;
SELECT * FROM dtest WHERE id=2;
2 | now is the time | 2003-04-02 10:38:06.259443 [2]The timestamp also changes for the fluff field.
UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
SELECT * FROM dtest WHERE id=3;
3 | this is a test | 2003-04-02 10:36:15.45687 [3]We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it

[1]: The timestamp has changed for this record when we changed the id field.
[2]: The timestamp also changes for the fluff field.
[3]: We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it
intercepted the change and forced it

On Sunday 22 November 2009 12:09:04 pm Thom Brown wrote:

2009/11/22 Aaron Burnett <aburnett@bzzagent.com>

this is how I do it if this helps:

column_name timestamp without time zone NOT NULL DEFAULT
('now'::text)::timestamp(6) without time zone

Hi Aaron. Thanks for the reply, but that would only insert the current

date upon insertion into the table, not when the row is updated.

For example

CREATE TABLE timetest(
id SERIAL NOT NULL,
stuff text,
stamp timestamp NOT NULL DEFAULT now()
);

INSERT INTO timetest (stuff) VALUES ('meow');

The table would contain:

id | stuff | stamp
----+-------+----------------------------
1 | meow | 2009-11-22 20:04:51.261739

But then I'd execute:

UPDATE timetest SET stuff = 'bark' WHERE id = 1;

id | stuff | stamp
----+-------+----------------------------
1 | bark | 2009-11-22 20:04:51.261739

You can see the time hasn't changed. But I'd want that stamp column to
update to the current time without referring to that column in the update
statement.

Thanks

Thom

You will need to use an UPDATE trigger with associated function.

--
Adrian Klaver
aklaver@comcast.net

2009/11/22 Scott Marlowe <scott.marlowe@gmail.com>

On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown@gmail.com> wrote:

Hi,
This should be simple, but for some reason I'm not quite sure what the
solution is. I want to be able to update the value of a column for rows
that have been updated. More specifically, if a row is updated, I want

it's

modified_date column to be populated with the current time stamp. I've
looked at triggers and rules, and it looks like I'd need to create a
function just to achieve this which seems incredibly clumsy and

unnecessary.

Could someone enlighten me?

Well, you DO have to create a function, but it's not all that clumsy
really. Also it's quite flexible so you can do lots of complex stuff
and hide it away in a trigger function.

Example:

-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';

-- TABLE --

CREATE TABLE dtest (
id int primary key,
fluff text,
lm timestamp without time zone
);

--TRIGGER --

CREATE TRIGGER dtest
BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
modtime(lm);

-- SQL TESTS --

INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
SELECT * FROM dtest;
1 | this is a test | 2003-04-02 10:33:12.577089
2 | this is another test | 2003-04-02 10:33:18.591148
UPDATE dtest SET id=3 WHERE id=1;
3 | this is a test | 2003-04-02 10:34:52.219963 [1]
UPDATE dtest SET fluff='now is the time' WHERE id=2;
SELECT * FROM dtest WHERE id=2;
2 | now is the time | 2003-04-02 10:38:06.259443 [2]
UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
SELECT * FROM dtest WHERE id=3;
3 | this is a test | 2003-04-02 10:36:15.45687 [3]

[1] The timestamp has changed for this record when we changed the id field.
[2] The timestamp also changes for the fluff field.
[3] We tried to set lm, but the trigger on that field in dtest
intercepted the change and forced it

Thanks Scott. It's a shame a function has to be used because it then has
the dependency of plpgsql being loaded. I'm attempting to write a database
schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
could get it to load plpgsql in as a language.

The problem now is if the the schema creation script is run against a
database where the language is already installed, I would get an error
saying it already exists. Is there a way to get it to check for it first,
and only create it if it isn't exist? Bear in mind I'd want this to be
compatible at least as far back as 8.1.

Thanks

Thom

On Sun, Nov 22, 2009 at 1:32 PM, Thom Brown <thombrown@gmail.com> wrote:

2009/11/22 Scott Marlowe <scott.marlowe@gmail.com>

On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown@gmail.com> wrote:

Hi,
This should be simple, but for some reason I'm not quite sure what the
solution is.  I want to be able to update the value of a column for rows
that have been updated.  More specifically, if a row is updated, I want
it's
modified_date column to be populated with the current time stamp.  I've
looked at triggers and rules, and it looks like I'd need to create a
function just to achieve this which seems incredibly clumsy and
unnecessary.
 Could someone enlighten me?

Well, you DO have to create a function, but it's not all that clumsy
really.   Also it's quite flexible so you can do lots of complex stuff
and hide it away in a trigger function.

Example:

-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
   BEGIN
       new.lm :=''now'';
       RETURN new;
   END;
' LANGUAGE 'plpgsql';

-- TABLE --

CREATE TABLE dtest (
   id int primary key,
   fluff text,
   lm timestamp without time zone
);

--TRIGGER --

CREATE TRIGGER dtest
 BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
   modtime(lm);

-- SQL TESTS --

INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
SELECT * FROM dtest;
 1 | this is a test       | 2003-04-02 10:33:12.577089
 2 | this is another test | 2003-04-02 10:33:18.591148
UPDATE dtest SET id=3 WHERE id=1;
 3 | this is a test | 2003-04-02 10:34:52.219963  [1]
UPDATE dtest SET fluff='now is the time' WHERE id=2;
SELECT * FROM dtest WHERE id=2;
 2 | now is the time | 2003-04-02 10:38:06.259443 [2]
UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
SELECT * FROM dtest WHERE id=3;
 3 | this is a test | 2003-04-02 10:36:15.45687 [3]

[1] The timestamp has changed for this record when we changed the id
field.
[2] The timestamp also changes for the fluff field.
[3] We tried to set lm, but the trigger on that field in dtest
intercepted the change and forced it

Thanks Scott.  It's a shame a function has to be used because it then has
the dependency of plpgsql being loaded.  I'm attempting to write a database
schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
could get it to load plpgsql in as a language.
The problem now is if the the schema creation script is run against a
database where the language is already installed, I would get an error
saying it already exists.  Is there a way to get it to check for it first,
and only create it if it isn't exist?  Bear in mind I'd want this to be
compatible at least as far back as 8.1.

Try this:

select * from pg_language ;

Pretty sure that exists pretty far back.

On Sun, Nov 22, 2009 at 1:32 PM, Thom Brown <thombrown@gmail.com> wrote:

Thanks Scott.  It's a shame a function has to be used because it then has
the dependency of plpgsql being loaded.  I'm attempting to write a database
schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
could get it to load plpgsql in as a language.

Nice thing about plpgsql is that as long as you own the database you
don't have to be a superuser to create language plgpsql.

2009/11/22 Scott Marlowe <scott.marlowe@gmail.com>

Thanks Scott. It's a shame a function has to be used because it then has
the dependency of plpgsql being loaded. I'm attempting to write a

database

schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
could get it to load plpgsql in as a language.
The problem now is if the the schema creation script is run against a
database where the language is already installed, I would get an error
saying it already exists. Is there a way to get it to check for it

first,

and only create it if it isn't exist? Bear in mind I'd want this to be
compatible at least as far back as 8.1.

Try this:

select * from pg_language ;

Pretty sure that exists pretty far back.

Yes, I noticed that existed in the catalogs, but how could that be
incorporated into an installation SQL script? The language constructs I
imagine I'd need to test that are in plpgsql itself.

Thanks

Thom

On Sunday 22 November 2009 1:10:36 pm Thom Brown wrote:

2009/11/22 Scott Marlowe <scott.marlowe@gmail.com>

Thanks Scott. It's a shame a function has to be used because it then
has the dependency of plpgsql being loaded. I'm attempting to write a

database

schema to accompany a PostgreSQL driver for a popular CMS, but I guess
I could get it to load plpgsql in as a language.
The problem now is if the the schema creation script is run against a
database where the language is already installed, I would get an error
saying it already exists. Is there a way to get it to check for it

first,

and only create it if it isn't exist? Bear in mind I'd want this to be
compatible at least as far back as 8.1.

Try this:

select * from pg_language ;

Pretty sure that exists pretty far back.

Yes, I noticed that existed in the catalogs, but how could that be
incorporated into an installation SQL script? The language constructs I
imagine I'd need to test that are in plpgsql itself.

Thanks

Thom

As far as I know the language exists ERROR will not stop the rest of the process
from completing. You may in search of solution to a problem that does not
exist :)

--
Adrian Klaver
aklaver@comcast.net

David Fetter and Andreas Scherbaum also have solutions for this in
reployment scripts:

http://people.planetpostgresql.org/dfetter/index.php?/archives/23-CREATE-OR-REPLACE-LANGUAGE.html
http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
--
-- Christophe Pettus
xof@thebuild.com

2009/11/22 Christophe Pettus <xof@thebuild.com>

David Fetter and Andreas Scherbaum also have solutions for this in
reployment scripts:

http://people.planetpostgresql.org/dfetter/index.php?/archives/23-CREATE-OR-REPLACE-LANGUAGE.html

http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
--
-- Christophe Pettus
xof@thebuild.com

Ah, I think that will work. Thanks :)

Thom

On 23/11/2009 4:15 AM, Scott Marlowe wrote:

On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown@gmail.com> wrote:

Hi,
This should be simple, but for some reason I'm not quite sure what the
solution is. I want to be able to update the value of a column for rows
that have been updated. More specifically, if a row is updated, I want it's
modified_date column to be populated with the current time stamp. I've
looked at triggers and rules, and it looks like I'd need to create a
function just to achieve this which seems incredibly clumsy and unnecessary.
Could someone enlighten me?

Well, you DO have to create a function, but it's not all that clumsy
really. Also it's quite flexible so you can do lots of complex stuff
and hide it away in a trigger function.

I do think this comes up often enough that a built-in trigger "update
named column with result of expression on insert" trigger might be
desirable. Especially if implemented in C to avoid the need for PL/PgSQL
and to reduce the CPU cost a smidge.

Hmm. CC'iing -hackers; there was a discussion earlier on it being
desirable to have more "[EASY]" TODO items, and this might be a good one
for the job.

So might "CREATE LANGUAGE ... IF NOT EXISTS". Maybe even "CREATE ROLE
... IF NOT EXISTS" and "CREATE USER ... IF NOT EXISTS" - I know I'd find
them really handy.

--
Craig Ringer

2009/11/22 Craig Ringer <craig@postnewspapers.com.au>

On 23/11/2009 4:15 AM, Scott Marlowe wrote:

On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown@gmail.com>

wrote:

Hi,
This should be simple, but for some reason I'm not quite sure what the
solution is. I want to be able to update the value of a column for rows
that have been updated. More specifically, if a row is updated, I want

it's

modified_date column to be populated with the current time stamp. I've
looked at triggers and rules, and it looks like I'd need to create a
function just to achieve this which seems incredibly clumsy and

unnecessary.

Could someone enlighten me?

Well, you DO have to create a function, but it's not all that clumsy
really. Also it's quite flexible so you can do lots of complex stuff
and hide it away in a trigger function.

I do think this comes up often enough that a built-in trigger "update
named column with result of expression on insert" trigger might be
desirable. Especially if implemented in C to avoid the need for PL/PgSQL
and to reduce the CPU cost a smidge.

Hmm. CC'iing -hackers; there was a discussion earlier on it being
desirable to have more "[EASY]" TODO items, and this might be a good one
for the job.

So might "CREATE LANGUAGE ... IF NOT EXISTS". Maybe even "CREATE ROLE
... IF NOT EXISTS" and "CREATE USER ... IF NOT EXISTS" - I know I'd find
them really handy.

--
Craig Ringer

I would have thought the IF NOT EXISTS syntax could be handy on every CREATE
command and I wouldn't object to such a thing being implemented in future.

But my reason for the column updating on row update was due to me converting
a MySQL script to PostgreSQL. MySQL had the following syntax available:

`updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP

That's effectively what I'm emulating. I think that syntax is actually
quite useful. Another thing I found useful was having COMMENT available on
the same line as the column declaration too. An example of this is:

`parent_id` integer unsigned NOT NULL default '0' COMMENT 'The parent menu
item in the menu tree.',

I really can't think of any other syntactic sugar I'd want from MySQL
though.

Thom

Craig Ringer <craig@postnewspapers.com.au> writes:

I do think this comes up often enough that a built-in trigger "update
named column with result of expression on insert" trigger might be
desirable.

There's something of the sort in contrib already, I believe, though
it's so old it still uses abstime :-(

So might "CREATE LANGUAGE ... IF NOT EXISTS". Maybe even "CREATE ROLE
... IF NOT EXISTS" and "CREATE USER ... IF NOT EXISTS" - I know I'd find
them really handy.

CREATE IF NOT EXISTS has been proposed and rejected before, more than
once. Please see the archives.

regards, tom lane

MySQL had the following syntax available:
`updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP

I wonder supporting this syntax would speed things up a little bit.
Here's a simple benchmark about the situation we are discussing here:

There are 2 tables:
CREATE TABLE t1 (n integer not null, mtime timestamp with time
zone not null);
CREATE TABLE t2 (n integer not null, mtime timestamp with time
zone not null);

and a trigger for the second one:
CREATE LANGUAGE plpgsql;
CREATE FUNCTION touch() RETURNS trigger AS $$
BEGIN
new.mtime := now();
RETURN new;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER ttt_mtime BEFORE UPDATE or INSERT
ON t2 FOR EACH ROW EXECUTE PROCEDURE touch();

and here's the actual test:

test=> INSERT INTO t1(n,mtime) SELECT *, now() FROM generate_series(1,1000000);
INSERT 0 1000000
Time: 7382.313 ms
test=> INSERT INTO t2(n) SELECT * FROM generate_series(1,1000000);
INSERT 0 1000000
Time: 24541.088 ms

So, updating the column explicitly is 3.5 times faster than the
trigger. My guess is that in real life applications where tables have
"bigger" rows (more columns, data types other than integer), the
overhead of the trigger will be even smaller.

On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Craig Ringer <craig@postnewspapers.com.au> writes:

I do think this comes up often enough that a built-in trigger "update
named column with result of expression on insert" trigger might be
desirable.

There's something of the sort in contrib already, I believe, though
it's so old it still uses abstime :-(

So might "CREATE LANGUAGE ... IF NOT EXISTS". Maybe even "CREATE ROLE
... IF NOT EXISTS" and "CREATE USER ... IF NOT EXISTS" - I know I'd find
them really handy.

CREATE IF NOT EXISTS has been proposed and rejected before, more than
once.  Please see the archives.

Search for CINE to find the discussions. This is a good place to start:

http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php

Despite Tom's assertions to the contrary, I am unable to find a clear
consensus against this feature in the archives, and still think it
would be useful. MySQL and SQLite both support it, at least in the
specific case of CREATE TABLE IF NOT EXISTS. But I've exhausted my
quota of beating my head against a brick wall on this issue.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

CREATE IF NOT EXISTS has been proposed and rejected before, more than
once. �Please see the archives.

Search for CINE to find the discussions. This is a good place to start:
http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php

Despite Tom's assertions to the contrary, I am unable to find a clear
consensus against this feature in the archives,

I think you didn't look back far enough --- that issue was settled years
ago. IIRC the killer argument is that after CINE you do not know the
state of the object: it exists, yes, but what properties has it got?
If it already existed then it's still got its old definition, which
might or might not be what you're expecting.

CREATE OR REPLACE has got far safer semantics from the viewpoint of a
script that wants to bull through without having any actual error
handling (which is more or less the scenario we are arguing here, no?)
After successful execution of the command you know exactly what
properties the object has got.

Whether it would be sensible to have CREATE OR REPLACE semantics for a
language is something I'm not very clear on. It seems like changing any
of the properties of a pg_language entry could be rather fatal from the
viewpoint of an existing function using the language.

[ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among
creation commands in that the common cases have no parameters, at least
not since we added pg_pltemplate. So you could imagine defining CINE
for a language as disallowing any parameters and having these semantics:
* language not present -> create from template
* language present, matches template -> OK, do nothing
* language present, does not match template -> report error
This would meet the objection of not being sure what the state is
after successful execution of the command. It doesn't scale to any
other object type, but is it worth doing for this one type?

regards, tom lane

Tom Lane wrote:

[ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among
creation commands in that the common cases have no parameters, at least
not since we added pg_pltemplate. So you could imagine defining CINE
for a language as disallowing any parameters and having these semantics:
* language not present -> create from template
* language present, matches template -> OK, do nothing
* language present, does not match template -> report error
This would meet the objection of not being sure what the state is
after successful execution of the command. It doesn't scale to any
other object type, but is it worth doing for this one type?

I seriously doubt it. The only reason I could see for such a thing would
be to make it orthogonal with other CINE commands.

Part of the motivation for allowing inline blocks was to allow for
conditional logic. So you can do things like:

DO $$

begin
if not exists (select 1 from pg_tables where schemaname = 'foo'
and tablename = 'bar') then
create table foo.bar (x int, y text);
end if;
end;

$$;

It's a bit more verbose (maybe someone can streamline it) but it does
give you CINE (for whatever flavor of CINE you want), as well as lots
more complex possibilities than we can conceivably build into SQL.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Part of the motivation for allowing inline blocks was to allow for
conditional logic.

I don't think that argument really applies to this case, because the
complaint was about not being sure if plpgsql is installed. If it
isn't, you can hardly use a plpgsql DO block to fix it.

(Is anyone up for revisiting the perennial topic of whether to install
plpgsql by default? Andrew's argument does suggest that DO might offer
a new consideration in that tradeoff.)

regards, tom lane