Re: Bulk Insert / Update / Delete - Mailing list pgsql-general

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 / 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 / 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 / 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.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