Thread: 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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Oct 21, 8:54 pm, da...@fetter.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...@fetter.org>http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > > Remember to vote! > Consider donating to Postgres:http://www.postgresql.org/about/donate > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.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@gmail.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...@fetter.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...@fetter.org>http://fetter.org/ >> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter >> Skype: davidfetter XMPP: david.fet...@gmail.com >> >> Remember to vote! >> Consider donating to Postgres:http://www.postgresql.org/about/donate >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.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...@postnewspapers.com.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...@postgresql.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@gmail.com> wrote: > On Oct 23, 5:10 am, cr...@postnewspapers.com.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...@postgresql.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@gmail.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