Re: Calculated Fields in Postgres?

From: "Andres Olarte" <olarte(dot)andres(at)gmail(dot)com>
To: "Jeanna Geier" <jgeier(at)apt-cafm(dot)com>
Cc: "Pgsql-Jdbc(at)Postgresql(dot)Org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Calculated Fields in Postgres?
Date: 2007-01-02 22:00:51
Message-ID: 3fccaa690701021400n4311e0a6t5fe08e2768606012@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

This is not a JDBC question, that's a basic SQL question, but any
way... You have some choices:

1- Use a view to show the data from the table plus the calculated fields.
2- Use a trigger to define the value of the calculated field on INSERT.

Which one you use depends on the usage scenario, basically how a
change of the mfg policy would affect you. However, you seem to have
a problem with your table structure, so you should probably read a
little bit about normalization. For example, it might make more sense
to convert everything to days, and forget about the 'Life Units'
field.

On 1/2/07, Jeanna Geier <jgeier(at)apt-cafm(dot)com> wrote:
> Hello List!
>
> Still trying to learn Postgres... =) I have a question on how/where to
> define a calculated field in a table...
>
> Let's say I have a 'Replacement Cost' table with the following fields:
>
> Original Cost
> Original Date
> Life Expectancy
> Life Units
>
> and I want to add a 'Mfg. Replace Date' field, which is a calculated field
> with the following formula:
>
> IF ('Life Units' = NAV)
> THEN (NAV)
> ELSE
> ( IF ('Life Units' = 'Day(s)'')
> THEN (AddDate('Original Date,0,0,'Life Expectancy'))
> ELSE
> ( IF 'Life Units' = 'Month(s)'')
> THEN (AddDate('Original Date',0,'Life Expectancy',0))
> ELSE
> ( IF 'Life Units' = 'Year(s)'')
> THEN (AddDate('Original Date','Life Expectancy',0.0))
> ELSE
> (today))))
>
> --------------------------------------------------------
>
> How/where do I define this calculated field?
>
> Any help or guidance that anyone could provide will be greatly appreciated!
>
> If any more information is necessary from my end, please let me know.
>
> Thanks,
> -Jeanna
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Siegmar 2007-01-04 13:39:12 Re: postgres PreparedStatement commented ? problem PSQLException:
Previous Message Jeanna Geier 2007-01-02 21:47:50 Calculated Fields in Postgres?