Dynamic update of a date field

Musial, Jan (GIUB) <jan.musial@giub.unibe.ch> wrote:

Dear all,

I have a question concerning default value/trigger function which
supposed to update/fill field called time_stamp whenever a row is
inserted. Let say that we have a table: CREATE TABLE dummy (year

you can use 'default now()' or an insert-trigger

smallint,month smallint,day smallint,time_stamp date); I would like to

That's silly, use one (and only one) field, timestamp (or timestamptz)
Don't use never ever multiple columns for the same information!

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On Thursday, February 16, 2012 6:05:40 am Musial, Jan (GIUB) wrote:

Dear all,

I have a question concerning default value/trigger function which supposed
to update/fill field called time_stamp whenever a row is inserted. Let say
that we have a table: CREATE TABLE dummy (year smallint,month smallint,day
smallint,time_stamp date); I would like to update "time_stamp" dynamically
without knowledge of a table name and using the values placed in the
columns: year,month,day. The trick is that I have ~2000 tables which I
populate with some time information, so either I could somehow fetch it
to_timestamp() function in the Default definition of the field (while
creating a table) or create a trigger function which doesn't require the
table name (or retrieve it dynamically) and which is executed whenever a
row is added to any table.

As I am new to postgres/plpgsql any suggestions are more than welcome.

I would agree with Andreas, there is no need to replicate the date/time
information across fields. You can pull that information out of a timestamp. If
the timestamp field name is going to be the same on all the tables you can create
a generic function in plpgsl and point a trigger on each table to it. The INSERT
value can be handled by a default value on the field. It is the update you will
need a function for. So something like:

CREATE OR REPLACE FUNCTION public.ts_update()
RETURNS trigger AS

$Body$
BEGIN
new.ts_update:=now();
RETURN NEW;
END;
$Body$
LANGUAGE 'plpgsql' VOLATILE;

and associated trigger

CREATE TRIGGER some_table_ts_update
BEFORE UPDATE
ON some_table
FOR EACH ROW
EXECUTE PROCEDURE public.ts_update()

Thank you in advance,

Jan Musial

--
Adrian Klaver
adrian.klaver@gmail.com

Dear Adrian & Andreas,

Thank you very much for this pieces of advice. I end up with creating a insert-triger function, which does the trick.
As far as the redundant time information within my database is concerned this is an data import issue. Simply it is easier for me to import year,month day separately than create a timestamp and drop the columns.
All the best,

Jan
________________________________________
Von: Adrian Klaver [adrian.klaver@gmail.com]
Gesendet: Donnerstag, 16. Februar 2012 15:56
An: pgsql-general@postgresql.org
Cc: Musial, Jan (GIUB)
Betreff: Re: [GENERAL] Dynamic update of a date field

On Thursday, February 16, 2012 6:05:40 am Musial, Jan (GIUB) wrote:

Dear all,

I have a question concerning default value/trigger function which supposed
to update/fill field called time_stamp whenever a row is inserted. Let say
that we have a table: CREATE TABLE dummy (year smallint,month smallint,day
smallint,time_stamp date); I would like to update "time_stamp" dynamically
without knowledge of a table name and using the values placed in the
columns: year,month,day. The trick is that I have ~2000 tables which I
populate with some time information, so either I could somehow fetch it
to_timestamp() function in the Default definition of the field (while
creating a table) or create a trigger function which doesn't require the
table name (or retrieve it dynamically) and which is executed whenever a
row is added to any table.

As I am new to postgres/plpgsql any suggestions are more than welcome.

I would agree with Andreas, there is no need to replicate the date/time
information across fields. You can pull that information out of a timestamp. If
the timestamp field name is going to be the same on all the tables you can create
a generic function in plpgsl and point a trigger on each table to it. The INSERT
value can be handled by a default value on the field. It is the update you will
need a function for. So something like:

CREATE OR REPLACE FUNCTION public.ts_update()
RETURNS trigger AS

$Body$
BEGIN
new.ts_update:=now();
RETURN NEW;
END;
$Body$
LANGUAGE 'plpgsql' VOLATILE;

and associated trigger

CREATE TRIGGER some_table_ts_update
BEFORE UPDATE
ON some_table
FOR EACH ROW
EXECUTE PROCEDURE public.ts_update()

Thank you in advance,

Jan Musial

--
Adrian Klaver
adrian.klaver@gmail.com

On 2/16/12 7:27 AM, Andreas Kretschmer wrote:

Musial, Jan (GIUB)<jan.musial@giub.unibe.ch> wrote:

smallint,month smallint,day smallint,time_stamp date); I would like to

That's silly, use one (and only one) field, timestamp (or timestamptz)
Don't use never ever multiple columns for the same information!

Would it not be advantageous to replicate information in the above
form if you wanted to, say, get all records in the month of May, and
therefore create an index on the month field? I would think that
would be more efficient than creating a functional index on a timestamp.
And if you're not too picky, that would give an index that couldn't
be done on a timestamptz field, as that's mutable.

-ds

On 02/16/12 2:34 PM, David Salisbury wrote:

Would it not be advantageous to replicate information in the above
form if you wanted to, say, get all records in the month of May, and
therefore create an index on the month field? I would think that
would be more efficient than creating a functional index on a timestamp.
And if you're not too picky, that would give an index that couldn't
be done on a timestamptz field, as that's mutable.

an index on a timestamp will work just fine on date_trunc('month',
timestampfield)

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 02/16/2012 02:45 PM, John R Pierce wrote:

On 02/16/12 2:34 PM, David Salisbury wrote:

Would it not be advantageous to replicate information in the above
form if you wanted to, say, get all records in the month of May, and
therefore create an index on the month field? I would think that
would be more efficient than creating a functional index on a timestamp.
And if you're not too picky, that would give an index that couldn't
be done on a timestamptz field, as that's mutable.

an index on a timestamp will work just fine on date_trunc('month',
timestampfield)

You can try but PostgreSQL will respond:
...functions in index expression must be marked IMMUTABLE...

The current month returned by extract or date_trunc depends on the
current time zone. New York will see March 3-hours ahead of us
left-coasters.

Note: storing the month in a separate field does not solve this problem
- it just shuffles it around and requires additional mechanisms to
update that field when the timestamp field changes.

You can, if it is appropriate to the situation, specify the time zone in
which you are interested:
... (date_trunc('month', timestampfield at time zone
'posix/America/Los_Angeles') ) ...

Cheers,
Steve

On 2012-02-16, David Salisbury <salisbury@globe.gov> wrote:

On 2/16/12 7:27 AM, Andreas Kretschmer wrote:

Musial, Jan (GIUB)<jan.musial@giub.unibe.ch> wrote:

smallint,month smallint,day smallint,time_stamp date); I would like to

That's silly, use one (and only one) field, timestamp (or timestamptz)
Don't use never ever multiple columns for the same information!

Would it not be advantageous to replicate information in the above
form if you wanted to, say, get all records in the month of May, and
therefore create an index on the month field? I would think that
would be more efficient than creating a functional index on a timestamp.
And if you're not too picky, that would give an index that couldn't
be done on a timestamptz field, as that's mutable.

using "at timezone ...." can convert timestamptz to timestamp.
which can then be fragmented immutably for indexing using extract
or to_char.

--
⚂⚃ 100% natural

On 2012-02-17, Steve Crawford <scrawford@pinpointresearch.com> wrote:

On 02/16/2012 02:45 PM, John R Pierce wrote:

On 02/16/12 2:34 PM, David Salisbury wrote:

Would it not be advantageous to replicate information in the above
form if you wanted to, say, get all records in the month of May, and
therefore create an index on the month field? I would think that
would be more efficient than creating a functional index on a timestamp.
And if you're not too picky, that would give an index that couldn't
be done on a timestamptz field, as that's mutable.

an index on a timestamp will work just fine on date_trunc('month',
timestampfield)

You can try but PostgreSQL will respond:
...functions in index expression must be marked IMMUTABLE...

The current month returned by extract or date_trunc depends on the
current time zone. New York will see March 3-hours ahead of us
left-coasters.

David is claiming than a funtional index isnt needed at all, perhaps
postgres knows what date_trunc does and knows how to use an ordinary
btree index. (he could be right)

Date_trunc doesn't get you the month, it gets you the year and month,
date_runc on timestamp or date is immutable and can be indexed upon.

the original post was storing month and day (this information could be
useful for finding records having anniversaries eg: birthdays) it was
not storing year, month and day can be got using extract, the same
rules of mutablilty apply.

Note: storing the month in a separate field does not solve this problem
- it just shuffles it around and requires additional mechanisms to
update that field when the timestamp field changes.

yeah, I can't see any way that storing these values separately is more
efficient, the indexes are the same size, but the data records on disk
are larger. I don't think postgres will re-compute the function result
unless one of the function's inputs hase been changed.

--
⚂⚃ 100% natural