Insert JSON Fails Node-Postgres

Question

I am trying to insert an array of JSON into Postgres. Everything appears to run successfully (the table gets created), but there is no data inserted, and no error given. I am using the async/await method for transactions, but I can't understand what is failing.

(async() => {

    const client = await pool.connect()

    let val= [
        {"table_pk":1,"NAME":"my Great Name","ROLE":"name1"},
        {"table_pk":2,"NAME":"new Name","ROLE":"name1"},
        {"table_pk":3,"NAME":"someone's funny name","ROLE":"name1"}
        ]

    try {
        await client.query('BEGIN');
        await client.query('DROP TABLE IF EXISTS myTable')
        await client.query(`CREATE TABLE myTable(table_pk INTEGER PRIMARY KEY, name TEXT, role TEXT)`)

        await client.query(`INSERT INTO myTable SELECT * FROM jsonb_populate_recordset(NULL::myTable, $1::jsonb)`,[JSON.stringify(val)]);
        await client.query('COMMIT');
    }
    catch (e) {
        await client.query('ROLLBACK');
        throw e
    }
    finally {
        client.release();
    }
})().catch(e => console.error(e.stack))

EDIT Updated the code to account for missing INSERT, now rows will insert, but not all the data.

And where is your INSERT? You are not adding anything into the table. — vitaly-t
– vitaly-t, Commented Jul 18, 2017 at 14:54
Doesn't 'json_populate_recordset' do the insert for you? I guess I was under the impression it would based on the postgres docs — unseen_damage
– unseen_damage, Commented Jul 18, 2017 at 15:03
You're misunderstanding. It populates the result of the SELECT, it got nothing to do with inserting records. — vitaly-t
– vitaly-t, Commented Jul 18, 2017 at 15:17
You are correct. I was missing the insert. I have updated my code. I have seen another issue with failing to insert. — unseen_damage
– unseen_damage, Commented Jul 18, 2017 at 19:15

unseen_damage · Accepted Answer · 2017-07-18 20:34:36Z

1

The INSERT failed because JSONB is case sensitive when it comes to column names. I had Upper Case column names in the val data and they should have been lower. Thanks to @vitaly-t for pointing out the missing INSERT in my original post.

answered Jul 18, 2017 at 20:34

unseen_damage

1,3763 gold badges15 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert JSON Fails Node-Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related