Bulk Insert / Update / Delete

[Philip Boonzaaier]

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 po********@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,damag e 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?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jason Godden]

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 po********@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,damag e 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?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Doug McNaught]

"Philip Boonzaaier" <ph**@cks.co.za > writes:

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.
Nope. I don't know of an SQL database that does, though I certainly
haven't seen all of them...
Does anyone have any suggestions as to how I can achieve this ?
Application code that loops through the results of the first query,
and issues UPDATE/INSERT statements as needed? Or you could do it as
a PL/pgSQL function which might be a little faster.
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 po********@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,damag e or expense resulting directly or indirectly from this transmission
of this message and/or attachments.

I have companies that force crap like this on mailing list postings...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Doug McNaught]

Doug McNaught <do**@mcnaught. org> writes:

"Philip Boonzaaier" <ph**@cks.co.za > writes:

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 po********@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,damag e or expense resulting directly or indirectly from this transmission
of this message and/or attachments.

I have companies that force crap like this on mailing list postings...

^^^^ hate

Arrghh.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Shridhar Daithankar]

On Tuesday 19 August 2003 20:54, Doug McNaught wrote:

Doug McNaught <do**@mcnaught. org> writes:

I have companies that force crap like this on mailing list postings...

^^^^ hate

Arrghh.

Not to troll, but another mailing list I am on, anybody posting such
messages/footers is politely excused with links to free webmail services that
offer clean text mails. Some known domains are also barred from joining
mailing lists,,

Can not afford to spam excess to 3000 subscribers most of whom pay expensive
metered dial up access. The is a justified logic behind the actions.

Shridhar
---------------------------(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

[Philip Boonzaaier]

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 <ja*********@op tushome.com.au>
To: Philip Boonzaaier <ph**@cks.co.za >; <pg***********@ 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 po********@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,damag e 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?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(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 po********@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,damag e 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?

http://archives.postgresql.org

[Jason Godden]

Hi Philip,

See:

http://www.postgresql.org/docs/7.3/s...-subquery.html

...for starters.

Essentially, to perform the operation atomically I'd use:

begin;

update <table> set <cols> = <values>, ... where exists (select <correspondin g
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 <correspondin g 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
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 <ja*********@op tushome.com.au>
To: Philip Boonzaaier <ph**@cks.co.za >; <pg***********@ 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 po********@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,damag e 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?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(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 po********@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,damag e 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?

http://archives.postgresql.org

---------------------------(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

[Philip Boonzaaier]

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 <ro***********@ cox.net>
To: PgSQL General ML <pg***********@ 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 <ja*********@op tushome.com.au>
To: Philip Boonzaaier <ph**@cks.co.za >; <pg***********@ 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 ?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@c ox.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 ma*******@postg resql.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 po********@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,damag e 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

[Philip Boonzaaier]

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 <ro***********@ cox.net>
To: PgSQL General ML <pg***********@ 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 <ro***********@ cox.net>
To: PgSQL General ML <pg***********@ 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 <ja*********@op tushome.com.au>
To: Philip Boonzaaier <ph**@cks.co.za >; <pg***********@ 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 ?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@c ox.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 po********@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,damag e 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

[Ron Johnson]

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 <ro***********@ cox.net>
To: PgSQL General ML <pg***********@ 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 <ro***********@ cox.net>
To: PgSQL General ML <pg***********@ 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 <ja*********@op tushome.com.au>
To: Philip Boonzaaier <ph**@cks.co.za >; <pg***********@ 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 ?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@c ox.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 ma*******@postg resql.org