Re: auto-filling a field on insert

If I have a table with fields A, B, C and D how could I do something
such that if I insert values in fields A, B and C field D would be
auto-filled with the value A||B||C? Just a pointer of where to begin
looking is fine. Triggers? Copy ... From? Table or field definitions?

thx

On Wed, Oct 21, 2009 at 07:17:44PM -0700, semi-ambivalent wrote:
> If I have a table with fields A, B, C and D how could I do something
> such that if I insert values in fields A, B and C field D would be
> auto-filled with the value A||B||C? Just a pointer of where to begin
> looking is fine. Triggers? Copy ... From? Table or field
> definitions?

Instead of populating your table with this extra information, create a
VIEW with what you want in it.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Oct 21, 8:54 pm, da(dot)(dot)(dot)(at)fetter(dot)org (David Fetter) wrote:
> On Wed, Oct 21, 2009 at 07:17:44PM -0700, semi-ambivalent wrote:
> > If I have a table with fields A, B, C and D how could I do something
> > such that if I insert values in fields A, B and C field D would be
> > auto-filled with the value A||B||C? Just a pointer of where to begin
> > looking is fine. Triggers? Copy ... From? Table or field
> > definitions?
>
> Instead of populating your table with this extra information, create a
> VIEW with what you want in it.
>
> Cheers,
> David.
> --
> David Fetter <da(dot)(dot)(dot)(at)fetter(dot)org>http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fet(dot)(dot)(dot)(at)gmail(dot)com
>
> Remember to vote!
> Consider donating to Postgres:http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

At first blush that looks good but I need an index on that concatted
value and I don't seem to be able to index a field in a view. I'll
check the docs on views to see if I'm missing something.

thanks!

sa

On Wed, Oct 21, 2009 at 8:54 PM, semi-ambivalent <thefronny(at)gmail(dot)com> wrote:
> At first blush that looks good but I need an index on that concatted
> value and I don't seem to be able to index a field in a view. I'll
> check the docs on views to see if I'm missing something.

To create an index on a computed value you use an "index on
expression". Here are more details on how to do it:

http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html

--
Regards,
Richard Broersma Jr.

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

semi-ambivalent wrote:
> On Oct 21, 8:54 pm, da(dot)(dot)(dot)(at)fetter(dot)org (David Fetter) wrote:
>> On Wed, Oct 21, 2009 at 07:17:44PM -0700, semi-ambivalent wrote:
>>> If I have a table with fields A, B, C and D how could I do something
>>> such that if I insert values in fields A, B and C field D would be
>>> auto-filled with the value A||B||C? Just a pointer of where to begin
>>> looking is fine. Triggers? Copy ... From? Table or field
>>> definitions?
>> Instead of populating your table with this extra information, create a
>> VIEW with what you want in it.
>>
>> Cheers,
>> David.
>> --
>> David Fetter <da(dot)(dot)(dot)(at)fetter(dot)org>http://fetter.org/
>> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
>> Skype: davidfetter XMPP: david(dot)fet(dot)(dot)(dot)(at)gmail(dot)com
>>
>> Remember to vote!
>> Consider donating to Postgres:http://www.postgresql.org/about/donate
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
>> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>
> At first blush that looks good but I need an index on that concatted
> value and I don't seem to be able to index a field in a view. I'll
> check the docs on views to see if I'm missing something.

How about an index on the three "fields"?

http://www.postgresql.org/docs/8.4/static/sql-createindex.html

\\||/
Rod
--

semi-ambivalent wrote:

> At first blush that looks good but I need an index on that concatted
> value and I don't seem to be able to index a field in a view. I'll
> check the docs on views to see if I'm missing something.

As others have noted, a multi-field index or a functional index is
probably the best option.

If you decide for some reason that you really, really need the
concatenated fields stored in the table its self, you will need to use a
BEFORE INSERT ... FOR EACH ROW trigger to populate field `D'.

--
Craig Ringer

On Oct 23, 5:10 am, cr(dot)(dot)(dot)(at)postnewspapers(dot)com(dot)au (Craig Ringer) wrote:
> semi-ambivalent wrote:
> > At first blush that looks good but I need an index on that concatted
> > value and I don't seem to be able to index a field in a view. I'll
> > check the docs on views to see if I'm missing something.
>
> As others have noted, a multi-field index or a functional index is
> probably the best option.
>
> If you decide for some reason that you really, really need the
> concatenated fields stored in the table its self, you will need to use a
> BEFORE INSERT ... FOR EACH ROW trigger to populate field `D'.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Thanks everyone for the pointers. I like the idea of getting rid of
the concatenated field even though it reduced the worst case query
time to one tenth of what I had been seeing. But for now I'm going to
keep it there because I'm ignorant about triggers so this will be a
good opportunity to learn about them before I drop the column for
something more efficient, assuming there is.

sa

On Fri, Oct 23, 2009 at 9:32 PM, semi-ambivalent <thefronny(at)gmail(dot)com> wrote:
> On Oct 23, 5:10 am, cr(dot)(dot)(dot)(at)postnewspapers(dot)com(dot)au (Craig Ringer) wrote:
>> semi-ambivalent wrote:
>> > At first blush that looks good but I need an index on that concatted
>> > value and I don't seem to be able to index a field in a view. I'll
>> > check the docs on views to see if I'm missing something.
>>
>> As others have noted, a multi-field index or a functional index is
>> probably the best option.
>>
>> If you decide for some reason that you really, really need the
>> concatenated fields stored in the table its self, you will need to use a
>> BEFORE INSERT ... FOR EACH ROW trigger to populate field `D'.
>>
>> --
>> Craig Ringer
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
>> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>
> Thanks everyone for the pointers. I like the idea of getting rid of
> the concatenated field even though it reduced the worst case query
> time to one tenth of what I had been seeing. But for now I'm going to
> keep it there because I'm ignorant about triggers so this will be a
> good opportunity to learn about them before I drop the column for
> something more efficient, assuming there is.

The multi column index should give you equivalent speed.

Scott Marlowe wrote:
> On Fri, Oct 23, 2009 at 9:32 PM, semi-ambivalent <thefronny(at)gmail(dot)com> wrote:
>> Thanks everyone for the pointers. I like the idea of getting rid of
>> the concatenated field even though it reduced the worst case query
>> time to one tenth of what I had been seeing. But for now I'm going to
>> keep it there because I'm ignorant about triggers so this will be a
>> good opportunity to learn about them before I drop the column for
>> something more efficient, assuming there is.
>
> The multi column index should give you equivalent speed.

In fact, it may well give you significantly superior speed, particularly
if at least one of the columns always has a value specified for it in a
query. Put that column first in the index column list and you'll only
have to search for the other two values in the subset of the index that
matches the first value. This could be a *lot* faster than a full index
scan on your concatenated field.

A trigger isn't necessary or desirable as a solution to this problem -
you'd only need a trigger if you really want to have a "real"
concatenated column. A multi-column index is almost certainly the best
choice in this situation. A functional index can be used in situations
where a multi-column index doesn't apply, or where you need something
more complex, but here a multi-column index looks just ideal.

--
Craig Ringer