Re: 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(dot)l(dot)johnson(at)cox(dot)net>
> To: PgSQL General ML <pgsql-general(at)postgresql(dot)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(at)optushome(dot)com(dot)au>
> > To: Philip Boonzaaier <phil(at)cks(dot)co(dot)za>; <pgsql-general(at)postgresql(dot)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. ron(dot)l(dot)johnson(at)cox(dot)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.