Re: [GENERAL] Insert or Update a path from textbox...
On 31 May 2010, at 23:27, wrote: > Hi, > > I'm just try to insert or update an actually table with Microsoft .NET > platform VS2005. > > The problem is that de "\" dissapear when I make the insert or Update. Postgres is interpreting those backslashes as escape characters. Either escape them or turn on standard_conforming_strings. That said, if you're having this problem your queries are probably vulnerable to SQL injection too, they're certainly not parameterised or Postgres would have done the escaping for you. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c04394a10151125916774! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Insert or Update a path from textbox...
Hi, I'm just try to insert or update an actually table with Microsoft .NET platform VS2005. The problem is that de "\" dissapear when I make the insert or Update. If i debug the object has all detailed path...so Why is not saved on the table. The type of column is character (100). So: I have: C:\EVOL\Carga\ I obtain on table of the postgresql: c:EVOLCarga. Is an X-File? If anybody can has any idea... Thanks. David Catasús
Re: [GENERAL] INSERT or UPDATE
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of James B. Byrne > Sent: Monday, April 06, 2009 1:46 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] INSERT or UPDATE > > I have spent the last couple of days reading up on SQL, of which I > know very little, and PL/pgSQl, of which I know less. I am trying > to decide how best to approach the following requirement. > > Given a legal name and a common name and associated details, we wish > to insert this information into a table, entities. As well, we > believe it useful to allow a specific entity more than one common > name. So, at the moment we are considering having another table, > identifiers, that takes entity_id = entity.id (synthetic sequenced > PK for entities), the identifier_type (always 'AKNA' for this > collection of identifiers) and identifier_value = > entity.common_name. > > This seems straight forward enough when initially inserting an > entity. However, it is conceivable that over the lifetime of the > system a particular entity might change its common name. For example > the former "John Tash Enterprises" might become popularly known as > "JTE Inc." while the legal name remains unchanged. > > When we update the entity record and set the common_name = "JTE > Inc." then we need insert an identifier row to match. However, > identifiers for a given entity can be maintained separately from the > entity itself. It is therefore possible, indeed likely, that the > identifier "JTE Inc." for that entity already exists. Likely, but > not certain. In any case, the old identifier row remains unchanged > after the new is inserted. > > The issue then is how to determine on an UPDATE entities whether it > is necessary to INSERT a new identifier using values provided from > the entities row. > > From what I have gathered, what one does is simply insert the new > identifiers row. If there is a primary key conflict then the update > fails, which the function handles gracefully. If not, then it > succeeds. > > I have also formed the opinion that what one does is write a > function or functions, such as fn_aknau(entity_id, name), and tie > these with triggers to the appropriate actions on entities such as: > > CREATE TRIGGER tr_entities_aioru AFTER INSERT OR UPDATE > ON entities >FOR EACH ROW >EXECUTE fn_aknai(entities.id, 'AKNA', entities.common_name); > > Is my appreciation correct or am I missing the mark entirely? Is > this considered the proper place and means to accomplish this sort > of task in an RDBMS? Does it belong elsewhere? Am I correct in > inferring that the values in the columns id and common_name will be > those of entities AFTER the insert or update and that these will be > available to the body of the function? Is the trigger dependent > upon a SUCCESSFUL INSERT or UPDATE of entities or will it be called > regardless? Must the function be written in PL/pgSQl (or similar PL) > or could this function be written in straight SQL? Should it be > straight SQL if possible? What should the function return, if > anything? > > Fairly basic stuff I am sure but somewhat mystifying for me at the > moment. Any help would be appreciated. It is a difficult question. For instance, there are many possibilities when a collision occurs. I guess that for some collisions, sharing the name is OK. Consider two different fictional companies (hopefully in different domains): Jet Propulsion Industries Incorporated == JPI Inc. (makes jet engines) Journey Protection Investments Inc. == JPI Inc. (underwrites travel insurance) Probably, they don't have a legal battle because they have completely different domains. So it seems OK for both companies to relate to this entity if it is only used as a label. On the other hand, you may have a typographical error on data entry for a computer firm. If you label a company as "IBM" when it should have been "IBN" I guess that won't make anyone happy. I think that the real issue is that you must truly and carefully identify your business rules and model those in the database structure. Said another way, "How would a human handle this issue given a name collision?" If the answer is not obvious, then maybe you need to write an exceptions log and handle each case by hand that is not solved by a simple and clear to understand rule. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
On Tue, April 7, 2009 16:07, Tom Lane wrote: > > You might find it more useful to add some elog(LOG) statements to > the trigger body. > Thank you again. I will go through section 44.2 tonight. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
"James B. Byrne" writes: > I am poking in the dark here. What I want to do is to determine if > the trigger is firing and whether the function works as intended. > At the moment I am not seeing anything show up in the secondary > table so I have done something wrong. Is there some way of getting > PG to tell me what it is doing? You might find it more useful to add some elog(LOG) statements to the trigger body. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
On Tue, April 7, 2009 15:09, Tom Lane wrote: > > ALTER DATABASE foo SET log_min_messages = whatever; > > Note this will only affect subsequently-started sessions. Also, > if memory serves, you have to be superuser to set this particular > variable. Thanks. Am I correct to infer from the output this generates that log_min_messages = debug is primarily for developers of PG itself? I am poking in the dark here. What I want to do is to determine if the trigger is firing and whether the function works as intended. At the moment I am not seeing anything show up in the secondary table so I have done something wrong. Is there some way of getting PG to tell me what it is doing? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
"James B. Byrne" writes: > I am testing the trigger function that I wrote. Is there a way to > increase the logging detail level for just a single database > instance? ALTER DATABASE foo SET log_min_messages = whatever; Note this will only affect subsequently-started sessions. Also, if memory serves, you have to be superuser to set this particular variable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
Not in regards to logging detail, but that function in general... I'm pretty new to postgres, so I could be totally wrong in this, but I think this thread http://archives.postgresql.org/pgsql-performance/2008-03/msg00204.php may pertain if you see some performance degradation with that trigger. Like I said, somebody correct me if I'm way off base. Chris -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of James B. Byrne Sent: Tuesday, April 07, 2009 1:52 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] INSERT or UPDATE TRIGGER I am testing the trigger function that I wrote. Is there a way to increase the logging detail level for just a single database instance? The manual indicates not, but just in case I am misreading things I am asking here? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
I am testing the trigger function that I wrote. Is there a way to increase the logging detail level for just a single database instance? The manual indicates not, but just in case I am misreading things I am asking here? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE TRIGGER
This is what I have come up with. Comments are welcomed. CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert() RETURNS TRIGGER AS $pg_fn$ -- ROW AFTER TRIGGER -- trigger passes identifier_type, _value and _description -- received as ARGV[0], ARGV[1] and ARGV[2] BEGIN INSERT INTO identifiers( entity_id, identifier_type, identifier_value, identifier_description) VALUES( NEW.id, TG_ARGV[0], TG.ARGV[1], TG_ARGV[2]); -- Assume the INSERT fails because of a unique key violation, -- (entity_id + identifier_type + identifier_value) -- -- This does not matter since we only need ensure that this -- alias exists, so handle the exception and return: EXCEPTION WHEN unique_violation THEN -- do nothing NULL; END; $pg_fn$ LANGUAGE plpgsql; COMMENT ON FUNCTION hll_pg_fn_ident_insert IS 'Used by entities trigger. Inserts a corresponding identifiers row.' CREATE TRIGGER hll_pg_tr_entity_identifier_akna AFTER INSERT OR UPDATE ON entities FOR EACH ROW EXECUTE PROCEDURE hll_pg_fn_identifier_insert( "AKNA", entities.entity_common_name, "Common Name auto-insert"); COMMENT ON TRIGGER hll_pg_tr_entity_identifier_akna IS 'Inserts an alias identifier for common name if one does not exist' -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
> -Original Message- > From: James B. Byrne [mailto:byrn...@harte-lyne.ca] > Sent: Monday, April 06, 2009 5:43 PM > To: Dann Corbit > Cc: pgsql-general@postgresql.org > Subject: RE: [GENERAL] INSERT or UPDATE > > > On Mon, April 6, 2009 20:23, Dann Corbit wrote: > > > > > If a transaction involves rows where some succeed and some fail, > > all will roll back. If that is the desired behavior, or if all > > operations are singleton, then you won't see any problems. > > > > Do I understand correctly that this means that even if the function > "handles" a failed insert then if the function occurs inside a > transaction then that transaction fails and is rolled back > regardless? It depends on how it is handled. You could certainly (for instance) use a cursor and perform the operations one by one. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
On Mon, April 6, 2009 20:23, Dann Corbit wrote: > > If a transaction involves rows where some succeed and some fail, > all will roll back. If that is the desired behavior, or if all > operations are singleton, then you won't see any problems. > Do I understand correctly that this means that even if the function "handles" a failed insert then if the function occurs inside a transaction then that transaction fails and is rolled back regardless? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
> -Original Message- > From: James B. Byrne [mailto:byrn...@harte-lyne.ca] > Sent: Monday, April 06, 2009 5:16 PM > To: Dann Corbit > Cc: pgsql-general@postgresql.org > Subject: RE: [GENERAL] INSERT or UPDATE > > > On Mon, April 6, 2009 17:15, Dann Corbit wrote: > > > > > The pedagogic solution for this type of problem is called merge. > > The last I knew, PostgreSQL did not directly support merge. > > So you can accomplish the same thing in two stages: > > 1. Check for existence and perform an update if the key is present > > 2. If the key is not present, then perform an insert. > > > > Again, this may or may not be the right thing to do. > > > > Forgive my obtuseness, but what does the preliminary SELECT > accomplish? When the trigger fires we already know whether or not > the entities row existed previously, what we are deciding is how to > handle the concurrent identifiers table entry. > > I initially thought along these lines (select insert/update > depending on the return value) but I gradually realized that it did > not matter whether the identifier row was already there or not. If > it exists then an UNIQUE key constraint prevents duplicates. If it > does not exist then the INSERT succeeds. The previous identifier > associated with the original common name has to remain on file to > allow lookups by former names. Thus, we never update an identifier > row in this fashion. If a transaction involves rows where some succeed and some fail, all will roll back. If that is the desired behavior, or if all operations are singleton, then you won't see any problems. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
On Mon, April 6, 2009 17:15, Dann Corbit wrote: > > The pedagogic solution for this type of problem is called merge. > The last I knew, PostgreSQL did not directly support merge. > So you can accomplish the same thing in two stages: > 1. Check for existence and perform an update if the key is present > 2. If the key is not present, then perform an insert. > > Again, this may or may not be the right thing to do. > Forgive my obtuseness, but what does the preliminary SELECT accomplish? When the trigger fires we already know whether or not the entities row existed previously, what we are deciding is how to handle the concurrent identifiers table entry. I initially thought along these lines (select insert/update depending on the return value) but I gradually realized that it did not matter whether the identifier row was already there or not. If it exists then an UNIQUE key constraint prevents duplicates. If it does not exist then the INSERT succeeds. The previous identifier associated with the original common name has to remain on file to allow lookups by former names. Thus, we never update an identifier row in this fashion. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
Dann Corbit wrote on 06.04.2009 23:15:
I guess that for some collisions, sharing the name is OK.
I failed to explicitly state what the PK looked like.
entity_id(entities.id) +
identifier_type ('AKNA') +
identifier_value(entities.common_name)
There will only be a PK collision when we attempt to add a duplicate
common name for the same entity, which means it already exists and
does not need to be added again.
The pedagogic solution for this type of problem is called merge.
The last I knew, PostgreSQL did not directly support merge.
So you can accomplish the same thing in two stages:
1. Check for existence and perform an update if the key is present
2. If the key is not present, then perform an insert.
You don't actually need to check for existence. Just do the update, if no rows
were updated, you can insert (UPDATE will do an existence check anyway)
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
> -Original Message-
> From: James B. Byrne [mailto:byrn...@harte-lyne.ca]
> Sent: Monday, April 06, 2009 2:06 PM
> To: Dann Corbit
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] INSERT or UPDATE
>
> On Mon, April 6, 2009 17:00, Dann Corbit wrote:
> .
> >
> > It is a difficult question.
> >
> > For instance, there are many possibilities when a collision occurs.
> >
> > I guess that for some collisions, sharing the name is OK.
> >
>
> I failed to explicitly state what the PK looked like.
>
> entity_id(entities.id) +
> identifier_type ('AKNA') +
> identifier_value(entities.common_name)
>
> There will only be a PK collision when we attempt to add a duplicate
> common name for the same entity, which means it already exists and
> does not need to be added again.
The pedagogic solution for this type of problem is called merge.
The last I knew, PostgreSQL did not directly support merge.
So you can accomplish the same thing in two stages:
1. Check for existence and perform an update if the key is present
2. If the key is not present, then perform an insert.
Again, this may or may not be the right thing to do.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
On Mon, April 6, 2009 17:00, Dann Corbit wrote:
.
>
> It is a difficult question.
>
> For instance, there are many possibilities when a collision occurs.
>
> I guess that for some collisions, sharing the name is OK.
>
I failed to explicitly state what the PK looked like.
entity_id(entities.id) +
identifier_type ('AKNA') +
identifier_value(entities.common_name)
There will only be a PK collision when we attempt to add a duplicate
common name for the same entity, which means it already exists and
does not need to be added again.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INSERT or UPDATE
I have spent the last couple of days reading up on SQL, of which I know very little, and PL/pgSQl, of which I know less. I am trying to decide how best to approach the following requirement. Given a legal name and a common name and associated details, we wish to insert this information into a table, entities. As well, we believe it useful to allow a specific entity more than one common name. So, at the moment we are considering having another table, identifiers, that takes entity_id = entity.id (synthetic sequenced PK for entities), the identifier_type (always 'AKNA' for this collection of identifiers) and identifier_value = entity.common_name. This seems straight forward enough when initially inserting an entity. However, it is conceivable that over the lifetime of the system a particular entity might change its common name. For example the former "John Tash Enterprises" might become popularly known as "JTE Inc." while the legal name remains unchanged. When we update the entity record and set the common_name = "JTE Inc." then we need insert an identifier row to match. However, identifiers for a given entity can be maintained separately from the entity itself. It is therefore possible, indeed likely, that the identifier "JTE Inc." for that entity already exists. Likely, but not certain. In any case, the old identifier row remains unchanged after the new is inserted. The issue then is how to determine on an UPDATE entities whether it is necessary to INSERT a new identifier using values provided from the entities row. >From what I have gathered, what one does is simply insert the new identifiers row. If there is a primary key conflict then the update fails, which the function handles gracefully. If not, then it succeeds. I have also formed the opinion that what one does is write a function or functions, such as fn_aknau(entity_id, name), and tie these with triggers to the appropriate actions on entities such as: CREATE TRIGGER tr_entities_aioru AFTER INSERT OR UPDATE ON entities FOR EACH ROW EXECUTE fn_aknai(entities.id, 'AKNA', entities.common_name); Is my appreciation correct or am I missing the mark entirely? Is this considered the proper place and means to accomplish this sort of task in an RDBMS? Does it belong elsewhere? Am I correct in inferring that the values in the columns id and common_name will be those of entities AFTER the insert or update and that these will be available to the body of the function? Is the trigger dependent upon a SUCCESSFUL INSERT or UPDATE of entities or will it be called regardless? Must the function be written in PL/pgSQl (or similar PL) or could this function be written in straight SQL? Should it be straight SQL if possible? What should the function return, if anything? Fairly basic stuff I am sure but somewhat mystifying for me at the moment. Any help would be appreciated. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT OR UPDATE
On 2006-01-03, Tony Wasson <[EMAIL PROTECTED]> wrote: > On 1/2/06, Julian Scarfe <[EMAIL PROTECTED]> wrote: >> I'm keeping config information for an application in a series of related >> tables. I'd like a command that INSERTs data if it's new, or UPDATEs it if >> the key is duplicated. > > A MERGE trigger will do exactly what you are asking for. > > http://archives.postgresql.org/pgsql-sql/2004-05/msg00135.php > > Send all your data as INSERTS. > The trigger will run a function to see if the row exists. > If the row exists -> rewrite it as it as an UPDATE > If the row does not exist -> leave the INSERT alone There are some rather serious race conditions in that (it's not taking anything like a strong enough lock to avoid that - it would need to use SHARE ROW EXCLUSIVE, not ROW EXCLUSIVE). In 8.0 on, use the method described here: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] INSERT OR UPDATE
On 1/2/06, Julian Scarfe <[EMAIL PROTECTED]> wrote: > I'm keeping config information for an application in a series of related > tables. I'd like a command that INSERTs data if it's new, or UPDATEs it if > the key is duplicated. A MERGE trigger will do exactly what you are asking for. http://archives.postgresql.org/pgsql-sql/2004-05/msg00135.php Send all your data as INSERTS. The trigger will run a function to see if the row exists. If the row exists -> rewrite it as it as an UPDATE If the row does not exist -> leave the INSERT alone Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] INSERT OR UPDATE
Julian Scarfe wrote: > I'm keeping config information for an application in a series of > related tables. I'd like a command that INSERTs data if it's new, < or UPDATEs it if the key is duplicated. Write a stored procedure called something like InsertUpdateConfigData. Pick the operation that you think is most likely to occur more often (the insert or update). Code that as the initial statement. Then create an exception block, and in that exception block catch the error that would result from the initial statement being executed in the case where the other one should have been; then execute that other statement. There is no magic database-provided SQL statement that says "try an insert and if that fails then try an update." You have to do that yourself. -- Guy Rouillier ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] INSERT OR UPDATE
I'm keeping config information for an application in a series of related
tables. I'd like a command that INSERTs data if it's new, or UPDATEs it if
the key is duplicated.
Copying the config info from one database to another virgin installation is
easy, of course. I can just use pg_dump on the original db1 to create the
DDL and run it into the new database db2.
The problem comes when I've made changes in db1 that I want to copy to db2,
but db2 may itself have additional entries not present in db1. I'd like to
update the entries in db2 that have equivalent entries in db1 with their db1
values, without destroying the rest of the data in db2.
Here's an example:
db1 is created as follows:
create table a (
a_id text PRIMARY KEY,
param_b text,
param_c text
);
create table d (
d_id text PRIMARY KEY,
a_id text references a(a_id) ON UPDATE CASCADE,
param_e text,
param_f text
);
insert into a values ('a1', 'b1', 'c1');
insert into a values ('a2', 'b2', 'c2');
insert into d values ('d1', 'a1', 'e1', 'f1');
insert into d values ('d2', 'a2', 'e2', 'f2');
insert into d values ('d3', 'a1', 'e3', 'f3');
The databases have identical schema:
$ pg_dump -s db1 | psql db2
and provided db2 is empty, I can just copy the contents across:
$ pg_dump -ad db1 > db1.config
$ psql db2 < db1.config
So far so good.
But now db2 gets some additional entries:
db2=# insert into a values ('a3', 'b3', 'c3');
db2=# insert into d values ('d4', 'a1', 'e4', 'f4');
db2=# insert into d values ('d5', 'a2', 'e5', 'f5');
Meanwhile, I make some config changes in db1:
db1=# update a set param_b = 'b1 new' where a_id = 'a1';
db1=# update d set param_e = 'e1 new' where d_id = 'd1';
db1=# update d set param_f = 'f2 new' where d_id = 'd2';
So:
$ pg_dump -ad db1 > db1.config
and db1.config now contains
INSERT INTO a VALUES ('a2', 'b2', 'c2');
INSERT INTO a VALUES ('a1', 'b1 new', 'c1');
INSERT INTO d VALUES ('d3', 'a1', 'e3', 'f3');
INSERT INTO d VALUES ('d1', 'a1', 'e1 new', 'f1');
INSERT INTO d VALUES ('d2', 'a2', 'e2', 'f2 new');
I want to update the data in db2 to reflect the values in db1. I can't
truncate the tables in db2 because I'd lose the additional entries (a3, d4,
d5).
But of course when I do...
$ psql db2 < db1.config
... I get ...
ERROR: duplicate key violates unique constraint "a_pkey"
ERROR: duplicate key violates unique constraint "d_pkey"
...and the a1, d1, d2 rows are not updated to match db1.
What I'd really like is to be able to do:
INSERT OR UPDATE INTO a VALUES ('a2', 'b2', 'c2');
INSERT OR UPDATE INTO a VALUES ('a1', 'b1 new', 'c1');
INSERT OR UPDATE INTO d VALUES ('d3', 'a1', 'e3', 'f3');
INSERT OR UPDATE INTO d VALUES ('d1', 'a1', 'e1 new', 'f1');
INSERT OR UPDATE INTO d VALUES ('d2', 'a2', 'e2', 'f2 new');
so that the rest of the row is treated as an UPDATE if the primary key is a
duplicate.
Of course I can write something at the application level to examine each row
and take appropriate action. But it feels like this may be a commonly
encountered problem for which there may be a database-level solution. Am I
missing something obvious?
Thanks
Julian Scarfe
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT OR UPDATE?
Check the TODO, I'm 99% certain it's on there. On Mon, Oct 10, 2005 at 02:02:32PM +0200, Csaba Nagy wrote: > On Mon, 2005-10-10 at 13:34, Lincoln Yeoh wrote: > [snip] > > It's actually quite surprising how many people get this wrong and don't > > realize it (I wonder how many problems are because of this). The SQL spec > > should have had a PUT/MERGE decades ago. The insert vs update format being > > different is also annoying, oh well. > > Referring to the above, is there any plan to implement such commands in > postgres ? I don't know if it is standard SQL, but some other RDBMSes > have such command, and they are actually useful. > > > > > Regards, > > Link. > [snip] > > Cheers, > Csaba. > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] INSERT OR UPDATE?
On Mon, 2005-10-10 at 13:34, Lincoln Yeoh wrote: [snip] > It's actually quite surprising how many people get this wrong and don't > realize it (I wonder how many problems are because of this). The SQL spec > should have had a PUT/MERGE decades ago. The insert vs update format being > different is also annoying, oh well. Referring to the above, is there any plan to implement such commands in postgres ? I don't know if it is standard SQL, but some other RDBMSes have such command, and they are actually useful. > > Regards, > Link. [snip] Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] INSERT OR UPDATE?
Actually I think the uniqueness constraint (due to the primary key) is the one handling it. There's nothing special about that function that will prevent duplicates. Try running it without the primary key specifications in two separate concurrent transactions. Then commit both transactions. Similarly the other methods will be fine as long as there is a uniqueness constraint. If you don't have a uniqueness constraint or you don't want to trigger and exception/error (which could be troublesome in versions of Postgresql without savepoints) then you will have to use locking. It's actually quite surprising how many people get this wrong and don't realize it (I wonder how many problems are because of this). The SQL spec should have had a PUT/MERGE decades ago. The insert vs update format being different is also annoying, oh well. Regards, Link. At 10:01 AM 10/9/2005 -0700, David Fetter wrote: This is very clever, but it has a race condition. What happens if between the time of the EXISTS() check and the start of the UPDATE, something happens to that row? Similarly, what if a row comes into existence between the EXISTS() check and the INSERT? The UPSERT example below, while a little more complicated to write and use, handles this. http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING SQL:2003 standard MERGE should fix all this. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] INSERT OR UPDATE?
On 09.10.2005 08:48, andrew wrote: A very usual and smart approach is to use clases in PEAR::DB. Well, IMHO PEAR::DB is one of the worst classes of PEAR. Besides its ugly code it's worth nothing. This is some incomplete "abstraction layer" for kiddies, to make it easy for people coming from stupid storage engines like MySQL or SqLite, to _store_ their data in another DBMS. I never really understood, what such a thing should be good for. SQL implementations are not portable, nor are stored procedures, functions, rules, triggers, etc. Every DBMS implements them in it's own way and almost all professional devs will implement most, or at least the critical parts, of the business logic directly inside DBMS and not at application level. Thus making the application basically just a frontend, calling stored procedures and writing to views, controlled by triggers and strict permissions. The only thing PEAR::DB might be usefull for is, writing those famous cross-dbms-apps, which basically use the DBMS highly inefficient as a better plaintext file (not even using foreign keys in most cases, so it will work with MySql 3.x as well). And if you don't what to write such bizarre cross-dbms-apps, what is the point in using PEAR::DB at all? Those few incomplete methods wrapping the pg_* functions in some highly debatable way? Do yourself a favor and write your own slime and efficient set of classes around the native API-functions of your DBMS. PEAR::DB and others prevent you from using an advanced DBMS the way you should. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT OR UPDATE?
On Sun, Oct 09, 2005 at 10:10:28AM -0400, Jerry Sievers wrote: > [EMAIL PROTECTED] writes: > > > Hello all, > > > > I am writing an app in PHP that uses a PostGres database. One > > thing i have noticed is that what should/could be a single line of > > SQL code takes about 6 lines of PHP. This seem wasteful and > > redundant to me. > > Here ya go!... > > create temp table foo ( > id int primary key, > data text > ); > > create rule foo > as on insert to foo > where exists ( > select 1 > from foo > where id = new.id > ) > do instead > update foo > set data = new.data > where id = new.id > ; This is very clever, but it has a race condition. What happens if between the time of the EXISTS() check and the start of the UPDATE, something happens to that row? Similarly, what if a row comes into existence between the EXISTS() check and the INSERT? The UPSERT example below, while a little more complicated to write and use, handles this. http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING SQL:2003 standard MERGE should fix all this. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT OR UPDATE?
Try (for simple cases):
DELETE FROM my.table WHERE somecondition;
INSERT INTO my.table (somefield) VALUES ('$someval');
In complex cases it may be necessary to INSERT the values into a
temporary table, which is then used to condition the DELETE before
INSERTing the temporary table into your permanent table.
One advantage of the above is that it works for multiple rows, whereas
your technique does not.
-- Dean
On 2005-10-08 23:15, [EMAIL PROTECTED] wrote:
Hello all,
I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.
Here is a sample of what I'm talking about ($db is a PDO already
defined and created).
$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
somecondition";
$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
if(!$db->query($query[1])){
$db->query($query[2]);
}
What I'm curious to know is if there is some way to simplify this,
either buy some PHP builtin or extension, or possibly something in SQL
I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
but I can't seem to find anything relevant at all about it.
Anyways I hope you don't mind, but I'm crossposting this to
pgsql.general and comp.lang.php to see if I can get some information on
the subject.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] INSERT OR UPDATE?
>I am writing an app in PHP that uses a PostGres database.
>One thing i have noticed is that what should/could be a single line of
>SQL code takes about 6 lines of PHP. This seem wasteful and redundant
>to me.
>
>Here is a sample of what I'm talking about ($db is a PDO already
>defined and created).
>
>$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
>somecondition";
>$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
>if(!$db->query($query[1])){
>$db->query($query[2]);
>}
>
>What I'm curious to know is if there is some way to simplify this,
>either buy some PHP builtin or extension, or possibly something in SQL
>I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
>but I can't seem to find anything relevant at all about it.
MySQL permits (but it's not standard, and available in MySQL 4.1.0
and later):
INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
KEY UPDATE somefield = '$someval';
This is very useful for times when you want to count something (e.g.
SPAM), and if a record doesn't exist, make one with a count of 1.
I don't know whether something similar is available in PostGres.
Gordon L. Burditt
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] INSERT OR UPDATE?
Gordon Burditt wrote:
> [...stuff snipped...]
>
> MySQL permits (but it's not standard, and available in MySQL 4.1.0
> and later):
>
> INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
> KEY UPDATE somefield = '$someval';
>
> This is very useful for times when you want to count something (e.g.
> SPAM), and if a record doesn't exist, make one with a count of 1.
>
> I don't know whether something similar is available in PostGres.
>
> Gordon L. Burditt
In Postgres you'd probably create a server-side function to get that
effect.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] INSERT OR UPDATE?
Hello all,
I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.
Here is a sample of what I'm talking about ($db is a PDO already
defined and created).
$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
somecondition";
$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
if(!$db->query($query[1])){
$db->query($query[2]);
}
What I'm curious to know is if there is some way to simplify this,
either buy some PHP builtin or extension, or possibly something in SQL
I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
but I can't seem to find anything relevant at all about it.
Anyways I hope you don't mind, but I'm crossposting this to
pgsql.general and comp.lang.php to see if I can get some information on
the subject.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] INSERT OR UPDATE?
I think is almost the same that in many other languages, and like in many other with the time you can have function's libraries, or more likely class libraries with the usefull stuff. In desktop programming environments you have components, here you have classes that are the same thing using it in another way. :) Watch out the new auto-includes from PHP5 classes. There are scripts with differents approaches, you can select one or build your own for multiple proyects. Personally I preffer not to build the SQL code in PHP. I allways have the queries in XML files with an specific format I chose, to describe a SQL Query with parameters. A class to handle the query can "tell me" about the parameters or simply "receive" the parameters, build and execute the query, and return the results or making it browseable (recordset). A very usual and smart approach is to use clases in PEAR::DB. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] INSERT OR UPDATE?
[EMAIL PROTECTED] writes: > Hello all, > > I am writing an app in PHP that uses a PostGres database. > One thing i have noticed is that what should/could be a single line of > SQL code takes about 6 lines of PHP. This seem wasteful and redundant > to me. Here ya go!... create temp table foo ( id int primary key, data text ); create rule foo as on insert to foo where exists ( select 1 from foo where id = new.id ) do instead update foo set data = new.data where id = new.id ; copy foo from stdin using delimiters ','; 1,hello 2,hello \. select * from foo order by id; insert into foo values ( 1,'it works!' ); select * from foo order by id; Outout... CREATE TABLE CREATE RULE id | data +--- 1 | hello 2 | hello (2 rows) INSERT 0 0 id | data +--- 1 | it works! 2 | hello (2 rows) HTH -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] INSERT OR UPDATE?
[EMAIL PROTECTED] writes: > Hello all, > > I am writing an app in PHP that uses a PostGres database. > One thing i have noticed is that what should/could be a single line of > SQL code takes about 6 lines of PHP. This seem wasteful and redundant > to me. > > Here is a sample of what I'm talking about ($db is a PDO already > defined and created). Well, at least you're using a very good DB! This is easy to solve in Postgres. Study up on "the rule system" and you will find the solution. A BEFORE INSERT trigger could be used here as well. create table foo (a int not mull primary key, b text); create rule maybe_update as on insert to foo where exists (select 1 from foo where a = new.a) do instead update foo set b = new.b where a = new.a; Untested example above... but have done this sort of thing a lot. Can be difficult to grasp at first. A before insert trigger would test if the record exists already and if so, do an update inside the trigger function and return null else return new and the outter query proceed doing the insert. HTH -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Insert Or update
On Saturday 24 April 2004 00:09, you wrote: > And in the proper way to do this in a relational database, those rows > are locked by the application until the user presses the OK button. This kind of change is very rare and is usually done by "admin" user. There's no need to lock those rows between load and update. > As an addendum to Bruno's comment (which is true), I'll add that the > "insert or update" command (in MySQL it's a "replace" command) really > suggests a broken architecture in your database. It's okay, we often > use relational databases for non-relational tasks simply because we have > nothing else available, but don't be surprised when it breaks. In my case, insert-or-update is just a handy shortcut for a longer operation. It appears that this shortcut can be useful to many people in various situations, as the requests for it are constantly appearing from time to time. mysql's "replace" is broken in a sence that for existing row, it's actually DELETE+INSERT, and this breaks lots of things. For example, it'll assign new ID for auto_increment column type. This is a big problem for foreign keys (but not a problem for mysql due to obvious reasons:). insert-or-update is useful in a general case when you need to merge some new set of data (maybe coming as an update from some external source) with an existing set of data in some table. Is it a relational or a non-relational task ? Are there any plans on adding this feature per sql 2003 spec ? -- Best regards, Igor Shevchenko ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Insert Or update
On Fri, Apr 23, 2004 at 20:17:10 +0300, Igor Shevchenko <[EMAIL PROTECTED]> wrote: > > This workaround is ok but it requires additional programming instead of a > simple single query. Absence of this sort of thing moves some of naturally > database-side logic off to the application, and this sounds quite mysql-ish > to me =\ > This feature was asked for for too many times, maybe it's time to implement it > in some form ? I suspect most of the people doing this have something wrong with their design in the first place. > For example, like this: INSERT OR UPDATE into TABLE [(list of attributes)] > values (list of values) There is a command defined to do this in the latest SQL standard. There was some discussion about this very recently on one of the lists. You might want to look at it to see what the standard syntax is going to be. There were also some comments on the semantics of the command. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Insert Or update
On Friday 23 April 2004 20:41, Bruno Wolff III wrote: > I suspect most of the people doing this have something wrong with their > design in the first place. Not really. Here's a simple example. I have a set of mailboxes and I needed to implement a gui widget to assign/remove them to/from a workspace. When the widget starts, there are two lists: first with assigned, second with unassigned (all other) mailboxes. User moves data from one list to another and some back, etc, and hits "OK" button. It appeared that the easiest way to sync this widget's data with the database was via insert-or-update path. -- Best regards, Igor Shevchenko ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Insert Or update
Bruno Wolff III <[EMAIL PROTECTED]> writes: > This was discussed on the list over the last couple of days. > There is no update or insert statement in postgres. > You can do an update and check the number of rows affected and if it > is 0 do the insert. I prefer to do the insert and if it fails due to a unique key constraint then do the update. If you don't have any deletes then this is safe from race conditions whereas the update first method could fail if two people do updates and then both try to insert. In practice actually I rarely have to do this. > However unless you lock the table while doing this, you have to be prepared > to handle errors. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Insert Or update
On Friday 23 April 2004 17:53, Bas Scheffers wrote: > What do you need to do more of, inserts or updates? If the answer is > updates, just do an update and then check for the number of rows affected. > If it is 0, follow it with an insert, if not, you are done. > > You could do this in a stored procedure to save you the round trip of data > between the DB and your application and will be faster. This workaround is ok but it requires additional programming instead of a simple single query. Absence of this sort of thing moves some of naturally database-side logic off to the application, and this sounds quite mysql-ish to me =\ This feature was asked for for too many times, maybe it's time to implement it in some form ? For example, like this: INSERT OR UPDATE into TABLE [(list of attributes)] values (list of values) 2 possibilities here: a) target table has at least one unique constraint on which this insert fails, i.e. the row is "already in the database", by the unique constraint's definition. In this case, pg can UPDATE it's attributes from (list of attributes) with data from (list of values). b) no constraint failure => new row is inserted. This definition uses unique constraints to define and answer the question "is the row is in the table already?". I'm sure somebody would want to define this via some subset of target table's attributes, like this: INSERT OR UPDATE ON (list of target table's attributes) into TABLE [(list of attributes)] values (list of values) I dont know if there's anything about this topic in the SQL spec; those are just my random thoughs. Personally, I've had several situations where I'd benefit from having this feature in either form. -- Best regards, Igor Shevchenko ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Insert Or update
What do you need to do more of, inserts or updates? If the answer is updates, just do an update and then check for the number of rows affected. If it is 0, follow it with an insert, if not, you are done. You could do this in a stored procedure to save you the round trip of data between the DB and your application and will be faster. Cheers, Bas. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Insert Or update
On Fri, Apr 23, 2004 at 17:48:21 +0400, Anton Nikiforov <[EMAIL PROTECTED]> wrote: > I know that this will be helpful to write a function that will do this > for me, but it will run the same time as my insertion tool that is > written in c or even slower. So my question is: is it possible to have > "UPDATE OR INSERT" statement in the PostgreSQL like it is possible in > Oracle (as i heard but did not check it by myself yet)? This was discussed on the list over the last couple of days. There is no update or insert statement in postgres. You can do an update and check the number of rows affected and if it is 0 do the insert. However unless you lock the table while doing this, you have to be prepared to handle errors. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Insert Or update
Dear All, I have a database which stores traffic data and to update the traffic for the particular IP i have to select this ip from the table for this period and if it is already in the database i should run an update statement, but if it is not presented - i should insert the data. It was OK when i have had 2-3 millions of records, but now it is 15 millions and i'm not complaining that it is too slow to select - no, but it takes some time anyway. I know that this will be helpful to write a function that will do this for me, but it will run the same time as my insertion tool that is written in c or even slower. So my question is: is it possible to have "UPDATE OR INSERT" statement in the PostgreSQL like it is possible in Oracle (as i heard but did not check it by myself yet)? Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
