Thread: column default dependant on another columns value

Hi list,
Given a table with columns seconds and minutes, how can I have minutes be
computed automatically at the insert statement?
I tried:
ALTER TABLE table1 ALTER COLUMN minutes SET default (seconds/60);
Postgres' answer was:
ERROR:  cannot use column references in default expression
So I gave rules a look but it seems rules apply to the entire row.
CREATE RULE "my_rule" AS ON 
INSERT TO table1
WHERE minutes is null
DO INSTEAD 
INSERT INTO table1 (column1, column2, seconds, minutes) 
VALUES(new.column1, new.column2, new.seconds, new.seconds/60);
Is this correct? Is there another (better/simpler) way to achieve this?
Regards,
Fernando

On Tue, Jul 1, 2008 at 1:12 PM, Fernando Hevia <fhevia@ip-tel.com.ar> wrote:

> Given a table with columns seconds and minutes, how can I have minutes be
> computed automatically at the insert statement?

It is possible to do this with a trigger or a rule.  A trigger would
be more robust.

> Is this correct? Is there another (better/simpler) way to achieve this?

Well I might work, but it is a bad practice to get into since what you
are trying to do violates the rules of database normalization.

Wouldn't it be better to calculate the minutes with you query your table?

SELECT *, seconds / 60 AS minutes FROM yourtable;


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

> -----Mensaje original-----
> De: Richard Broersma [mailto:richard.broersma@gmail.com] 
> 
> It is possible to do this with a trigger or a rule.  A 
> trigger would be more robust.
> 
> > Is this correct? Is there another (better/simpler) way to 
> achieve this?
> 
> Well I might work, but it is a bad practice to get into since 
> what you are trying to do violates the rules of database 
> normalization.
> 
> Wouldn't it be better to calculate the minutes with you query 
> your table?
> 
> SELECT *, seconds / 60 AS minutes
>   FROM yourtable;

Actually I only used this as an example.
The real table is queried lots of times for millions of rows and the server
is showing some high-level user cpu consumption. There are a couple
calculated columns on the table so I am trying to reduce cpu usage by
pre-calculating the more cpu intensive data once on insert. Enhancing the
application is currently not possible.

Anyway, the rule didn't work. Got "an infinite recursion error" when
inserting on the table.
Can't figure out where the recursion is as supposedly the rule kicks in when
the "where minutes is null" condition is satisfied. The DO INSTEAD part runs
an insert were minutes is NOT null so the rule should be ignored. 
Where is the recursion then? I am on postgres 8.2.9.

Thanks for your hindsight Richard. I Will look into the trigger solution.
Still, I'd like to understand this recursion error.

Regards,
Fernando.

"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> Anyway, the rule didn't work. Got "an infinite recursion error" when
> inserting on the table.
> Can't figure out where the recursion is

You didn't show us the rule, but I imagine that you think the WHERE
clause is applied while expanding the rule.  It's not, it can only
suppress rows at run-time; and what you've got is infinite macro
expansion recursion.
        regards, tom lane

 

> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org 
> [mailto:pgsql-sql-owner@postgresql.org] En nombre de Tom Lane
> Enviado el: Martes, 01 de Julio de 2008 19:24
> Para: Fernando Hevia
> CC: 'Richard Broersma'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] column default dependant on another columns value 
> 
> "Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> > Anyway, the rule didn't work. Got "an infinite recursion 
> error" when 
> > inserting on the table.
> > Can't figure out where the recursion is
> 
> You didn't show us the rule, but I imagine that you think the 
> WHERE clause is applied while expanding the rule.  It's not, 
> it can only suppress rows at run-time; and what you've got is 
> infinite macro expansion recursion.
> 

I see. In that case rules do not serve this particular purpose. 
It seems a trigger should be the tool for solving this.

Just to confirm, this is my test case:

create table table1 (  column1 text,  seconds integer,  minutes integer );

CREATE RULE "my_rule" AS ON
INSERT TO table1
WHERE minutes is null
DO INSTEAD
INSERT INTO table1 (column1, seconds, minutes) VALUES(new.column1,
new.seconds, new.seconds/60);

insert into table1 values ('a', 60);         --- Here the rule should kick
in right?
insert into table1 values ('b', 120, NULL);  --- Rule should kick in too.
insert into table1 values ('c', 180, 3);     --- the rule should not apply
since minutes is not null.

Of course, all three of them throw the recursion error.