0

I've searched extensively and I've not been able to solve this simple issue. I'm programming in nodejs, and using pg-promise to store my JSON file dynamically into a postgres 9.4.11 database (I'm not allowed to upgrade the DB). My JSON contains about a hundred columns each, and I've parsed it properly into three objects. 1. fields 2. columns 3. tables Pg-promise works PERFECTLY when I do this:

//-- connection details would be up here  const db = pgp (conn);--
//Insert statement is below


 for (var i=0; i<Object.keys(fields).length; i++){

    const data = [fields[i]];
    const cs = new pgp.helpers.ColumnSet(columns[i]);
    const table = new pgp.helpers.TableName(tables[i],"public");
    const insert = pgp.helpers.insert(data,cs,table);

       db.none(insert)
        .then(data => {
            console.log("Success! Record inserted for " + table);
        })
        .catch(error => {
            //error
            console.log("ALERT!!! Something went wrong with Record in table " + table);
        });
    }

But of course, I'm on postgresql 9.4.11 and I can't use the simple:

const insert = pgp.helpers.insert(data,cs,table) + " ON CONFLICT (id1, uid2, xid3) DO NOTHING";

to perform an insert of ONLY new items because UPSERTS are only available starting at 9.5.

My query performs about 2,000 row inserts per run and I'm comfortable with that.

I'm pretty new at this, and I hope maybe in my hours of googling I overlooked the straight forward solution.

The closest resolution I've seen is a stored procedure, but I'm not sure how I'd implement that.

Please, stackoverflow, you might be my only hope!

I simply want to make sure each row that matches on id1, uid2, and xid3 does not get inserted into my postgres db.

lysol manilla
  • 1
  • 2
  • Your issue is not really related to `pg-promise`. You simply need to adjust the query according to the `UPSERT` logic supported by your version of PostgreSQL. For that see: [How to UPSERT in PostgreSQL?](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql) – vitaly-t Oct 30 '17 at 22:46
  • @vitaly-t thanks for your quick response. so yes, I saw that post, and I want to attempt the stored procedure method indicated, creating a temporary table but with pg-promise and I'm not sure how. Are you suggesting, maybe I don't need Pg-promise in order to achieve what I'm trying to do? – lysol manilla Oct 30 '17 at 23:10
  • The things you need to change are outside of `pg-promise`, which you will only use to execute queries ;) – vitaly-t Oct 30 '17 at 23:22
  • Why not using then CTE (WITH) query (instead of a stored procedure) as described here: https://stackoverflow.com/questions/39014190/how-to-retrieve-id-of-inserted-row-when-using-upsert-with-with-cluase-in-posgres/39016797#39016797 ? – Kristo Mägi Oct 30 '17 at 23:36
  • Thanks guys! :-) spent a whole day on it and I couldn't get it to work. I'll figure something else out. Maybe I'll query the database and store the matched ID's and load items from the JSON that don't match. – lysol manilla Oct 31 '17 at 20:47
  • So I tried to use the following constructed query within pg-promise, and I got the error "column "google.com" doesn't exist. db.none( 'BEGIN WORK; LOCK TABLE public.'+tables[i] + ' IN SHARE ROW EXCLUSIVE MODE; ' + 'INSERT INTO public.'+tables[i]+ ' ('+ columns[i]+')' + 'SELECT "google.com","another value", "etc..."' +' WHERE NOT EXISTS(SELECT 1 FROM public.'+tables[i] +' WHERE id1 ='+fields[i].id1 +' AND uid2 ='+fields[i].uid2 +' AND xid3 ='+fields[i].xid3 +');' + 'COMMIT WORK;' ) Not sure why this works when others do it, but pg-promise seems to see the VALUEs as columns – lysol manilla Nov 09 '17 at 21:51

1 Answers1

0

So I learned a couple of things about postgres. But first, this is the code that finally worked for me:

 for (var i=0; i<Object.keys(fields).length; i++){

    const data = [fields[i]];
    const cs = new pgp.helpers.ColumnSet(columns[i]);
    const table = new pgp.helpers.TableName(tables[i],"public");
    const insert = pgp.helpers.insert(data,cs,table);
         db.none( 'BEGIN WORK; LOCK TABLE public.'
        + tables[i] + ' IN SHARE ROW EXCLUSIVE MODE; ' 
        + 'INSERT INTO public.'+tables[i]+ ' ('+ columns[i]+')' 
        + 'SELECT 'google.com','another value', 'etc...' WHERE NOT EXISTS(SELECT 1 FROM public.'
        +tables[i] +' WHERE id1 ='+fields[i].id1 
        +' AND uid2 ='+fields[i].uid2 
        +' AND xid3 ='+fields[i].xid3 +');' 
        + 'COMMIT WORK;' ) 
    }

So what I learned was that postgres does not see items inside double quotes as values, but as columns. So I had to take my values array and strip the double quotes and replace with single quotes, which allowed postgres to accept the query.

This use of pg-promise to stitch together a postgres query might seem hacky, but it's the best way I found to work with my data using a for loop to load hundreds of rows that are hundreds of columns wide.

Note, I have a values array, but I wrote them out as 'google.com','another value', 'etc...' just so you'd see the double quotes vs single quotes.

lysol manilla
  • 1
  • 2
  • As long as you do not actually do manual SQL concatenation the way you showed, you should be fine. Otherwise, you absolutely should let `pg-promise` format the SQL for you. Best is to place such an SQL into an external SQL file and use [QueryFile](http://vitaly-t.github.io/pg-promise/QueryFile.html). – vitaly-t Nov 15 '17 at 11:47
  • Thanks, vitaly-t. I've been searching for weeks, and I haven't found any examples that show a sample project that uses a Query file, or would help me use pg-promise to format my sql exactly how I built it in my solution. If I could find one it would probably help make my execution more resource friendly. Currently, I'm looking at trying to wrap the above query in db.tx instead of db.none. Again, trial and error because, n00b. Being new to nodejs, I'm still trying to fully grab the concept of promises vs callbacks. – lysol manilla Nov 16 '17 at 19:23
  • Examples of using `QueryFile` are both in the [API documentation](http://vitaly-t.github.io/pg-promise/QueryFile.html) and in [pg-promise-demo](https://github.com/vitaly-t/pg-promise-demo). – vitaly-t Nov 16 '17 at 19:36
  • Thanks @vitaly-t. I'm going to use this to refine my approach. I'll feed back as things progress – lysol manilla Nov 20 '17 at 15:36