Bulk Insert / Update / Delete
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;
The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.
Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to accomplist
this in one go ?
Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
Hi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correclty you
should be able to acheive the same result using two seperate queries and the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine docs
on pl/pgsql and other postgresql procedural languages which allow you to use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed in
this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
Hi Philip,
See:
http://www.postgresql.org/docs/7.3/static/functions-subquery.html
..for starters.
Essentially, to perform the operation atomically I'd use:
begin;
update <table> set <cols> = <values>, ... where exists (select <corresponding
columns> from <table2> where <table1>.<col> = <table2>.<col> (and).. etc..);
(actually i'd probably use a the from extension here ^^^^ , see example below)
insert into <table> <columnlist> select <columns> from <table2> where not
exists (select <corresponding columns> from <table1> where <table2>.<col> =
<table1>.<col> (and).. etc..);
commit;
because it's wrapped in a transaction both queries have to work or it's all
rolled back. This example only applies to comparing two tables. You can
specify a value list if need be.
As an actual example:
begin;
update table1 set col1 = table2.col1, col2 = table2.col2 from
table2 where table2.key = table1.key;
(whatever your key may be..)
insert into table1 (col1,col2) select col1,col2 from table2 where not exists
(select col1,col2 from table1 where table1.col1 = table2.col1 and table1.col2
= table2.col2);
(in this ^^^ I'm assuming your keys are col1 and col2 and so it's not
consistent with the update but you get the idea.
commit;
Rgds,
Jason
Show quoted text
On Wed, 20 Aug 2003 01:03 pm, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
questionHow do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist this in one go ?Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correclty you
should be able to acheive the same result using two seperate queries and
the (NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs on pl/pgsql and other postgresql procedural languages which allow you
to use loops and conditional statements like 'if'.Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the
addressee only. If you are not the intended recipient you may not
disclose, copy or in any way use or publish the content hereof, which is
subject to copyright.If you have received this in error, please destroy
the original message and contact us at postmaster@cks.co.za. Any views
expressed inthis
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems,
its subsidiaries or associates. Please note that the recipient must scan
this e-mail and attachments for viruses. We accept no liability of
whatever nature for any loss, liability,damage or expense resulting
directly or indirectly from this transmission of this message and/or
attachments.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmasterThis message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed in this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Thanks Jason.
The UPDATE part works fine. However, INSERT still gives problems. For now,
I'm inserting hard coded values, and not from one table into the other.
I use
UPDATE telephones SET
telephone_type='CELL',
telephone_number=836789012
WHERE data_set='AA'
AND account_number=8
AND dependant_number=1
AND sub_entity='pers'
AND sub_occur=1
INSERT INTO telephones
VALUES ('AA',8,1,'pers',1,CELL,836789012);
even if I were getting these values from another table, I'd still have to
somehow determine if the data EXISTS in the telephones table before deciding
to UPDATE, or INSERT.
It looks like this is just not possible in a SQL query on its own, but has
to be done in a higher level language where the condition may be
successfully processed - i.e. SELECT * WHERE <same WHERE as above>
and then check IF EXISTS, if so, UPDATE, if not INSERT.
Or would I be able to achieve such functionality by writing a Function ?
Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, August 20, 2003 10:07 AM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
Hi Philip,
See:
http://www.postgresql.org/docs/7.3/static/functions-subquery.html
..for starters.
Essentially, to perform the operation atomically I'd use:
begin;
update <table> set <cols> = <values>, ... where exists (select
<corresponding
columns> from <table2> where <table1>.<col> = <table2>.<col> (and).. etc..);
(actually i'd probably use a the from extension here ^^^^ , see example
below)
insert into <table> <columnlist> select <columns> from <table2> where not
exists (select <corresponding columns> from <table1> where <table2>.<col> =
<table1>.<col> (and).. etc..);
commit;
because it's wrapped in a transaction both queries have to work or it's all
rolled back. This example only applies to comparing two tables. You can
specify a value list if need be.
As an actual example:
begin;
update table1 set col1 = table2.col1, col2 = table2.col2 from
table2 where table2.key = table1.key;
(whatever your key may be..)
insert into table1 (col1,col2) select col1,col2 from table2 where not exists
(select col1,col2 from table1 where table1.col1 = table2.col1 and
table1.col2
= table2.col2);
(in this ^^^ I'm assuming your keys are col1 and col2 and so it's not
consistent with the update but you get the idea.
commit;
Rgds,
Jason
On Wed, 20 Aug 2003 01:03 pm, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
questionHow do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist this in one go ?Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correclty
you
should be able to acheive the same result using two seperate queries and
the (NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs on pl/pgsql and other postgresql procedural languages which allow you
to use loops and conditional statements like 'if'.Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the
addressee only. If you are not the intended recipient you may not
disclose, copy or in any way use or publish the content hereof, which is
subject to copyright.If you have received this in error, please destroy
the original message and contact us at postmaster@cks.co.za. Any views
expressed inthis
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems,
its subsidiaries or associates. Please note that the recipient must scan
this e-mail and attachments for viruses. We accept no liability of
whatever nature for any loss, liability,damage or expense resulting
directly or indirectly from this transmission of this message and/or
attachments.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmasterThis message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed in
this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
On Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist and
need updating, and which do not and need inserting, then what can ?In the old world of indexed ISAM files it is very simple - try to get the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.
SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.
Now, one can do this with a higher level language and SQL combined, but is
SQL that weak ?
No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow determine
what needs INSERTING and what needs UPDATING.... Or does one try to merge,
get a failure, an resort to writing something in Perl or C ?
In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.
So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.
For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.
With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.
If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...
Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, toaccomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correcltyyou
should be able to acheive the same result using two seperate queries and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
After listening to many White House, Pentagon & CENTCOM
briefings in both Gulf Wars, it is my firm belief that most
"senior correspondents" either have serious agendas that don't
get shaken by facts, or are dumb as dog feces.
Import Notes
Reply to msg id not found: 011c01c36812$cb6b1b00$e701f00a@240.1.139.196.23.149.50
On Thu, 2003-08-21 at 14:37, Philip Boonzaaier wrote:
Hi Ron
Yeah. I see what you are getting at. However, what about using a RULE ? This
seems to fit what I am trying to do.
You mean a PostgreSQL RULE?
Let me tell you what I am doing at the moment. I am migrating a COBOL based
system to a RDBMS base, and eventually a Perl / Java / Whatever front end.
Well, gee, there are pre-compilers floating around that let you
embed SQL in COBOL. Unfortunately, non of them are OSS...
As Phase 1, I am simple replicating the data in PostgreSQL. I have created
tables identical to the 'records' in COBOL. When I INSERT in COBOL, I create
an INSERT in SQL and action this. This is done externally from COBOL, and
Externally from COBOL? You mean in some lashed-together batch
mode operation?
not using any embedded SQL features. Similarly with UPDATE. However, I now
want to create a Table based on a sub - set of information, in the record
in the first attempt, I am creating a table of Telephone numbers for an
account, which is currently defined as an array of 4 possibilities within
the account record. ). Now, when UPDATING the main row, I have no idea if
the sub - set of information is already in the database, or not. So I want
to, simply by writing a SQL statement, INSERT or UPDATE the information in
the database.
Give the name T_SUBSET to this sub-set table, and T_MAIN to the
main table. Original, eh?
Thus, for a given tuple in the main row, some pseudo-code:
UPDATE t_main AS m
SET m.field1 = ss.field1,
m.field2 = ss.field2
FROM t_subset AS ss
WHERE m.field3 = ss.field3
AND m.field4 = ss.field4
AND ss.field3 = ??
AND ss.field4 = ?? ;
IF zero rows updated THEN
INSERT INTO T_MAIN VALUES (blah, blah, blah);
END IF
If the number of parameters that you'd need to send is a reasonable
amount, then you could encapsulate the code into a trigger, thus
simplifying the Perl / Java / Whatever code.
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 9:01 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist and
need updating, and which do not and need inserting, then what can ?In the old world of indexed ISAM files it is very simple - try to get the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.Now, one can do this with a higher level language and SQL combined, but is
SQL that weak ?No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow determine
what needs INSERTING and what needs UPDATING.... Or does one try to merge,
get a failure, an resort to writing something in Perl or C ?In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, toaccomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correcltyyou
should be able to acheive the same result using two seperate queries and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a
list
of
data, effectively row by row, enquire on the database if this exists
in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe notat
all.
Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"Whatever may be the moral ambiguities of the so-called
demoratic nations and however serious may be their failure to
conform perfectly to their democratic ideals, it is sheer moral
perversity to equate the inconsistencies of a democratic
civilization with the brutalities which modern tyrannical states
practice."
Reinhold Nieburhr, ca. 1940
Import Notes
Reply to msg id not found: 013301c3681b$9ed6f880$e701f00a@240.1.139.196.23.149.50
SQL (and PostgreSQL) certainly can do what you want to do. You don't need an
external language to achieve this unless there are specific conditions that
require an external data source.
Essentially if you can bring the data into PostgreSQL (flat text) then using
the UPDATE ... FROM and INSERT ... SELECT FROM syntaxes you can determine:
a) What already exists and needs updating (you need a key for reference - well
you would with if statements anyway)
b) What doesn't exist and needs to be inserted.
If you can't do this then I don't know how my production system has been
running for the last 6 months, updating and bringing in new data every 24
hours : ) You don't need to use pl/pgsql to get this basic functionality
out of your db.
I recommend reading:
http://www.postgresql.org/docs/7.3/static/sql-update.html
http://www.postgresql.org/docs/7.3/static/sql-insert.html
http://www.postgresql.org/docs/7.3/static/functions-subquery.html
http://www.postgresql.org/docs/7.3/static/queries-union.html
..and see below for an example of something I've been playing with for work.
It relies on two tables, a target (production table) and import table. It
compares the two and deletes any non existant data from the production table
based on the import table, then updates any existing data and then inserts
new data - all in SQL and all working quite nicely. And because it's all
wrapped in a transaction the whole lot has to work (or fail) and you achieve
the 'one sql action' effect you were trying to get. Note that I use the
EXCEPT key word instead of EXISTS here.
begin;
--Group by to remove duplicates
delete from departments where not exists
(select departmenthostkey from ztimetable zt where departmenthostkey is not
null and departments.departmenthostkey = zt.departmenthostkey
group by zt.departmenthostkey);
update departments set department = zt.departmentname from
(select departmenthostkey,departmentname from ztimetable where
departmenthostkey is not null group by departmenthostkey,departmentname) zt
where (departments.departmenthostkey = zt.departmenthostkey and
departments.department <> zt.departmentname);
insert into departments (departmenthostkey,department) select
zt.departmenthostkey,zt.departmentname from
(select departmenthostkey,departmentname from ztimetable where
departmenthostkey is not null group by departmenthostkey,departmentname) zt
except (select departmenthostkey,department from departments);
commit;
Rgds,
Jason
Show quoted text
On Fri, 22 Aug 2003 05:37 am, Philip Boonzaaier wrote:
Hi Ron
Yeah. I see what you are getting at. However, what about using a RULE ?
This seems to fit what I am trying to do.Let me tell you what I am doing at the moment. I am migrating a COBOL based
system to a RDBMS base, and eventually a Perl / Java / Whatever front end.
As Phase 1, I am simple replicating the data in PostgreSQL. I have created
tables identical to the 'records' in COBOL. When I INSERT in COBOL, I
create an INSERT in SQL and action this. This is done externally from
COBOL, and not using any embedded SQL features. Similarly with UPDATE.
However, I now want to create a Table based on a sub - set of information,
in the record in the first attempt, I am creating a table of Telephone
numbers for an account, which is currently defined as an array of 4
possibilities within the account record. ). Now, when UPDATING the main
row, I have no idea if the sub - set of information is already in the
database, or not. So I want to, simply by writing a SQL statement, INSERT
or UPDATE the information in the database.Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 9:01 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist
and need updating, and which do not and need inserting, then what can ?In the old world of indexed ISAM files it is very simple - try to get the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.Now, one can do this with a higher level language and SQL combined, but
is SQL that weak ?No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow
determine what needs INSERTING and what needs UPDATING.... Or does one
try to merge, get a failure, an resort to writing something in Perl or C
?In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be
feasible, using your suggestion, to simply put in two SQL statements,
in the same query - first UPDATE when EXISTS, then INSERT when NOT
EXISTS, toaccomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correcltyyou
should be able to acheive the same result using two seperate queries
andthe
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you
touse
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a
list
of
data, effectively row by row, enquire on the database if this exists
in
the
selected table- If it exists, then the colums must be UPDATED, if
not,they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe notat
all.
Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USAAfter listening to many White House, Pentagon & CENTCOM
briefings in both Gulf Wars, it is my firm belief that most
"senior correspondents" either have serious agendas that don't
get shaken by facts, or are dumb as dog feces.---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friendThis message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed in this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Import Notes
Reply to msg id not found: 013301c3681b$9ed6f880$e701f00a@240.1.139.196.23.149.50
OK I understand where you are coming from now... I thought you meant large
batch updates. You could always just write the data in delimited fromat to
disk and have a static SQL handler to do it but if you want to generate SQL
then in this instance use pl/pgsql:
CREATE OR REPLACE FUNCTION processdata (int4,int4,varchar) RETURNS VARCHAR AS
'
DECLARE
P_ACCNUM ALIAS FOR $1;
P_OCCUR ALIAS FOR $2;
P_PHNUM ALIAS FOR $3;
V_TYPE VARCHAR;
BEGIN
IF (P_ACCNUM IS NULL OR P_OCCUR IS NULL OR P_PHNUM IS NULL) THEN
RAISE EXCEPTION ''Error in parameters'';
ELSE
IF ((SELECT COUNT(*) FROM T_SUBSET WHERE "Account_Number" = P_ACCNUM AND
"S_Occurance" = P_OCCUR) = 1) THEN
UPDATE T_SUBSET SET "Telephone_Number" = P_PHNUM WHERE "Account_Number" =
P_ACCNUM AND "S_Occurance" = P_OCCUR;
V_TYPE = ''UPDATE'';
ELSE
INSERT INTO T_SUBSET VALUES (P_ACCNUM,P_POCCUR,P_PHNUM);
V_TYPE = ''INSERT'';
END IF;
END IF;
RETURN V_TYPE;
END;
' LANGUAGE 'plpgsql';
I haven't tested or debugged that but you get the idea. Watch plpgsql's
string escaping (ie - double quoting etc...). Read the manual about that.
You may also need to check for the existance of the key first but it will
error in the event of that anyway.
Now write to your text file:
SELECT processdata(100,1,'+27543643');
SELECT processdata(101,2,'+27544567');
etc....
and pipe it to psql. It will return either UPDATE or INSERT depending on
which action it had to take.
Rgds,
Jason
Show quoted text
On Fri, 22 Aug 2003 07:13 am, Philip Boonzaaier wrote:
Hi Ron
I could pursue the pre-compiler route, but I'm actually trying to avoid
this. The more independant I can make the RDBMS, the better. Step 1 will be
to put the data in a decently aranged set of tables, Step 2, generate the
business rules and logic as functions in the database, Step 3, re-write the
front ends as now 'logic free' data capture windows.We are using Client / Server tools with the COBOL. So, while the Clients
are Windows based, the Server is UNIX or LINUX. What we have working so
far, is the gerneration of the appropriate SQL commands, into a text file,
with the time incorporated in the name. No more than 60 seconds later, we
have a UNIX script, running in the background, which picks up the file, and
executes the batch.T_SUBSET would be defined as having a FOREIGN KEY linking it to T_MAIN, but
other that the Key columns, the data in T_SUBSET is unique, and does not
exist in T_MAIN.For example, I would CREATE T_MAIN like this :
CREATE TABLE T_MAIN
(Account_Number INTEGER NOT NULL,
Account_Name VARCHAR (40),
PRIMARY KEY (Account_Number));And T_SUBSET would be :
CREATE TABLE T_SUBSET
(Account_Number INTEGER NOT NULL,
S_Occurance INTEGER NOT NULL,
Telephone_Number VARCHAR(15),
PRIMARY KEY (Account_Number,S_Occurance),
FOREIGN KEY(Account_Number)
REFERENCES T_MAIN(Account_Number));The Record in COBOL would be simply Account, containing Account Number,
Name, and Telephone Number Occurs 4 times.INSERTING a New Account would be no problem, I'd generate SQL INSERT for
T_MAIN, and for each occurance of Telephone number, where this is not NULL,
I'd generate an appropriate INSERT into T_SUBSET, corresponding S_Occurance
with the Occurance number in the COBOL Record..So far so good.
But when a user changes an existing record, I'd generate an UPDATE T_MAIN
statement. Now comes the problem. Maybe when the data was first captured,
only 1 telephone number was entered. Now with the update, phone number 1
was changed, and phone number 2 was added. So I'd need to check for each of
them - does this occurance exist ? YES - then Update it, NO, then INSERT
IT. This would be possible if IF statements were supported in SQL, as I
would then SELECT * FROM T_SUBSET WHERE Account_Number = <account number>
AND S_Occurance = < occuarance number>, then IF EXISTS, UPDATE.... ELSE
INSERT ....,Jason, your latest email seems to shed some light on this. Would I Then
HAVE to put the source data into a text file to accomplish this ? Is there
no INSERT INTO T_SUBSET VALUES (100,1,'+27543643') and then something like
WHERE NOT EXISTS ( SELECT * FROM T_MAIN WHERE Account_Number = 100 AND
S_Occurance=1); ???Any ideas ?
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 10:30 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 14:37, Philip Boonzaaier wrote:
Hi Ron
Yeah. I see what you are getting at. However, what about using a RULE ?
This
seems to fit what I am trying to do.
You mean a PostgreSQL RULE?
Let me tell you what I am doing at the moment. I am migrating a COBOL
based
system to a RDBMS base, and eventually a Perl / Java / Whatever front
end.Well, gee, there are pre-compilers floating around that let you
embed SQL in COBOL. Unfortunately, non of them are OSS...As Phase 1, I am simple replicating the data in PostgreSQL. I have
created tables identical to the 'records' in COBOL. When I INSERT in
COBOL, Icreate
an INSERT in SQL and action this. This is done externally from COBOL, and
Externally from COBOL? You mean in some lashed-together batch
mode operation?not using any embedded SQL features. Similarly with UPDATE. However, I
now want to create a Table based on a sub - set of information, in the
record in the first attempt, I am creating a table of Telephone numbers
for an account, which is currently defined as an array of 4 possibilities
within the account record. ). Now, when UPDATING the main row, I have no
idea if the sub - set of information is already in the database, or not.
So I want to, simply by writing a SQL statement, INSERT or UPDATE the
information in the database.Give the name T_SUBSET to this sub-set table, and T_MAIN to the
main table. Original, eh?Thus, for a given tuple in the main row, some pseudo-code:
UPDATE t_main AS m
SET m.field1 = ss.field1,
m.field2 = ss.field2
FROM t_subset AS ss
WHERE m.field3 = ss.field3
AND m.field4 = ss.field4
AND ss.field3 = ??
AND ss.field4 = ?? ;IF zero rows updated THEN
INSERT INTO T_MAIN VALUES (blah, blah, blah);
END IFIf the number of parameters that you'd need to send is a reasonable
amount, then you could encapsulate the code into a trigger, thus
simplifying the Perl / Java / Whatever code.Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 9:01 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist
and
need updating, and which do not and need inserting, then what can ?
In the old world of indexed ISAM files it is very simple - try to get
the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.Now, one can do this with a higher level language and SQL combined, but
is
SQL that weak ?
No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow
determine
what needs INSERTING and what needs UPDATING.... Or does one try to
merge,
get a failure, an resort to writing something in Perl or C ?
In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be
feasible,
using your suggestion, to simply put in two SQL statements, in the
same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>;
<pgsql-general@postgresql.org> Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use
the 'when'
conditional but not to do what you need. If I understand youcorreclty
you
should be able to acheive the same result using two seperate queries
and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the
finedocs
on pl/pgsql and other postgresql procedural languages which allow you
to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a
list
of
data, effectively row by row, enquire on the database if this
existsin
the
selected table- If it exists, then the colums must be UPDATED, if
not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear
that
PostgreSQL does not support an IF in this type of query, or maybe
not
at
all.
Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA"Whatever may be the moral ambiguities of the so-called
demoratic nations and however serious may be their failure to
conform perfectly to their democratic ideals, it is sheer moral
perversity to equate the inconsistencies of a democratic
civilization with the brutalities which modern tyrannical states
practice."
Reinhold Nieburhr, ca. 1940---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.orgThis message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed in this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Import Notes
Reply to msg id not found: 015301c36829$0b017aa0$e701f00a@240.1.139.196.23.149.50
Or you could write your data to text file:
100,1,'+23456789'
101,1,'+23456767'
with a table:
CREATE TABLE import (
accnum int4,
instance int4,
phone varchar(15)
) without oids;
then in a script:
cat datafile.txt | psql -ddatabase -c "COPY import FROM STDIN DELIMITER ',';"
echo "
BEGIN;
UPDATE T_SUBSET SET "Telephone_Number" = import.phone FROM
import WHERE "Account_Number" = accnum AND "S_Occurance" = instance;
INSERT INTO T_SUBSET SELECT accnum,instance,phone FROM
(SELECT accnum,instance,phone FROM import WHERE NOT EXISTS
(SELECT * FROM T_SUBSET WHERE "Account Number" = accnum AND
"S_Occurance" = instance));
COMMIT;" | psql -ddatabase
Or something like that.. and only use 3 sql statements for the whole lot.
That way you don't even have to form your queries in your text file and just
pipe raw data through.
Rgds,
Jason
Show quoted text
On Fri, 22 Aug 2003 07:13 am, Philip Boonzaaier wrote:
Hi Ron
I could pursue the pre-compiler route, but I'm actually trying to avoid
this. The more independant I can make the RDBMS, the better. Step 1 will be
to put the data in a decently aranged set of tables, Step 2, generate the
business rules and logic as functions in the database, Step 3, re-write the
front ends as now 'logic free' data capture windows.We are using Client / Server tools with the COBOL. So, while the Clients
are Windows based, the Server is UNIX or LINUX. What we have working so
far, is the gerneration of the appropriate SQL commands, into a text file,
with the time incorporated in the name. No more than 60 seconds later, we
have a UNIX script, running in the background, which picks up the file, and
executes the batch.T_SUBSET would be defined as having a FOREIGN KEY linking it to T_MAIN, but
other that the Key columns, the data in T_SUBSET is unique, and does not
exist in T_MAIN.For example, I would CREATE T_MAIN like this :
CREATE TABLE T_MAIN
(Account_Number INTEGER NOT NULL,
Account_Name VARCHAR (40),
PRIMARY KEY (Account_Number));And T_SUBSET would be :
CREATE TABLE T_SUBSET
(Account_Number INTEGER NOT NULL,
S_Occurance INTEGER NOT NULL,
Telephone_Number VARCHAR(15),
PRIMARY KEY (Account_Number,S_Occurance),
FOREIGN KEY(Account_Number)
REFERENCES T_MAIN(Account_Number));
Import Notes
Reply to msg id not found: 015301c36829$0b017aa0$e701f00a@240.1.139.196.23.149.50
Wasn't there a feature in some SQL database which was the equivalent of
UPDATE OR INSERT ... based on the primary key? Would this accomplish what
you want (I know that I have a desire for this feature a couple of times,
as I simply have code or triggers to essentially do the equivalent)? Is
this a desirable feature for Postgresql?
Regards,
Ed
On Thu, 21 Aug 2003, Philip Boonzaaier wrote:
Show quoted text
Hi Ron
That is just the point. If Postgres cannot tell me which records exist and
need updating, and which do not and need inserting, then what can ?In the old world of indexed ISAM files it is very simple - try to get the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.Now, one can do this with a higher level language and SQL combined, but is
SQL that weak ?What happens when you merge two tables ? Surely SQL must somehow determine
what needs INSERTING and what needs UPDATING.... Or does one try to merge,
get a failure, an resort to writing something in Perl or C ?Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, toaccomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correcltyyou
should be able to acheive the same result using two seperate queries and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.orgThis message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Import Notes
Reply to msg id not found: 011c01c36812$cb6b1b00$e701f00a@240.1.139.196.23.149.50 | Resolved by subject fallback
On Thu, Aug 21, 2003 at 12:56:18 -0400,
Edmund Dengler <edmundd@eSentire.com> wrote:
Wasn't there a feature in some SQL database which was the equivalent of
UPDATE OR INSERT ... based on the primary key? Would this accomplish what
you want (I know that I have a desire for this feature a couple of times,
as I simply have code or triggers to essentially do the equivalent)? Is
this a desirable feature for Postgresql?
I remember people asking for the equivalent of MYSQL's REPLACE command
in previous threads.
Another alternative is allowing the clients to determine what do do after an
error so that you could try an insert first and then do an update if it
failed without aborting your transaction.
I take it, following this thread, is that REPLACE is not in the SQL
standard?
Bruno Wolff III wrote:
Show quoted text
On Thu, Aug 21, 2003 at 12:56:18 -0400,
Edmund Dengler <edmundd@eSentire.com> wrote:Wasn't there a feature in some SQL database which was the equivalent of
UPDATE OR INSERT ... based on the primary key? Would this accomplish what
you want (I know that I have a desire for this feature a couple of times,
as I simply have code or triggers to essentially do the equivalent)? Is
this a desirable feature for Postgresql?I remember people asking for the equivalent of MYSQL's REPLACE command
in previous threads.Another alternative is allowing the clients to determine what do do after an
error so that you could try an insert first and then do an update if it
failed without aborting your transaction.---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Thanks Jason.
The UPDATE part works fine. However, INSERT still gives problems. For now,
I'm inserting hard coded values, and not from one table into the other.
I use
UPDATE telephones SET
telephone_type='CELL',
telephone_number=836789012
WHERE data_set='AA'
AND account_number=8
AND dependant_number=1
AND sub_entity='pers'
AND sub_occur=1
INSERT INTO telephones
VALUES ('AA',8,1,'pers',1,CELL,836789012);
even if I were getting these values from another table, I'd still have to
somehow determine if the data EXISTS in the telephones table before deciding
to UPDATE, or INSERT.
It looks like this is just not possible in a SQL query on its own, but has
to be done in a higher level language where the condition may be
successfully processed - i.e. SELECT * WHERE <same WHERE as above>
and then check IF EXISTS, if so, UPDATE, if not INSERT.
Or would I be able to achieve such functionality by writing a Function ?
Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, August 20, 2003 10:07 AM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
Hi Philip,
See:
http://www.postgresql.org/docs/7.3/static/functions-subquery.html
..for starters.
Essentially, to perform the operation atomically I'd use:
begin;
update <table> set <cols> = <values>, ... where exists (select
<corresponding
columns> from <table2> where <table1>.<col> = <table2>.<col> (and).. etc..);
(actually i'd probably use a the from extension here ^^^^ , see example
below)
insert into <table> <columnlist> select <columns> from <table2> where not
exists (select <corresponding columns> from <table1> where <table2>.<col> =
<table1>.<col> (and).. etc..);
commit;
because it's wrapped in a transaction both queries have to work or it's all
rolled back. This example only applies to comparing two tables. You can
specify a value list if need be.
As an actual example:
begin;
update table1 set col1 = table2.col1, col2 = table2.col2 from
table2 where table2.key = table1.key;
(whatever your key may be..)
insert into table1 (col1,col2) select col1,col2 from table2 where not exists
(select col1,col2 from table1 where table1.col1 = table2.col1 and
table1.col2
= table2.col2);
(in this ^^^ I'm assuming your keys are col1 and col2 and so it's not
consistent with the update but you get the idea.
commit;
Rgds,
Jason
On Wed, 20 Aug 2003 01:03 pm, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
questionHow do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist this in one go ?Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correclty
you
should be able to acheive the same result using two seperate queries and
the (NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs on pl/pgsql and other postgresql procedural languages which allow you
to use loops and conditional statements like 'if'.Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
Import Notes
Resolved by subject fallback
Hi Ron
That is just the point. If Postgres cannot tell me which records exist and
need updating, and which do not and need inserting, then what can ?
In the old world of indexed ISAM files it is very simple - try to get the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.
Now, one can do this with a higher level language and SQL combined, but is
SQL that weak ?
What happens when you merge two tables ? Surely SQL must somehow determine
what needs INSERTING and what needs UPDATING.... Or does one try to merge,
get a failure, an resort to writing something in Perl or C ?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
On Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?
A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correclty
you
should be able to acheive the same result using two seperate queries and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
Hi Ron
Yeah. I see what you are getting at. However, what about using a RULE ? This
seems to fit what I am trying to do.
Let me tell you what I am doing at the moment. I am migrating a COBOL based
system to a RDBMS base, and eventually a Perl / Java / Whatever front end.
As Phase 1, I am simple replicating the data in PostgreSQL. I have created
tables identical to the 'records' in COBOL. When I INSERT in COBOL, I create
an INSERT in SQL and action this. This is done externally from COBOL, and
not using any embedded SQL features. Similarly with UPDATE. However, I now
want to create a Table based on a sub - set of information, in the record
in the first attempt, I am creating a table of Telephone numbers for an
account, which is currently defined as an array of 4 possibilities within
the account record. ). Now, when UPDATING the main row, I have no idea if
the sub - set of information is already in the database, or not. So I want
to, simply by writing a SQL statement, INSERT or UPDATE the information in
the database.
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 9:01 AM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
On Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist and
need updating, and which do not and need inserting, then what can ?In the old world of indexed ISAM files it is very simple - try to get the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.
SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.
Now, one can do this with a higher level language and SQL combined, but is
SQL that weak ?
No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow determine
what needs INSERTING and what needs UPDATING.... Or does one try to merge,
get a failure, an resort to writing something in Perl or C ?
In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.
So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.
For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.
With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.
If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...
Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, toaccomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correcltyyou
should be able to acheive the same result using two seperate queries and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a
list
of
data, effectively row by row, enquire on the database if this exists
in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not
at
all.
Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
After listening to many White House, Pentagon & CENTCOM
briefings in both Gulf Wars, it is my firm belief that most
"senior correspondents" either have serious agendas that don't
get shaken by facts, or are dumb as dog feces.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
Hi Ron
I could pursue the pre-compiler route, but I'm actually trying to avoid
this. The more independant I can make the RDBMS, the better. Step 1 will be
to put the data in a decently aranged set of tables, Step 2, generate the
business rules and logic as functions in the database, Step 3, re-write the
front ends as now 'logic free' data capture windows.
We are using Client / Server tools with the COBOL. So, while the Clients are
Windows based, the Server is UNIX or LINUX. What we have working so far, is
the gerneration of the appropriate SQL commands, into a text file, with the
time incorporated in the name. No more than 60 seconds later, we have a UNIX
script, running in the background, which picks up the file, and executes the
batch.
T_SUBSET would be defined as having a FOREIGN KEY linking it to T_MAIN, but
other that the Key columns, the data in T_SUBSET is unique, and does not
exist in T_MAIN.
For example, I would CREATE T_MAIN like this :
CREATE TABLE T_MAIN
(Account_Number INTEGER NOT NULL,
Account_Name VARCHAR (40),
PRIMARY KEY (Account_Number));
And T_SUBSET would be :
CREATE TABLE T_SUBSET
(Account_Number INTEGER NOT NULL,
S_Occurance INTEGER NOT NULL,
Telephone_Number VARCHAR(15),
PRIMARY KEY (Account_Number,S_Occurance),
FOREIGN KEY(Account_Number)
REFERENCES T_MAIN(Account_Number));
The Record in COBOL would be simply Account, containing Account Number,
Name, and Telephone Number Occurs 4 times.
INSERTING a New Account would be no problem, I'd generate SQL INSERT for
T_MAIN, and for each occurance of Telephone number, where this is not NULL,
I'd generate an appropriate INSERT into T_SUBSET, corresponding S_Occurance
with the Occurance number in the COBOL Record..
So far so good.
But when a user changes an existing record, I'd generate an UPDATE T_MAIN
statement. Now comes the problem. Maybe when the data was first captured,
only 1 telephone number was entered. Now with the update, phone number 1 was
changed, and phone number 2 was added. So I'd need to check for each of
them - does this occurance exist ? YES - then Update it, NO, then INSERT IT.
This would be possible if IF statements were supported in SQL, as I would
then SELECT * FROM T_SUBSET WHERE Account_Number = <account number> AND
S_Occurance = < occuarance number>, then IF EXISTS, UPDATE.... ELSE INSERT
....,
Jason, your latest email seems to shed some light on this. Would I Then HAVE
to put the source data into a text file to accomplish this ? Is there no
INSERT INTO T_SUBSET VALUES (100,1,'+27543643') and then something like
WHERE NOT EXISTS ( SELECT * FROM T_MAIN WHERE Account_Number = 100 AND
S_Occurance=1); ???
Any ideas ?
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 10:30 AM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
On Thu, 2003-08-21 at 14:37, Philip Boonzaaier wrote:
Hi Ron
Yeah. I see what you are getting at. However, what about using a RULE ?
This
seems to fit what I am trying to do.
You mean a PostgreSQL RULE?
Let me tell you what I am doing at the moment. I am migrating a COBOL
based
system to a RDBMS base, and eventually a Perl / Java / Whatever front end.
Well, gee, there are pre-compilers floating around that let you
embed SQL in COBOL. Unfortunately, non of them are OSS...
As Phase 1, I am simple replicating the data in PostgreSQL. I have created
tables identical to the 'records' in COBOL. When I INSERT in COBOL, I
create
an INSERT in SQL and action this. This is done externally from COBOL, and
Externally from COBOL? You mean in some lashed-together batch
mode operation?
not using any embedded SQL features. Similarly with UPDATE. However, I now
want to create a Table based on a sub - set of information, in the record
in the first attempt, I am creating a table of Telephone numbers for an
account, which is currently defined as an array of 4 possibilities within
the account record. ). Now, when UPDATING the main row, I have no idea if
the sub - set of information is already in the database, or not. So I want
to, simply by writing a SQL statement, INSERT or UPDATE the information in
the database.
Give the name T_SUBSET to this sub-set table, and T_MAIN to the
main table. Original, eh?
Thus, for a given tuple in the main row, some pseudo-code:
UPDATE t_main AS m
SET m.field1 = ss.field1,
m.field2 = ss.field2
FROM t_subset AS ss
WHERE m.field3 = ss.field3
AND m.field4 = ss.field4
AND ss.field3 = ??
AND ss.field4 = ?? ;
IF zero rows updated THEN
INSERT INTO T_MAIN VALUES (blah, blah, blah);
END IF
If the number of parameters that you'd need to send is a reasonable
amount, then you could encapsulate the code into a trigger, thus
simplifying the Perl / Java / Whatever code.
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 9:01 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist
and
need updating, and which do not and need inserting, then what can ?
In the old world of indexed ISAM files it is very simple - try to get
the
record ( row ) by primary key. If it is there, update it, if it is not,
insert it.SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.Now, one can do this with a higher level language and SQL combined, but
is
SQL that weak ?
No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow
determine
what needs INSERTING and what needs UPDATING.... Or does one try to
merge,
get a failure, an resort to writing something in Perl or C ?
In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
:
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be
feasible,
using your suggestion, to simply put in two SQL statements, in the
same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you
correclty
you
should be able to acheive the same result using two seperate queries
and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs
on pl/pgsql and other postgresql procedural languages which allow you
to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a
list
of
data, effectively row by row, enquire on the database if this exists
in
the
selected table- If it exists, then the colums must be UPDATED, if
not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear
that
PostgreSQL does not support an IF in this type of query, or maybe
not
at
all.
Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"Whatever may be the moral ambiguities of the so-called
demoratic nations and however serious may be their failure to
conform perfectly to their democratic ideals, it is sheer moral
perversity to equate the inconsistencies of a democratic
civilization with the brutalities which modern tyrannical states
practice."
Reinhold Nieburhr, ca. 1940
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
Thanks Jason
I'm sure this will work perfectly !
Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; Ron Johnson <ron.l.johnson@cox.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 12:05 PM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
OK I understand where you are coming from now... I thought you meant large
batch updates. You could always just write the data in delimited fromat to
disk and have a static SQL handler to do it but if you want to generate SQL
then in this instance use pl/pgsql:
CREATE OR REPLACE FUNCTION processdata (int4,int4,varchar) RETURNS VARCHAR
AS
'
DECLARE
P_ACCNUM ALIAS FOR $1;
P_OCCUR ALIAS FOR $2;
P_PHNUM ALIAS FOR $3;
V_TYPE VARCHAR;
BEGIN
IF (P_ACCNUM IS NULL OR P_OCCUR IS NULL OR P_PHNUM IS NULL) THEN
RAISE EXCEPTION ''Error in parameters'';
ELSE
IF ((SELECT COUNT(*) FROM T_SUBSET WHERE "Account_Number" = P_ACCNUM AND
"S_Occurance" = P_OCCUR) = 1) THEN
UPDATE T_SUBSET SET "Telephone_Number" = P_PHNUM WHERE "Account_Number" =
P_ACCNUM AND "S_Occurance" = P_OCCUR;
V_TYPE = ''UPDATE'';
ELSE
INSERT INTO T_SUBSET VALUES (P_ACCNUM,P_POCCUR,P_PHNUM);
V_TYPE = ''INSERT'';
END IF;
END IF;
RETURN V_TYPE;
END;
' LANGUAGE 'plpgsql';
I haven't tested or debugged that but you get the idea. Watch plpgsql's
string escaping (ie - double quoting etc...). Read the manual about that.
You may also need to check for the existance of the key first but it will
error in the event of that anyway.
Now write to your text file:
SELECT processdata(100,1,'+27543643');
SELECT processdata(101,2,'+27544567');
etc....
and pipe it to psql. It will return either UPDATE or INSERT depending on
which action it had to take.
Rgds,
Jason
On Fri, 22 Aug 2003 07:13 am, Philip Boonzaaier wrote:
Hi Ron
I could pursue the pre-compiler route, but I'm actually trying to avoid
this. The more independant I can make the RDBMS, the better. Step 1 will
be
to put the data in a decently aranged set of tables, Step 2, generate the
business rules and logic as functions in the database, Step 3, re-write
the
front ends as now 'logic free' data capture windows.
We are using Client / Server tools with the COBOL. So, while the Clients
are Windows based, the Server is UNIX or LINUX. What we have working so
far, is the gerneration of the appropriate SQL commands, into a text file,
with the time incorporated in the name. No more than 60 seconds later, we
have a UNIX script, running in the background, which picks up the file,
and
executes the batch.
T_SUBSET would be defined as having a FOREIGN KEY linking it to T_MAIN,
but
other that the Key columns, the data in T_SUBSET is unique, and does not
exist in T_MAIN.For example, I would CREATE T_MAIN like this :
CREATE TABLE T_MAIN
(Account_Number INTEGER NOT NULL,
Account_Name VARCHAR (40),
PRIMARY KEY (Account_Number));And T_SUBSET would be :
CREATE TABLE T_SUBSET
(Account_Number INTEGER NOT NULL,
S_Occurance INTEGER NOT NULL,
Telephone_Number VARCHAR(15),
PRIMARY KEY (Account_Number,S_Occurance),
FOREIGN KEY(Account_Number)
REFERENCES T_MAIN(Account_Number));The Record in COBOL would be simply Account, containing Account Number,
Name, and Telephone Number Occurs 4 times.INSERTING a New Account would be no problem, I'd generate SQL INSERT for
T_MAIN, and for each occurance of Telephone number, where this is not
NULL,
I'd generate an appropriate INSERT into T_SUBSET, corresponding
S_Occurance
with the Occurance number in the COBOL Record..
So far so good.
But when a user changes an existing record, I'd generate an UPDATE T_MAIN
statement. Now comes the problem. Maybe when the data was first captured,
only 1 telephone number was entered. Now with the update, phone number 1
was changed, and phone number 2 was added. So I'd need to check for each
of
them - does this occurance exist ? YES - then Update it, NO, then INSERT
IT. This would be possible if IF statements were supported in SQL, as I
would then SELECT * FROM T_SUBSET WHERE Account_Number = <account number>
AND S_Occurance = < occuarance number>, then IF EXISTS, UPDATE.... ELSE
INSERT ....,Jason, your latest email seems to shed some light on this. Would I Then
HAVE to put the source data into a text file to accomplish this ? Is there
no INSERT INTO T_SUBSET VALUES (100,1,'+27543643') and then something like
WHERE NOT EXISTS ( SELECT * FROM T_MAIN WHERE Account_Number = 100 AND
S_Occurance=1); ???Any ideas ?
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 10:30 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 14:37, Philip Boonzaaier wrote:
Hi Ron
Yeah. I see what you are getting at. However, what about using a RULE ?
This
seems to fit what I am trying to do.
You mean a PostgreSQL RULE?
Let me tell you what I am doing at the moment. I am migrating a COBOL
based
system to a RDBMS base, and eventually a Perl / Java / Whatever front
end.Well, gee, there are pre-compilers floating around that let you
embed SQL in COBOL. Unfortunately, non of them are OSS...As Phase 1, I am simple replicating the data in PostgreSQL. I have
created tables identical to the 'records' in COBOL. When I INSERT in
COBOL, Icreate
an INSERT in SQL and action this. This is done externally from COBOL,
and
Externally from COBOL? You mean in some lashed-together batch
mode operation?not using any embedded SQL features. Similarly with UPDATE. However, I
now want to create a Table based on a sub - set of information, in the
record in the first attempt, I am creating a table of Telephone numbers
for an account, which is currently defined as an array of 4
possibilities
within the account record. ). Now, when UPDATING the main row, I have no
idea if the sub - set of information is already in the database, or not.
So I want to, simply by writing a SQL statement, INSERT or UPDATE the
information in the database.Give the name T_SUBSET to this sub-set table, and T_MAIN to the
main table. Original, eh?Thus, for a given tuple in the main row, some pseudo-code:
UPDATE t_main AS m
SET m.field1 = ss.field1,
m.field2 = ss.field2
FROM t_subset AS ss
WHERE m.field3 = ss.field3
AND m.field4 = ss.field4
AND ss.field3 = ??
AND ss.field4 = ?? ;IF zero rows updated THEN
INSERT INTO T_MAIN VALUES (blah, blah, blah);
END IFIf the number of parameters that you'd need to send is a reasonable
amount, then you could encapsulate the code into a trigger, thus
simplifying the Perl / Java / Whatever code.Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Thursday, August 21, 2003 9:01 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Thu, 2003-08-21 at 13:33, Philip Boonzaaier wrote:
Hi Ron
That is just the point. If Postgres cannot tell me which records exist
and
need updating, and which do not and need inserting, then what can ?
In the old world of indexed ISAM files it is very simple - try to get
the
record ( row ) by primary key. If it is there, update it, if it is
not,
insert it.
SQL (and, by extension, the relational DBMS) isn't magic. It just
makes it easier to do what we did is the "old world of indexed ISAM"
files.Now, one can do this with a higher level language and SQL combined,
but
is
SQL that weak ?
No, not weak. See below.
What happens when you merge two tables ? Surely SQL must somehow
determine
what needs INSERTING and what needs UPDATING.... Or does one try to
merge,
get a failure, an resort to writing something in Perl or C ?
In this case, SQL will make it easier to tell you what's there,
and, if the "comparison data" is loaded into a separate table,
what's not there.So, yes, you will almost certainly need an "outer" language (C,
Perl, Python, Tck/Tk, Java, etc). However, you'll need less
lines of the outer language if you use SQL.For example, if you use dumb old ISAM files, the most you can do
is specify which index key you want the file sorted on before fetching
*each* *row* *in* *the* *file*, and tough noogies if there are
100M rows in it. And then you must code in IF statements to
skip over any records that don't meet your criteria. This is
just adds more SLOC, thereby increasing the likelihood of bugs.With SQL, however, you embed the winnowing criteria as predicates
in the WHERE clause, or maybe even the FROM clause, if you need
certain kinds of sub-selects.If you think in terms of guns, SQL is a machine gun, thus giving
great firepower/usefullness to the programmer. However, it doesn't
shoot silver bullets...Make any sense?
Please help to un - confuse me !
Regards
Phil
----- Original Message -----
From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL General ML <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 6:45 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteOn Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
question
How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be
feasible,
using your suggestion, to simply put in two SQL statements, in the
same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist
this in one go ?
Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>;
<pgsql-general@postgresql.org> Sent: Tuesday, August 19, 2003 4:42
PM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete
Hi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use
the 'when'
conditional but not to do what you need. If I understand youcorreclty
you
should be able to acheive the same result using two seperate queries
and
the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the
finedocs
on pl/pgsql and other postgresql procedural languages which allow
you
to
use
loops and conditional statements like 'if'.
Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through
a
list
of
data, effectively row by row, enquire on the database if this
existsin
the
selected table- If it exists, then the colums must be UPDATED, if
not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear
that
PostgreSQL does not support an IF in this type of query, or maybe
not
at
all.
Does anyone have any suggestions as to how I can achieve this ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA"Whatever may be the moral ambiguities of the so-called
demoratic nations and however serious may be their failure to
conform perfectly to their democratic ideals, it is sheer moral
perversity to equate the inconsistencies of a democratic
civilization with the brutalities which modern tyrannical states
practice."
Reinhold Nieburhr, ca. 1940---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.orgThis message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed in
this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.
You could create a "virtual" table, that you just inserted to, which had a
"do instead" rule which was a function.
Jon
On Thu, 21 Aug 2003, Philip Boonzaaier wrote:
Show quoted text
Thanks Jason.
The UPDATE part works fine. However, INSERT still gives problems. For now,
I'm inserting hard coded values, and not from one table into the other.
I useUPDATE telephones SET
telephone_type='CELL',
telephone_number=836789012
WHERE data_set='AA'
AND account_number=8
AND dependant_number=1
AND sub_entity='pers'
AND sub_occur=1INSERT INTO telephones
VALUES ('AA',8,1,'pers',1,CELL,836789012);even if I were getting these values from another table, I'd still have to
somehow determine if the data EXISTS in the telephones table before deciding
to UPDATE, or INSERT.It looks like this is just not possible in a SQL query on its own, but has
to be done in a higher level language where the condition may be
successfully processed - i.e. SELECT * WHERE <same WHERE as above>
and then check IF EXISTS, if so, UPDATE, if not INSERT.Or would I be able to achieve such functionality by writing a Function ?
Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, August 20, 2003 10:07 AM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
See:
http://www.postgresql.org/docs/7.3/static/functions-subquery.html
..for starters.
Essentially, to perform the operation atomically I'd use:
begin;
update <table> set <cols> = <values>, ... where exists (select
<corresponding
columns> from <table2> where <table1>.<col> = <table2>.<col> (and).. etc..);(actually i'd probably use a the from extension here ^^^^ , see example
below)insert into <table> <columnlist> select <columns> from <table2> where not
exists (select <corresponding columns> from <table1> where <table2>.<col> =
<table1>.<col> (and).. etc..);commit;
because it's wrapped in a transaction both queries have to work or it's all
rolled back. This example only applies to comparing two tables. You can
specify a value list if need be.As an actual example:
begin;
update table1 set col1 = table2.col1, col2 = table2.col2 from
table2 where table2.key = table1.key;(whatever your key may be..)
insert into table1 (col1,col2) select col1,col2 from table2 where not exists
(select col1,col2 from table1 where table1.col1 = table2.col1 and
table1.col2
= table2.col2);(in this ^^^ I'm assuming your keys are col1 and col2 and so it's not
consistent with the update but you get the idea.commit;
Rgds,
Jason
On Wed, 20 Aug 2003 01:03 pm, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid
questionHow do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
accomplist this in one go ?Regards
Phil
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi Philip,
Pg is more ansi compliant than most (GoodThing (TM)). You can use the
'when'
conditional but not to do what you need. If I understand you correcltyyou
should be able to acheive the same result using two seperate queries and
the (NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
docs on pl/pgsql and other postgresql procedural languages which allow you
to use loops and conditional statements like 'if'.Rgds,
J
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
I want to be able to generate SQL statements that will go through a list
of
data, effectively row by row, enquire on the database if this exists in
the
selected table- If it exists, then the colums must be UPDATED, if not,
they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the
addressee only. If you are not the intended recipient you may not
disclose, copy or in any way use or publish the content hereof, which is
subject to copyright.If you have received this in error, please destroy
the original message and contact us at postmaster@cks.co.za. Any views
expressed inthis
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems,
its subsidiaries or associates. Please note that the recipient must scan
this e-mail and attachments for viruses. We accept no liability of
whatever nature for any loss, liability,damage or expense resulting
directly or indirectly from this transmission of this message and/or
attachments.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmasterThis message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at postmaster@cks.co.za. Any views expressed inthis
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not matchThis message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at postmaster@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend