[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 a path from textbox...
On 31 May 2010, at 23:27, david.cata...@1as.es 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
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
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 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
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
James B. Byrne byrn...@harte-lyne.ca 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
James B. Byrne byrn...@harte-lyne.ca 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
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
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
[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 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
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
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
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
-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 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: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
[GENERAL] Insert vs Update syntax
When inserting into a table and there are many columns to be inserted it is hard to synchronize columns to values: insert into my_table ( a, b, c, ...many more columns )values( @a, @b, @c, ... the corresponding values ) Is there some reason for the insert syntax to be the way it is in instead of the much easier to get it right Update syntax?: update my_table set a = @a, b = @b, c = @c, ... Regards, Clodoaldo Pinto Neto ---(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 vs Update syntax
2008/2/29, Martijn van Oosterhout [EMAIL PROTECTED]: On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote: When inserting into a table and there are many columns to be inserted it is hard to synchronize columns to values: snip Is there some reason for the insert syntax to be the way it is in instead of the much easier to get it right Update syntax?: Because it's what the SQL standard says. If you don't like it I suggest you take it up with them... But it's a little late to change now I think. I know about the standards and I'm not blaming postgresql. I just want to know if it is worth to bring the the matter to some responsible group. Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Insert vs Update syntax
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote: When inserting into a table and there are many columns to be inserted it is hard to synchronize columns to values: snip Is there some reason for the insert syntax to be the way it is in instead of the much easier to get it right Update syntax?: Because it's what the SQL standard says. If you don't like it I suggest you take it up with them... But it's a little late to change now I think. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Insert vs Update syntax
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to your abstraction layer/wrapper, and it can cycle
through the data structure to do the insert or update for you.
In very rough psuedo-code, something like
create my_data_structure {
table = employees,
first_name = sally,
last_name = smith,
date_of_birth = 2008-01-01
}
call function mywrapper (my_data_structure)
.
function cycles through array, and creates an sql string
dynamically
This may have problems with constraints, foreign keys, etc.
Clodoaldo wrote:
2008/2/29, Martijn van Oosterhout [EMAIL PROTECTED]:
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
snip
Is there some reason for the insert syntax to be the way it is in
instead of the much easier to get it right Update syntax?:
Because it's what the SQL standard says. If you don't like it I suggest
you take it up with them... But it's a little late to change now I
think.
I know about the standards and I'm not blaming postgresql. I just want
to know if it is worth to bring the the matter to some responsible
group.
Regards, Clodoaldo Pinto Neto
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Insert vs Update syntax
2008/2/29, Brent Friedman [EMAIL PROTECTED]:
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to your abstraction layer/wrapper, and it can cycle
through the data structure to do the insert or update for you.
Ok. I have been playing these tricks for the last 25 years. Please no
more tricks. If someone knows something about the reason for the
insert syntax in instead of the Update syntax then please elaborate on
it.
Regards, Clodoaldo Pinto Neto
In very rough psuedo-code, something like
create my_data_structure {
table = employees,
first_name = sally,
last_name = smith,
date_of_birth = 2008-01-01
}
call function mywrapper (my_data_structure)
.
function cycles through array, and creates an sql string
dynamically
This may have problems with constraints, foreign keys, etc.
Clodoaldo wrote:
2008/2/29, Martijn van Oosterhout [EMAIL PROTECTED]:
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
snip
Is there some reason for the insert syntax to be the way it is in
instead of the much easier to get it right Update syntax?:
Because it's what the SQL standard says. If you don't like it I suggest
you take it up with them... But it's a little late to change now I
think.
I know about the standards and I'm not blaming postgresql. I just want
to know if it is worth to bring the the matter to some responsible
group.
Regards, Clodoaldo Pinto Neto
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Insert vs Update syntax
Hi, I've solved this problem for me (Perl). I have a module DBAPI and write a function InsertIntoTable($table_name, $hash_with_values, $data_base_handler). I send the parms to the function in the hash (key1 = value1, key2 = value2 ...) and in the function I compose the insert and execute it. INSERT INTO $table_name (key1, key2, key3) VALUES (value1, value2, value3); So I suggest you write such function and solve most of your problems:-) Regards, Kaloyan Iliev Clodoaldo wrote: 2008/2/29, Martijn van Oosterhout [EMAIL PROTECTED]: On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote: When inserting into a table and there are many columns to be inserted it is hard to synchronize columns to values: snip Is there some reason for the insert syntax to be the way it is in instead of the much easier to get it right Update syntax?: Because it's what the SQL standard says. If you don't like it I suggest you take it up with them... But it's a little late to change now I think. I know about the standards and I'm not blaming postgresql. I just want to know if it is worth to bring the the matter to some responsible group. Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Insert vs Update syntax
Clodoaldo wrote: 2008/2/29, Brent Friedman [EMAIL PROTECTED]: If you don't like the standard sql implementation, you could use plsql or any language to make an abstraction layer/wrapper for this functionality. Just pass everything as a key/value pair, in an array or hashtable structure, to your abstraction layer/wrapper, and it can cycle through the data structure to do the insert or update for you. Ok. I have been playing these tricks for the last 25 years. Please no more tricks. Tricks? If someone knows something about the reason for the insert syntax in instead of the Update syntax then please elaborate on it. I believe it's because of the way it reads in English: INSERT INTO some columns these values. One of the key benefits of SQL was supposed to be its ability for non-programmers to use it. As a result, they sacrificed consistency for the sake of ease-of-learning. Of course, once you start writing queries of any complexity, you lose the benefits of reading as English. If you come across a time machine, pop back to the first standards meeting and have a word with them, would you? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] INSERT before UPDATE?
Hello, I would like to ask you for an advice. There are two tables in my PostgreSQL database - main table with datas and second with translations for all languages of these records. When I try to UPDATE a record in the language table and this record doesn't exists there I need to INSERT into the language table (from default language version) and execute UPDATE statement again. Could you tell me if is this possible and if so - how to do it? Thank you in advance. -- Jiri Nemec http://www.meneashop.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] INSERT before UPDATE?
am Thu, dem 16.08.2007, um 10:30:01 +0200 mailte Ji?í N?mec folgendes: Hello, I would like to ask you for an advice. There are two tables in my PostgreSQL database - main table with datas and second with translations for all languages of these records. When I try to UPDATE a record in the language table and this record doesn't exists there I need to INSERT into the language table (from default language version) and execute UPDATE statement again. Could you tell me if is this possible and if so - how to do it? Okay, i try to ask our doc-bot on IRC: 11:15 akretschmer ??upsert 11:15 rtfm_please For information about upsert 11:15 rtfm_please see http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT before UPDATE?
am Thu, dem 16.08.2007, um 14:11:07 +0200 mailte Ji??í N??mec folgendes: see http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Yes I see, but I'll try to describe it in more detail: I could write plpgsql trigger function which will UPDATE a record (or INSERT new record) when the record is not found. But as far as I know a trigger is not executed when a updated record is not found, so this solution doesn't work for me. I don't want to change my application - I would like to set this behaviour directly in PostgreSQL database so I couldn't use a method from your example (...which requires that all SQL UPDATE statements need to be modified). Maybe you can achieve this with a RULE. I'm not sure, but i would try ist. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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
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
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
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 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?
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?
[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?
[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?
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
[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?
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
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?
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?
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
[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
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
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 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
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 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
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 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
