Thread: Appending values non-destructively
Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the UPDATE function to add values to a field in a row that already has a value or values, i.e.: in a field named 'sponsor', the current value is '2588'. I would like the new value in 'sponsor' to become '2588, 2961', and later , '2588, 2961, 3166' etc. From my newbie reading, I haven't discovered whether it is possible to append values to a field without destroying the current value, and further, whether comma separators (or any sprators for that fact) are allowed or even possible. If some kind guru could help out with this simple question, I would greatly appreciate a small example code showing how to do it. Tia, Andre -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/
Well sure you can do that but there isn't anything special about a delimiter. In general the value is atomic and there isn't anything smaller than it. You could probably use a CASE expression to append new data to the value or start the value. It almost sounds like you might want to check out the array type and work with *that* instead since that at least is an atomic collection of values. This isn't a case of being destructive, you just have to include the previous value in the new value you write. That's just standard stuff. Joshua b. Jore http://www.greentechnologist.org On Wed, 8 May 2002, Andre Dubuc wrote: > Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the UPDATE function > to add values to a field in a row that already has a value or values, i.e.: > in a field named 'sponsor', the current value is '2588'. I would like the new > value in 'sponsor' to become '2588, 2961', and later , '2588, 2961, 3166' etc. > > >From my newbie reading, I haven't discovered whether it is possible to append > values to a field without destroying the current value, and further, whether > comma separators (or any sprators for that fact) are allowed or even possible. > > If some kind guru could help out with this simple question, I would greatly > appreciate a small example code showing how to do it. > > Tia, > Andre > > > -- > Please pray the Holy Rosary to end the holocaust of abortion. > Remember in your prayers the Holy Souls in Purgatory. > > May God bless you abundantly in His love! > For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/ > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Hello,
This sounds like a misunderstanding of how relational databases
work.
You have given no idea of the current schema but assuming that you have a
table with sponsor currently in it which we'll call tbl_a. We'll also assume
it has a primary key called pk_a. What you could do is create another table
as follows:
CREATE tbl_b (
pk_b SERIAL,
sponsor int4,
pk_a int4,
PRIMARY KEY (pk_b)
);
This table will have a 0 to n relationship with tbl_a by way of pk_a.
If you want you could use foriegn keys to enforce a relationship.
When ever you want to change the sponsor field you just INSERT another value
into this table (eg INSERT (sponsor,pk_a) VALUES (sponsor_id,<tbl_a's
pk_a>);
You can tell which is the currrent sponsor as this will have the highest
value of pk_b.
Alternatively you could UPDATE the record in sponsor, while either INSERTing
the last value into sponsor, or alternatively the new value (which would
require you to do this on INSERTS into tbl_a as well). This approach has the
advantage of speedier queries to find the current sponsor. Also rather than
having the application doing the updates you could use triggers to
automatically do the INSERTS into tbl_b.
Of course if you are infact just wanting a text representation of these
numbers you could just do something like:
UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE ....
Hope this helps,
- Stuart
> -----Original Message-----
> From: Andre Dubuc [mailto:aajdubuc@webhart.net]
>
> Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the
> UPDATE function
> to add values to a field in a row that already has a value or
> values, i.e.:
> in a field named 'sponsor', the current value is '2588'. I
> would like the new
> value in 'sponsor' to become '2588, 2961', and later , '2588,
> 2961, 3166' etc.
>
> From my newbie reading, I haven't discovered whether it is
> possible to append
> values to a field without destroying the current value, and
> further, whether
> comma separators (or any sprators for that fact) are allowed
> or even possible.
>
> If some kind guru could help out with this simple question, I
> would greatly
> appreciate a small example code showing how to do it.
>
> Tia,
> Andre
> -----Original Message-----
> From: Andre Dubuc [mailto:aajdubuc@webhart.net]
>
> Thanks Stuart,
>
> The test idea sounds like the way I want to go, but I've
> already set -up the
> table so that the field "sponsor" is int4. Would your idea
> still work?
>
> My current setup is with two tables: "rap" (that has the
> primary key) and
> "sponsor" (that has secondary key in relation to "rap"). I
> didn't bother
> including this info, since the question I'm dealing with
> concerns only a
> particular field in the secondary that may (only
> incidentally) have impact on
> the primary table.
It is relevant as to know exactly what you are wanting to do with this data
so that an appropriate strategy can be suggested. Finding that you can't do
what you want could mean that your relational model is incorrect in some
way. Of course an email saying everything about everything would be a bit
much to wade through :). However the schema for table would be a help, as
well as knowing what this data represents and what queries are intended for
it. :)
>
> I suppose my question was more generic: how to append say,
> numeric values in
> an 'int4' field without destroying the current values. When I
> tried something
> like:
>
> UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE ....
This would only work with a text field, varchar or char and it should have
been:
UPDATE tbl_name SET sponsor=sponsor || ', 567' WHERE ....
The double quotes came from me working on plpgsql to much.....
>
> I got an error message near the separator; so, my question . . .
>
> I gather I can use the "," separator before the number I wish to add?
>
> Terribly sorry if I'm not very coherent in asking the
> question -- I've left
> this problem toward the end of my database website design. My
> mind is sort of
> blotto.
>
> Thanks for the quick response,
> Andre
If you are using it to reference another table then this won't work (not
without parsing overhead anyway...)
One question is how you are wanting to use this information. If you just
insert the new values into the sponsor table rather than updating you can do
a left join something like this:
SELECT * FROM rap LEFT JOIN sponsor on rap.pk=sponsor.rap_pk_refernce;
This will return a row for each sponsor, which is probably what you want if
each rap can have more than one sponsor
If however you are just wanting historical information and are not wanting
everything every time you query then do something like the following:
(Not knoeing your actual field names, I've made them up):
CREATE TABLE sponsor_history (
pk SERIAL,
sponsor_id int4,
rap_pk int4,
dt timstamp DEFAULT now(),
PRIMARY KEY (pk)
);
CREATE RULE spnsr_updt_rl AS ON UPDATE TO sponsor
WHERE OLD.sponsor_id<>NEW.sponsor_id OR OLD.rap_pk<>NEW.rap_pk
OR NEW.sponsor IS NULL AND NOT OLD.sponsor_id IS NULL
OR NEW.rap_pk IS NULL AND NOT OLD.rap_pk IS NULL
DO
INSERT INTO sponsor_history (sponsor_id,rap_pk) VALUES
(OLD.sponsor_id,NEW.sponsor_id);
CREATE RULE spnsr_updt_rl AS ON DELETE TO sponsor
DO
INSERT INTO sponsor_history (sponsor_id,rap_pk) VALUES
(OLD.sponsor_id,NEW.sponsor_id);
This will keep a timestamped history of when sponsor become replaced or
deleted.
Hope this helps,
- Stuart
P.S. I'm including the mailing list so that this will end up searchable in
the archives if any one has a similar problem in the future,& in case
someone else is able to over better/different advice.
>
> On Wednesday 08 May 2002 06:58 am, you wrote:
> > Hello,
> > This sounds like a misunderstanding of how relational databases
> > work.
> > You have given no idea of the current schema but assuming
> that you have a
> > table with sponsor currently in it which we'll call tbl_a.
> We'll also
> > assume it has a primary key called pk_a. What you could do is create
> > another table as follows:
> > CREATE tbl_b (
> > pk_b SERIAL,
> > sponsor int4,
> > pk_a int4,
> > PRIMARY KEY (pk_b)
> > );
> > This table will have a 0 to n relationship with tbl_a by
> way of pk_a.
> > If you want you could use foriegn keys to enforce a relationship.
> > When ever you want to change the sponsor field you just
> INSERT another
> > value into this table (eg INSERT (sponsor,pk_a) VALUES
> (sponsor_id,<tbl_a's
> > pk_a>);
> > You can tell which is the currrent sponsor as this will
> have the highest
> > value of pk_b.
> > Alternatively you could UPDATE the record in sponsor, while either
> > INSERTing the last value into sponsor, or alternatively the
> new value
> > (which would require you to do this on INSERTS into tbl_a
> as well). This
> > approach has the advantage of speedier queries to find the
> current sponsor.
> > Also rather than having the application doing the updates
> you could use
> > triggers to automatically do the INSERTS into tbl_b.
> > Of course if you are infact just wanting a text
> representation of these
> > numbers you could just do something like:
> > UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE ....
> > Hope this helps,
> > - Stuart
> >
> > > -----Original Message-----
> > > From: Andre Dubuc [mailto:aajdubuc@webhart.net]
> > >
> > > Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the
> > > UPDATE function
> > > to add values to a field in a row that already has a value or
> > > values, i.e.:
> > > in a field named 'sponsor', the current value is '2588'. I
> > > would like the new
> > > value in 'sponsor' to become '2588, 2961', and later , '2588,
> > > 2961, 3166' etc.
> > >
> > > From my newbie reading, I haven't discovered whether it is
> > > possible to append
> > > values to a field without destroying the current value, and
> > > further, whether
> > > comma separators (or any sprators for that fact) are allowed
> > > or even possible.
> > >
> > > If some kind guru could help out with this simple question, I
> > > would greatly
> > > appreciate a small example code showing how to do it.
> > >
> > > Tia,
> > > Andre
>
> --
> Please pray the Holy Rosary to end the holocaust of abortion.
> Remember in your prayers the Holy Souls in Purgatory.
>
> May God bless you abundantly in His love!
> For a free Cenacle Scriptural Rosary Booklet:
http://www.webhart.net/csrb/
Thanks Stuart, That's a good chunk of info to digest! :> Unfortunately, I've spent minimal time learning PostgreSQl -- I've been so busycoding PHP and I've used only rudimentary functions such as SELECT, UPDATE in accessing the db. Now that I'm nearly finished the coding, I'll have to go back and see if I code simpler and more securely. For example, I've used SELECT table for authentication of username & password. I've read somewhere that the use of VIEW mightbe more secure, but I needed to complete the basic website structure first. You were correct in assuming: "SELECT * FROM rap LEFT JOIN sponsor on rap.pk=sponsor.rap_pk_refernce; > This will return a row for each sponsor, which is probably what you want if > each rap can have more than one sponsor" Strangely, the way it's set up now, it does this now without the whole history of sponsors displayed for the 'rap' person.I'm going to try what you've suggested and see what comes of it. Btw, am I supposed to 'snip' some of this message -- it's getting awfully long! Regards, Andre On Wednesday 08 May 2002 12:35 pm, you wrote: > > -----Original Message----- > > From: Andre Dubuc [mailto:aajdubuc@webhart.net] > > > > Thanks Stuart, > > > > The test idea sounds like the way I want to go, but I've > > already set -up the > > table so that the field "sponsor" is int4. Would your idea > > still work? > > > > My current setup is with two tables: "rap" (that has the > > primary key) and > > "sponsor" (that has secondary key in relation to "rap"). I > > didn't bother > > including this info, since the question I'm dealing with > > concerns only a > > particular field in the secondary that may (only > > incidentally) have impact on > > the primary table. > > It is relevant as to know exactly what you are wanting to do with this data > so that an appropriate strategy can be suggested. Finding that you can't do > what you want could mean that your relational model is incorrect in some > way. Of course an email saying everything about everything would be a bit > much to wade through :). However the schema for table would be a help, as > well as knowing what this data represents and what queries are intended for > it. :) > > > I suppose my question was more generic: how to append say, > > numeric values in > > an 'int4' field without destroying the current values. When I > > tried something > > like: > > > > UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE .... > > This would only work with a text field, varchar or char and it should have > been: > UPDATE tbl_name SET sponsor=sponsor || ', 567' WHERE .... > The double quotes came from me working on plpgsql to much..... > > > I got an error message near the separator; so, my question . . . > > > > I gather I can use the "," separator before the number I wish to add? > > > > Terribly sorry if I'm not very coherent in asking the > > question -- I've left > > this problem toward the end of my database website design. My > > mind is sort of > > blotto. > > > > Thanks for the quick response, > > Andre > > If you are using it to reference another table then this won't work (not > without parsing overhead anyway...) > One question is how you are wanting to use this information. If you just > insert the new values into the sponsor table rather than updating you can > do a left join something like this: > SELECT * FROM rap LEFT JOIN sponsor on rap.pk=sponsor.rap_pk_refernce; > This will return a row for each sponsor, which is probably what you want if > each rap can have more than one sponsor > > If however you are just wanting historical information and are not wanting > everything every time you query then do something like the following: > (Not knoeing your actual field names, I've made them up): > CREATE TABLE sponsor_history ( > pk SERIAL, > sponsor_id int4, > rap_pk int4, > dt timstamp DEFAULT now(), > PRIMARY KEY (pk) > ); > > CREATE RULE spnsr_updt_rl AS ON UPDATE TO sponsor > WHERE OLD.sponsor_id<>NEW.sponsor_id OR OLD.rap_pk<>NEW.rap_pk > OR NEW.sponsor IS NULL AND NOT OLD.sponsor_id IS NULL > OR NEW.rap_pk IS NULL AND NOT OLD.rap_pk IS NULL > DO > INSERT INTO sponsor_history (sponsor_id,rap_pk) VALUES > (OLD.sponsor_id,NEW.sponsor_id); > > CREATE RULE spnsr_updt_rl AS ON DELETE TO sponsor > DO > INSERT INTO sponsor_history (sponsor_id,rap_pk) VALUES > (OLD.sponsor_id,NEW.sponsor_id); > > This will keep a timestamped history of when sponsor become replaced or > deleted. > Hope this helps, > - Stuart > P.S. I'm including the mailing list so that this will end up searchable in > the archives if any one has a similar problem in the future,& in case > someone else is able to over better/different advice. > > > On Wednesday 08 May 2002 06:58 am, you wrote: > > > Hello, > > > This sounds like a misunderstanding of how relational databases > > > work. > > > You have given no idea of the current schema but assuming > > > > that you have a > > > > > table with sponsor currently in it which we'll call tbl_a. > > > > We'll also > > > > > assume it has a primary key called pk_a. What you could do is create > > > another table as follows: > > > CREATE tbl_b ( > > > pk_b SERIAL, > > > sponsor int4, > > > pk_a int4, > > > PRIMARY KEY (pk_b) > > > ); > > > This table will have a 0 to n relationship with tbl_a by > > > > way of pk_a. > > > > > If you want you could use foriegn keys to enforce a relationship. > > > When ever you want to change the sponsor field you just > > > > INSERT another > > > > > value into this table (eg INSERT (sponsor,pk_a) VALUES > > > > (sponsor_id,<tbl_a's > > > > > pk_a>); > > > You can tell which is the currrent sponsor as this will > > > > have the highest > > > > > value of pk_b. > > > Alternatively you could UPDATE the record in sponsor, while either > > > INSERTing the last value into sponsor, or alternatively the > > > > new value > > > > > (which would require you to do this on INSERTS into tbl_a > > > > as well). This > > > > > approach has the advantage of speedier queries to find the > > > > current sponsor. > > > > > Also rather than having the application doing the updates > > > > you could use > > > > > triggers to automatically do the INSERTS into tbl_b. > > > Of course if you are infact just wanting a text > > > > representation of these > > > > > numbers you could just do something like: > > > UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE .... > > > Hope this helps, > > > - Stuart > > > > > > > -----Original Message----- > > > > From: Andre Dubuc [mailto:aajdubuc@webhart.net] > > > > > > > > Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the > > > > UPDATE function > > > > to add values to a field in a row that already has a value or > > > > values, i.e.: > > > > in a field named 'sponsor', the current value is '2588'. I > > > > would like the new > > > > value in 'sponsor' to become '2588, 2961', and later , '2588, > > > > 2961, 3166' etc. > > > > > > > > From my newbie reading, I haven't discovered whether it is > > > > possible to append > > > > values to a field without destroying the current value, and > > > > further, whether > > > > comma separators (or any sprators for that fact) are allowed > > > > or even possible. > > > > > > > > If some kind guru could help out with this simple question, I > > > > would greatly > > > > appreciate a small example code showing how to do it. > > > > > > > > Tia, > > > > Andre > > > > -- > > Please pray the Holy Rosary to end the holocaust of abortion. > > Remember in your prayers the Holy Souls in Purgatory. > > > > May God bless you abundantly in His love! > > For a free Cenacle Scriptural Rosary Booklet: > > http://www.webhart.net/csrb/ -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/
Thanks Joshua, I think I have more reading to do: I've sort of 'avoided' arrays and triggers, but reading what you're suggesting (and Stuart'sideas), I think I'll need to bone up on these areas. Great fun -- in the last month I've been learning PHP, so I guess it's Postgresql's turn now! Regards, Andre On Wednesday 08 May 2002 10:08 am, you wrote: > Well sure you can do that but there isn't anything special about a > delimiter. In general the value is atomic and there isn't anything smaller > than it. You could probably use a CASE expression to append new data to > the value or start the value. It almost sounds like you might want to > check out the array type and work with *that* instead since that at least > is an atomic collection of values. This isn't a case of being destructive, > you just have to include the previous value in the new value you write. > That's just standard stuff. > > Joshua b. Jore > http://www.greentechnologist.org > > On Wed, 8 May 2002, Andre Dubuc wrote: > > Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the UPDATE > > function to add values to a field in a row that already has a value or > > values, i.e.: in a field named 'sponsor', the current value is '2588'. I > > would like the new value in 'sponsor' to become '2588, 2961', and later , > > '2588, 2961, 3166' etc. > > > > >From my newbie reading, I haven't discovered whether it is possible to > > > append > > > > values to a field without destroying the current value, and further, > > whether comma separators (or any sprators for that fact) are allowed or > > even possible. > > > > If some kind guru could help out with this simple question, I would > > greatly appreciate a small example code showing how to do it. > > > > Tia, > > Andre > > > > > > -- > > Please pray the Holy Rosary to end the holocaust of abortion. > > Remember in your prayers the Holy Souls in Purgatory. > > > > May God bless you abundantly in His love! > > For a free Cenacle Scriptural Rosary Booklet: > > http://www.webhart.net/csrb/ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/
Well actually... you'll probably do better to start with normal single-value values before using arrays. Start simple (and there is *tonnes* of stuff that you can do prior to arrays) Joshua b. Jore http://www.greentechnologist.org On Wed, 8 May 2002, Andre Dubuc wrote: > Thanks Joshua, > > I think I have more reading to do: I've sort of 'avoided' arrays and triggers, but reading what you're suggesting (andStuart's ideas), I think I'll need to bone up on these areas. > > Great fun -- in the last month I've been learning PHP, so I guess it's Postgresql's turn now! > > Regards, > Andre > > > On Wednesday 08 May 2002 10:08 am, you wrote: > > Well sure you can do that but there isn't anything special about a > > delimiter. In general the value is atomic and there isn't anything smaller > > than it. You could probably use a CASE expression to append new data to > > the value or start the value. It almost sounds like you might want to > > check out the array type and work with *that* instead since that at least > > is an atomic collection of values. This isn't a case of being destructive, > > you just have to include the previous value in the new value you write. > > That's just standard stuff. > > > > Joshua b. Jore > > http://www.greentechnologist.org > > > > On Wed, 8 May 2002, Andre Dubuc wrote: > > > Using PostgreSQL 7.2 with PHP 4.1.2, I would like to use the UPDATE > > > function to add values to a field in a row that already has a value or > > > values, i.e.: in a field named 'sponsor', the current value is '2588'. I > > > would like the new value in 'sponsor' to become '2588, 2961', and later , > > > '2588, 2961, 3166' etc. > > > > > > >From my newbie reading, I haven't discovered whether it is possible to > > > > append > > > > > > values to a field without destroying the current value, and further, > > > whether comma separators (or any sprators for that fact) are allowed or > > > even possible. > > > > > > If some kind guru could help out with this simple question, I would > > > greatly appreciate a small example code showing how to do it. > > > > > > Tia, > > > Andre > > > > > > > > > -- > > > Please pray the Holy Rosary to end the holocaust of abortion. > > > Remember in your prayers the Holy Souls in Purgatory. > > > > > > May God bless you abundantly in His love! > > > For a free Cenacle Scriptural Rosary Booklet: > > > http://www.webhart.net/csrb/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- > Please pray the Holy Rosary to end the holocaust of abortion. > Remember in your prayers the Holy Souls in Purgatory. > > May God bless you abundantly in His love! > For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >